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?

score 1 · Accepted Answer · answered Jan 14 '14 at 08:40

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 '14 at 08:40

M Khalid Junaid

63,861
10
90
118

@AngularAddict see [working fiddle](http://sqlfiddle.com/#!2/fdd4e/5) there is difference between where and having – M Khalid Junaid Jan 14 '14 at 09:01
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: http://sqlfiddle.com/#!2/f7b17/4 – makallio85 Jan 14 '14 at 09:11
@AngularAddict see [second query](http://sqlfiddle.com/#!2/f7b17/10) 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 Jan 14 '14 at 09:22
Ok. Updated fiddle: http://sqlfiddle.com/#!2/e97cf/2 This quite correct but also c_contact rows with no references in c_monitoring table should be in result set. – makallio85 Jan 14 '14 at 09:34
Nailed it by myself with different approach. See fiddle: http://sqlfiddle.com/#!2/e97cf/22 – makallio85 Jan 14 '14 at 09:58
@AngularAddict i guess i got you try this [union fiddle](http://sqlfiddle.com/#!2/e97cf/21) it has the records where dates are not null with a union where no reference is found let me know it this fits – M Khalid Junaid Jan 14 '14 at 09:58
But I have to say this is so slow. I have about 100.000 records in c_contact and 10.000 in c_monitoring.. Ill just start another thread for this. – makallio85 Jan 14 '14 at 10:15
1

@AngularAddict try your query with [*exists fiddle*](http://sqlfiddle.com/#!2/e97cf/24) Not in is also makes your query slow – M Khalid Junaid Jan 14 '14 at 10:22
1

@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`); – M Khalid Junaid Jan 14 '14 at 10:31

Mysql JOIN subquery

1 Answers1

Linked