Eliminating results

[haku]

I'm having troubles with a query.

 

Table A:

RID (int)

VALUE (text)

 

Table B:

SID (int)

RID (int)

 

The tables are joined by RID.

 

I want to select all rows from table A with a value of X, but not any that have a row in table B where SID = Y.

 

My query now:

 

SELECT rid

FROM table_a

JOIN table_b

ON table_a.rid = table_b.rid

WHERE table_a.value = "X" AND  table_b.rid != Y

 

The problem arises when there is another row in table_b that has that RID. So for example, lets say I don't want to return any rows with SID = 22, but in table_b I have:

 

Row 1: RID = 1, SID = 22

Row 2: RID = 1, SID = 23

 

I don't want to select table_a.value for RID = 1, because there is a row with SID = 22 and RID = 1. But currently, my query will return the table_a.value for RID = 1, because of Row 2 in my example.

 

Can anyone give me a hand with this? Thanks.

[haku]

Got it:

 

SELECT table_1.value

FROM table_1 

LEFT JOIN table_2

ON table_1.rid = table_2.rid

WHERE  table_2.rid NOT IN (SELECT second.rid FROM {table_2} AS second WHERE second.sid = 22)

[oni-kun]

Got it:

 

SELECT table_1.value

FROM table_1 

LEFT JOIN table_2

ON table_1.rid = table_2.rid

WHERE  table_2.rid NOT IN (SELECT second.rid FROM {table_2} AS second WHERE second.sid = 22)

 

Good work. Almost was about to write an example down.

[kickstart]

Hi

 

Possibly more efficient:-

 

SELECT a.*
FROM table_a
LEFT OUTER JOIN (SELECT RID FROM table_b WHERE SID = 22) b
ON a.RID = b.RID
WHERE b.RID IS NULL

 

All the best

 

Keith

[haku]

Thanks mate - that definitely is more efficient.