Select row of second table only if not exists in first one

Question

I have two tables t1 and t2 with the same structure

id INT
userid INT
date DATETIME

The first table contains my data, while the second table is kind of helper table which contains rows for 10 fix dates and userid = -1 What i need is a SELECT which gives me all rows from t1 with userid=X joined(merged) with all rows from t2 which date is not already in the result of t1.

Pseudo code

SELECT id, date
FROM t1, t2
WHERE (t1.userid=:id OR t2.userid=-1) AND t2.date NOT IN t1.date

Sample:

t1:

id    userid     date
1     1          2015-12-01
2     1          2015-12-02
3     1          2015-12-03
4     2          2015-12-01
5     2          2015-12-02

t2:

id    userid    date
1     -1        2015-12-01
2     -1        2015-12-02
3     -1        2015-12-03
4     -1        2015-12-04
5     -1        2015-12-05

Expected output for userid=1:

1      1        2015-12-01
2      1        2015-12-02
3      1        2015-12-03
4     -1        2015-12-04
5     -1        2015-12-05

Thanks for your help

Look at this: http://dev.mysql.com/doc/refman/5.7/en/join.html — Matt, Dec 16 '15 at 10:41
check this http://stackoverflow.com/questions/5706437/whats-the-difference-between-inner-join-left-join-right-join-and-full-join/6188334#6188334 — Kaushik, Dec 16 '15 at 10:50
I dont understand how this should be done with a join. Join only adds columns and not rows with the same columns. — Thallius, Dec 16 '15 at 10:53

score 1 · Answer 1 · answered Dec 16 '15 at 11:31

1

I'll use a union select for doing this.

SELECT 
id, date
FROM 
t1
WHERE 
t1.id=:id
UNION ALL
(SELECT 
id, date
FROM 
t2
WHERE
t2.id=-1
AND t2.date NOT IN (SELECT date FROM t1 WHERE t1.userid=:id))

answered Dec 16 '15 at 11:31

phsaires

2,188
1
14
11

Select row of second table only if not exists in first one

1 Answers1