i have a 3 tables rentals table,unit sizes tables and rental_units_pivot table.the rentals table contains the details of a rental,the unit sizes table contains a list of the units sizes such as i bedroom,2 bed room,etc.A rental facility might have…
I am attempting to join 2 tibbles (dplyr::inner_join) based on a UUID column in each. Oddly, instead of the expected one-to-one relationship, the join produces a many-to-many relationship (as stated in a warning message and also indicated by the resulting…
I'm trying to figure out how to perform a pretty complex join on 2 tables that satisfy multiple rules. I'm using mysql 8. Here is some sample SQL to work with: create table person ( id integer primary key name…
I am not sure on how to begin optimizing this High Latency SQL query that my platform runs. Other than reducing the datasets, what are some things that I can do? SELECT ctm.* FROM compound_transaction_map ctm JOIN bustransaction_map btm ON…
I have attendance table like this: Employee Date_Time Status std001 2024-02-01 22:01:00 IN std001 2024-02-02 06:00:05 OUT std001 2024-02-03 07:59:00 IN std001 2024-02-03 17:00:00 OUT std001 2024-02-04 06:00:00 IN std001 2024-02-04 14:00:00 OUT I want to select the table above…
I have four tables: supplies, stocks, stock_ins, and stock_outs. In the Stock controller, I want to sum the total stock_out for each supply . For this, I have used the following query: $supplies = Stock::select( 'supplies.name', 'stocks.supply_id', 'stocks.id', 'stocks.stock', 'stock_ins.quantity…
Let's say I have a table containing cities with their populations and a table containing buildings of different types. I want to know the percentage of people living in cities containing schools. Cities: name pop A 10 B 100 C…
The Problem To set the scene, I work on a PHP Laravel system that has taken a drupal-style approach where every field is its own MySQL table. Every table has optimum indexing making it very quick to fetch data for…
I consider a row duplicate when the combination of hash_key and load_date is repeating. I can find such rows using this CTE: with w_duplicated_rows as ( select hash_key, load_date, count(*), --row_number() over (partition by hash_key, load_date order by load_date desc)…
I need to display the departure and arrival airport codes with their names from another table. But I don't know how to do this in one request select flight_no, a.departure_airport, b.airport_name from flights a join airports_data b on a.departure_airport =…