Query Help

[ledtear]

Given 3 tables:

 

Table 1

ID Name

1 Fred

2 Jim

3 Tom

 

 

Table 2

ID title

1 Apples

2 Bananas

3 Oranges

4 Grapes

 

 

Table 3

ID table1Id table2Id

1 1 2

2 1 4

3 3 2

4 3 1

 

Given a variable that has the value of Table 1.id - I want to return all of Table 2.title that Table 1.id does not have a record of on table 3 - so given Table 1.id = 1 I would get:

table 2.title = Apples, Grapes

 

make sense?

[Barand]

try

 

SELECT DISTINCT t2.title
FROM table2 t2
INNER JOIN table3 t3 ON t2.id = t3.table2id
LEFT JOIN (
   SELECT table2id
   FROM table3
   WHERE table1id = 1
   ) as X ON t3.table2id = X.table2id
WHERE X.table2id IS NULL

[ledtear]

hmmm - thanks... that is not returning any results... I'll try to look into that more...

[Barand]

Plan B

SELECT DISTINCT t2.title
FROM table2 t2
LEFT JOIN (
    SELECT table2id
    FROM table3
    WHERE table1id = 1
    ) as X ON t2.id = X.table2id
WHERE X.table2id IS NULL;

[jazzman1]

 

So given Table 1.id = 1, I would get: table 2.title = Apples, Grapes

 

 

No, you wrong.

 

For userID 1, you should retrieve the titles - Apples and Oranges.

 

For userID 2 - All titles

 

For userID 3 - Oranges and Grapes

 

My solution is simple,

 

SELECT title FROM tabel2
WHERE id NOT IN (
SELECT table2Id
FROM tabel3
WHERE tabel1id = 1
)

[jazzman1]

hmmm - thanks... that is not returning any results... I'll try to look into that more...

B/s your database is a case sensitive and Barand's used (t2.id = X.table2id), t2.id must be t2.ID.

 

My query is also wrong, it should be something like this

 

SELECT title FROM tabel2
WHERE ID NOT IN (
SELECT table2id
FROM tabel3
WHERE table1id = 1
)