2

I am trying to find a SELECT query that returns all of tableA.tba_id that are not exists on tableB (note that are two different foreign keys - tbb_tbAID1 and tbb_tbAID2 between data. You can find DB Model here

What I can do so far
This only tests one column

SELECT tba_id FROM tableA 
WHERE tba_id NOT IN
  (SELECT tbb_tbAID1 FROM tableB
  WHERE tbb_date > '$date2')

I get a MySQL error when trying to test on both foreign keys

#1241 - Operand should contain 1 column(s)

Query:

SELECT tba_id FROM tableA 
WHERE tba_id NOT IN
  (SELECT tbb_tbAID1,tbb_tbAID2 FROM tableB
  WHERE tbb_date > '$date2')

Try 2:

SELECT tba_id FROM tableA 
WHERE tba_id NOT EXISTS
    (SELECT 1 FROM tableB
    WHERE (tba_id = tbb_tbAID1 OR tba_id = tbb_tbAID2)
  AND tbb_date > '$date2')

But gets more results than the first try (that suggests that it's extremely wrongly done)

Try3:

SELECT tba_id 
FROM tableA 
  JOIN tableB ON tba_id!=tbb_tbAID1 AND tba_is!=tbb_tbAID2 
WHERE tbb_data > '$data'

This results on enormous set of data, far away from first method.

0

2 Answers 2

2

There are various ways to do it.

For NOT IN (not recommended because you will get unexpected results if your columns are nullable), you should have 2 subqueries as the a IN (subquery) requires that the subquery returns a single column:

SELECT tba_id FROM tableA 
WHERE tba_id NOT IN
      ( SELECT tbb_tbAID1
        FROM tableB
        WHERE tbb_date > '$date2'
      )
  AND tba_id NOT IN
      ( SELECT tbb_tbAID2
        FROM tableB
        WHERE tbb_date > '$date2'
      ) ;

or a UNION:

SELECT tba_id FROM tableA 
WHERE tba_id NOT IN
      ( SELECT tbb_tbAID1
        FROM tableB
        WHERE tbb_date > '$date2'
      UNION ALL
        SELECT tbb_tbAID2
        FROM tableB
        WHERE tbb_date > '$date2'
      ) ;

My favourite is NOT EXISTS and seems you got it almost right, in Try2.

SELECT tba_id 
FROM tableA
WHERE NOT EXISTS                  -- notice the difference with your query
      ( SELECT 1
        FROM tableB
        WHERE (tba_id = tbb_tbAID1 OR tba_id = tbb_tbAID2)
          AND tbb_date > '$date2'
      ) ;

Of course, you could use 2 subqueries as well:

SELECT tba_id 
FROM tableA 
WHERE NOT EXISTS  
      ( SELECT 1
        FROM tableB
        WHERE tba_id = tbb_tbAID1
          AND tbb_date > '$date2'
      ) 
  AND NOT EXISTS  
      ( SELECT 1
        FROM tableB
        WHERE tba_id = tbb_tbAID2
          AND tbb_date > '$date2'
      ) ;

You could also use LEFT JOIN / IS NULL check:

SELECT tba_id 
FROM tableA
  LEFT JOIN tableB
    ON  (tba_id = tbb_tbAID1 OR tba_id = tbb_tbAID2)
    AND tbb_date > '$date2'
WHERE tbb_date IS NULL ;

or:

SELECT tba_id 
FROM tableA
  LEFT JOIN tableB AS b1
    ON  tba_id = b1.tbb_tbAID1
    AND b1.tbb_date > '$date2'
  LEFT JOIN tableB AS b2
    ON  tba_id = b2.tbb_tbAID2
    AND b2.tbb_date > '$date2'
WHERE b1.tbb_tbAID1 IS NULL 
  AND b2.tbb_tbAID2 IS NULL ;
1

The actually working query:

SELECT tba_id FROM tableA
WHERE NOT EXISTS (SELECT * FROM tableB WHERE (tba_id = tbb_tbAID1 OR tba_id = tbb_tbAID2) AND tbb_data > '$date')

On Try2, I deleted the tba_id from WHERE tba_id NOT EXISTS and worked like a charm. It should be WHERE NOT EXISTS ...

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.