CODEWITHSUNDEEP
mysql
user1111929
user1111929

Reputation: 6099

join with union of multiple tables in SQL

I have the following data structure:

Now I would like to make a MySQL query that does the following: for a given entry_id (say 222) it should select all question_id q from that table for which there is no row with (entry_id=222 AND question_id=q) in data_int, and also no such row in data_text. Is this possible in a single query, and if so how should I do this?

A sample data set would be

entries: 

1
2

data_int:

1, 1, 4
1, 2, 56
1, 6, 43
1, 7, -1

data_text:

1, 3, 'hello'
1, 5, 'world'

questions: 

1
2
3
4
5
6
7
8
9
10

Then for entry_id=1, the return value should be 4, 8, 9, 10, since these don't appear in either data_ table for entry_id=1.

For entry_id=2, the return value should be 1,2,3,4,5,6,7,8,9,10 since nothing appears in any of the data_ tables.

Upvotes: 0

Views: 35

Answers (1)

sgeddes
sgeddes

Reputation: 62831

There are a couple ways to do this. The more efficient way with mysql is probably using multiple outer join / null checks.

select q.*
from questions q 
    left join data_int di on q.questionid = di.questionid and di.entryid = 1
    left join data_text dt on q.questionid = dt.questionid and dt.entryid = 1
where di.entryid is null and dt.entryid is null

Upvotes: 1

Related Questions