[SOLVED] How To: Select SOME rows from table A that are NOT in table B

[acerbiter]

Hi guys,

 

I've spent an hour googling answer to this problem:

 

I have table A with the following structure (simplified here)

Table A

artist

title

album

id

 

Table B

title

id

 

where id is the primary key in both A and B.

 

Now I'd like to return all resutls from A whose title is similar to a query param

but whose id's are not in B.

 

For example:

$query = 'britney';

 

let's say A and B has the following data

 

table A

britneysong1album11

britneysong2album12

britneysong3album13

britneysong4album14

britneysong5album15

madonnasong1album15

madonnasong2album16

 

table B

song22

song44

song55

 

Then i want my result to return

 

song11

song33

 

 

I've tried the following but it doesn't seem to work (the NOT EXISTS has no effect on the result)

it just returns all 4 rows from A

 

SELECT song, id FROM A WHERE
MATCH (song) AGAINST ('britney' WITH QUERY EXPANSION) AND
NOT EXISTS ( SELECT * FROM B WHERE A.id = B.id ) 
LIMIT 0,10;

 

 

info: mysql 5.0

I've looked into INNER LEFT JOIN but can't figure out how to do the filtering (my MATCH) in it

 

any help will be much appreciated

 

p.s. there might be typos (causing syntax errors) in my code above, but that's not my problem, my queries run fine on my server, just return an undesired results

 

thank you

 

acer

[Barand]

You need

 

SELECT A.song, B.id

FROM A

LEFT JOIN B USING (id)

WHERE

MATCH (A.song) AGAINST ('britney' WITH QUERY EXPANSION) AND

B.id IS NULL

LIMIT 0,10;

[acerbiter]

thank you for the reply, but it doesn't seem to be working... the AND B.id IS NULL seems to have no effect. I got it working with LIKE statement though

 

SELECT A.song, B.id
FROM A
LEFT JOIN B USING (id)
WHERE
A.artist LIKE '%britney%' AND
B.id IS NULL
LIMIT 0,10;

 

works just fine but

 

SELECT A.song, B.id
FROM A
LEFT JOIN B USING (id)
WHERE
MATCH (A.artist) AGAINST ('britney' WITH QUERY EXPANSION) AND
B.id IS NULL
LIMIT 0,10;

 

ignores the B.id IS NULL directive

 

any idea why that might be happening?

 

thank you

 

acer

[acerbiter]

ah sorry, seems the result were being cached, both solution do work indeed.

 

thank you guys