How can I select using a join in mySQL to grab records that don't match the other table?

Question

I am trying to do this in one query but not sure if it's possible or not.

Table A:
id, uId, pinId

Table B:
id, pinId, ccId

I am trying to grab all of the pinId for specified uId that do not show up in table B matching ccId specified.

So a SELECT pin FROM TABLEA WHERE uId = 2 gets me all of the pinId I need however, if one of the pin codes show up in Table B with the specified ccId I don't want it to return in the results.

Am using mySQL 4 and MINUS seems to throw an error when using this statment:

SELECT pin FROM pincodes WHERE uId = 26 MINUS SELECT pin FROM pincodes_log WHERE cc_id = 22

Answer 1

Search for "NOT IN" SQL query should give you what you want.

Answer 2

i could accept as true with the previous answer...you have one in all 3 achieveable queries achieved reckoning on alternatives of the consumer from the drop-down 0 products chosen: execute question with out 'the place' standards a million merchandise chosen; execute question with 'the place someCol = ' 2+ products chosen: execute question with 'the place someCol IN '

Answer 3

SELECT a.pinId FROM tableA as a
OUTER JOIN tableB AS b ON a.pinId=b.pinId
WHERE a.uId = 2
AND b.pinId IS NULL

Answer 4

SELECT A.ID, A.UID, A.PINID, B.CCID
FROM PINCODES A

LEFT JOIN PINCODES_LOG B ON B.PINID = A.PINID

WHERE
A.UID = 26 AND
B.CCID NOT IN (, , ....)

Answer 5

use MINUS

select pinld FROM TABLEA
minus SELECT pinld from tableb