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 =…
Query 1: WITH cte AS ( SELECT *, MAX(score) OVER (PARTITION BY exam_id) AS max_score, MIN(score) OVER (PARTITION BY exam_id) AS min_score FROM student JOIN exam USING (student_id) ORDER BY exam_id, student_id ) SELECT * FROM cte; By Query 1,…
In the following scenario, there are 3 tables in a PostgreSQL database (tables and values are simplified for clarity). I am trying to get the following values: sum of the amounts for records (101, 102, 103) in the parent table…
lets get to the situation quickly, so i have a table which contains values as id f_name l_name phone 1 rohan mehra 9090909090 2 yusuf yohana 1010101010 3 rohan mehra 9090909090 4 yusuf yohana 1010101010 so if yu observe, there…
I have 3 tables : accounts, contacts, contact_activity. Accounts can have multiple contacts, also multiple activities whereas contacts can also have multiple activities. adding tables queries below: CREATE TABLE `accounts` ( `id` int(10) UNSIGNED NOT NULL, `companyName` varchar(255) COLLATE utf8mb4_unicode_ci…
I have two queries that I’m trying to combine, but I’m having an issue and I believe it has to do with the GROUP BY Date when I do LEFT JOIN. The date formats are the same and the only…
I have two tables similar to this example (simplified for clarity): parent: | id | name | amount | year | ---------------------------------------- | 101 | Henry | 300 | 2020 | ---------------------------------------- | 102 | Carol | 100 | 2020…
I have my models configured as below: public class Order { [Key] [DatabaseGenerated(DatabaseGeneratedOption.Identity)] public int Id { get; set; } public string UserId { get; set; } = null!; public DkUser User { get; set; } = null!; public int…