[SOLVED] Omitting specifuc data in resuls table

[php_fish]

I have 3 tables;

 

#######table content####

idcontent             title copy

------------------------------------

1 title1 text1

2 title2 text2

3 title3 text3

4 title4 text4

 

#######table images ####

idimg file

-------------------

1 img1.jpg

2 img2.jpg

3 img3.jpg

4 img4.jpg

 

####### assigned images ####

idassign             idcontent            idimg

------------------------------------------

1 1 2

2 1 1

3 3 2

4 4 3

 

I'm trying to build a query that will give me all images that are not assigned to a specific content id. Because the same image can be assigned to any number of contentid's I'm finding it tricky.

 

The sql I'm using at the moment is a simple select all images

SELECT file, idimg FROM images

 

I've tried various other queries involving joins and sub queries but I just can't work it out. MySQL is not my strongest skill

 

Any help will be greatly appreciated

[fenway]

Why not a LEFT JOIN... IS NULL?  NOT EXISTS might work too.

[php_fish]

I'm not getting very far, have spent several days trying out your suggestion but with no luck.

 

Can anyone advice me on the syntax of such a query. Can it be done without the intervention of php?

[kickstart]

Hi

 

Can be done in the way Fenway suggested.

 

Something like this:-

 

SELECT a.idimg
FROM images a
LEFT OUTER JOIN assignedimages b
ON a.idimg = b.idimg
WHERE b.idimg IS NULL

 

Basically join the table of images using a LEFT OUTER JOIN to the link table. This should give you one or more returned rows for each image, one for each entry on the link table. If there is nothing on the link table then it will return a row with NULL fields from the link table. The query merely discards all except those which have NULL fields on the link table.

 

All the best

 

Keith

[php_fish]

tried that, it doesn't give me want I want. I want all images except for those assigned to record 1 in he content table (for example). Since an image can be assigned to more than one content record that query still returns the images that record 1 has assigned to it because other content records have the same images assigned.

[fenway]

Well, then you need to specify this content id in your ON clause.

[php_fish]

I've tried;

 

SELECT a.idimg

FROM images a

LEFT OUTER JOIN assignedimages b

ON a.idimg = b.idimg

WHERE b.idimg IS NULL

AND assignedimages.idcontent != 1

 

again with no luck

[kickstart]

Hi

 

The way you have tried it will throw them away after the join. You want to discard them before the join:-

 

SELECT a.idimg
FROM images a
LEFT OUTER JOIN assignedimages b
ON a.idimg = b.idimg
AND b.idcontent = 1
WHERE b.idimg IS NULL

 

All the best

 

Keith

[php_fish]

Yes, that works perfectly. Once again you've helped me out. Thank you!