CODEWITHSUNDEEP
phpmysql
Dimitris Damilos
Dimitris Damilos

Reputation: 2438

mySQL double filtering on the same query

I have a mySQL database which includes 3 tables to associate galleries with images. The images are stored in the table "files" and the galleries in the table "galleries", so I use a middle table called "gallery_files" for the association between them. The "gallery_files" table consists of the 2 IDs (F_ID, G_ID) of each of the main tables respectively.

The files/images can be associated with more than one gallery, so there can be records in the "gallery_files" of the same F_ID for different G_IDs.

Now, I would like to make a mySQL query which would bring all the images that ARE NOT associated with a given gallery already. So I use the following query:

(let's say for the gallery with ID: 9)

SELECT * FROM files
INNER JOIN gallery_files ON files.F_ID=gallery_files.F_ID
WHERE files.F_FILETYPE IN ('.jpg','.jpeg','gif','png')
  AND files.F_DELETED = 0
  AND gallery_files.G_ID <> 9

My problem is that if there is a record of an image associated with a different gallery it still brings it. I know why this happens, but I cannot think of a smart query to do the job. I know how to solve this by using 2 queries but I would like to avoid it, if possible.

Any thoughts?

Upvotes: 1

Views: 386

Answers (2)

Ian Clelland
Ian Clelland

Reputation: 44132

Without a subquery, you can use an outer join, and check for NULL columns to only select those rows that don't match the inner join:

SELECT * FROM files
LEFT JOIN gallery_files ON files.F_ID=gallery_files.F_ID AND gallery_files.G_ID = 9
WHERE files.F_FILETYPE IN ('.jpg','.jpeg','gif','png')
  AND files.F_DELETED = 0
  AND gallery_files.G_ID IS NULL

By putting the G_ID condition in the ON clause, the JOIN joins each file to its gallery-9 row from the gallery_files table, if that exists, or to NULLs if it doesn't (since it's an outer join).

Then, by checking for NULL in the gallery_files columns (I checked G_ID, but any column would have worked), we only get those rows from files which were not in gallery 9.

Upvotes: 1

random_user_name
random_user_name

Reputation: 26160

I believe a NOT IN statement should do what you want.

Something along the lines of:

SELECT * 
FROM files 
WHERE files.F_ID NOT IN 
    (SELECT files.F_ID 
    FROM files 
    INNER JOIN gallery_files ON files.F_ID=gallery_files.F_ID 
    WHERE files.F_FILETYPE IN ('.jpg','.jpeg','gif','png') 
    AND files.F_DELETED = 0 AND gallery_files.G_ID = 9)

Upvotes: 0

Related Questions