Mysql – LEFT JOIN only one match per ID
I'm trying to join two tables in a very peculiar way and I can't find the right query to achieve this. I want to keep ALL dt1 rows intact and join the first row for each FUND_ID with the respective…
Postgresql – SQL: two aggregate functions without duplicates
How to use two aggregate functions (for example double string_agg or just sum), but ensure that the results do not contain duplicates caused by the other aggregate function (caused by second join)? I use PostgreSQL. Example I have three tables:…
Mysql – How to delete users who have not added card?
I have the following query: select u.* from users u left join customers_cards cc on cc.user_id = u.id where u.belongs_to = "ezpay" and cc.id is null It returns users who have not added any card yet. I need to delete…
“How do I JOIN, THEN WHERE, THEN LIMIT/OFFSET in MySQL?”
my_tickets +-----------+---------+ | ticket_id | user_id | +-----------+---------+ | 186 | 2 | | 187 | 2 | | 188 | 2 | | 253 | 33 | | 254 | 33 | | 256 | 33 | | 261…
Why aren’t the correct records populating in Laravel using SQL query?
I am trying to filter data with search terms. But its not populate data. Any one can help me with this? Here is my code: $query = Lesson::select('lesson.*') ->join('lesson_language', 'lesson_language.lesson_id', 'lesson.id') ->leftJoin('content', 'content.lesson_id', 'lesson.id') ->leftJoin('lesson_role', 'lesson_role.lesson_id', 'lesson.id') ->leftJoin('lesson_brand', 'lesson_brand.lesson_id', 'lesson.id')…
combining duplicates (perhaps via CONCAT) in a mySQL LEFT JOIN
This question appears to be slightly different from some of the other questions out there dealing with duplicates on a LEFT JOIN. Here's my situation: I have a list of datapoints associated with a meter. The datapoints are contiguous but…
Postgresql – How do I obtain the count of Distinct values after joining two table?
I am not able to get the proper count. I have 2 tables: Tbl1 id name call 123 aaaaaa 15 132 bbbbb 0 Tbl2 id involvement 123 0 123 0 123 1 I would like to get the count of…
About LEFT JOIN in MySQL
In MySQL version 8 series, there is SQL that uses aggregation functions such as count. LEFT JOIN is performed, and the pattern with "IS NULL" specified in the WHERE clause behaves differently than the pattern with "IS NULL" specified in…
PostgreSQL select self referencing with two columns
I'm struggling with this for a while now and can't seem to figure it out. I have a clients, relations (which define relations and their type between clients) and consultations. What I wan't to do is count how many consultations…
How to prioritize selected data based on specific column in MYSQL
I have 2 tables below: table1 store_name store_code sub_channel A 12345 C021 B 56789 C021 C 10000 C021 table2 product_code store_code sub_channel qty X1 12345 10 X1 C021 20 X2 12345 C021 50 X3 56789 C021 20 X4 C021 40…