Have a question on request ..

There is one table with fields:

  • pare
  • day
  • aud (maybe a bunch of duplicates)

There is a request:

SELECT auditories.* FROM (SELECT * FROM TRANS_min GROUP BY aud) auditories RIGHT JOIN (SELECT * FROM TRANS_min WHERE pare = 1 AND day = 6 GROUP BY aud) paramAuditories ON paramAuditories.aud = auditories.aud WHERE paramAuditories.aud IS NOT NULL GROUP BY auditories.aud

SAMPLE

two times we make a request to the table and make a sample for the first time without a condition; we get a list of all unique records by aud; we make the same selection a second time and impose a condition on the selection by day and pair number - we get a list of unique records by aud

TASK : get the records from the first table which are not in the second table

There are about 2 million entries in the table :-)

  • Possible duplicate question: Displaying the value, provided that there is no such thing in another table - Mike
  • And yes, specifically in your case (since the table is only one and you want to apply grouping) everything is much simpler: SELECT * FROM TRANS_min GROUP BY aud HAVING sum( case when pare = 1 AND day = 6 then 1 else 0 end)=0 - Mike
  • @Mike, and how to do the same thing without GROUP BY? - Ujin Pererva
  • so you would decide whether you need it or not, the result is strongly dependent on this. Your option in question with cleaned group by fits, for example. or any of the options for the first link - Mike
  • @Mike in general, I need to get 4 fields from the table, where the aud field can be repeated a large number of times. Queries work correctly, but you need to somehow group the values ​​by aud. DISTINCT does not fit the same ... here I am using group by, but on the battle server there is muscle 5.7. He has ONLY_FULL_GROUP_BY mode enabled and the query doesn't work - Ujin Pererva

1 answer 1

 SELECT COUNT(*) FROM (SELECT * FROM TRANS_min GROUP BY aud) auditories WHERE auditories.aud NOT IN (SELECT aud FROM TRANS_min WHERE pare = 1 GROUP BY aud)

It turned out to solve))

  • SELECT * FROM TRANS_min GROUP BY aud not allowed in most SQL dialects (unless of course there is a single aud column in the table), because all selectable columns must either be present in group by or used in aggregate functions. only MySQL (and not all versions are not with all the settings) and sqlite allow such liberties. And according to the terms of this particular task you "do n’t get records from the first table in the second table, " not a single group by is needed - Mike