Mysql JOIN subquery

Question

How should I do query where

I have (table1) row which can have none or several rows referencing to it in other table
These referencing (table2) rows have colum1 that can be null or date

I would like to have all rows from table1 which have all rows column1 not as null or no rows at all, in table2.

Of course the basic sql goes like:

SELECT table1.* FROM table1 JOIN table2 ON table2.id = table1.table2_id

But what comes next?

M Khalid Junaid · Accepted Answer · 2014-01-14 08:40:47Z

1

You can count the occurences of null in your query like SUM(CASE WHEN table2.col IS NULL THEN 1 ELSE 0 END) AS nullcount, i assume table2.col is the one which has date of null in it

SELECT 
  table1.*,
  SUM(
    CASE
      WHEN table2.col IS NULL 
      THEN 1 
      ELSE 0 
    END
  ) AS nullcount 
FROM
  table1 
  JOIN table2 
    ON table2.id = table1.table2_id 
HAVING nullcount > 0

answered Jan 14, 2014 at 8:40

M Khalid Junaid

64.6k10 gold badges93 silver badges120 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

M Khalid Junaid Over a year ago

@AngularAddict see working fiddle there is difference between where and having

makallio85 Over a year ago

Ok. Thanks. But how can get also c_contact row with id=2 as it has no references in c_monitoring. Actually what I want is rows from c_contact that has no rows in c_cmonitoring or all c_monitoring ended rows are dates, not nulls. Updated fiddle: sqlfiddle.com/#!2/f7b17/4

M Khalid Junaid Over a year ago

@AngularAddict see second query i have added the one more condition in on clause and the row which has 0 null dates are there first query is just to show the data set

M Khalid Junaid Over a year ago

@AngularAddict try your query with exists fiddle Not in is also makes your query slow

M Khalid Junaid Over a year ago

@AngularAddict add these two indexes if you have not added already ALTER TABLE c_contact ADD INDEX test (securityid); ALTER TABLE c_monitoring ADD INDEX test1 (securityid);

|

Collectives™ on Stack Overflow

Mysql JOIN subquery

1 Answer 1

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related