Andrew Andrew - 4 months ago 8
SQL Question

Write a sql statement to query one table based on columns in other tables?

I have 3 tables:

pics
,
folders
, and
pics_by_folders
.

pics

pic_ID | name | info


folders

folder_ID | name | info


pics_by_folders

folder_ID | pic_ID


I am trying to return all
pics
that meet requirements x,y,z but only if they are contained in folders meeting requirements j,k,l. I hope I am explaining this in a way that makes sense. My current statement is:

SELECT * FROM pics WHERE
pic_ID = 'x' AND
pics.name = 'y' AND
pics.info = 'Z'
JOIN pics_by_folder as pbf ON
pbf.pic_ID = pics.pic_ID
JOIN folders WHERE
folder_ID = 'j' AND
folders.name = 'k' AND
folders.info = 'l'
ON
folders.folder_ID = pbf.folder_ID


This isn't working and I was thinking it had something to do with the ordering of statements or maybe sql doesn't allow
"JOIN x WHERE ..."
statements. Either way, any help would be appreciated.

Answer

Wrong syntax!!!.

SELECT 
 * 
FROM pics 
JOIN pics_by_folder as pbf ON pbf.pic_ID = pics.pic_ID
JOIN folders ON folders.folder_ID = pbf.folder_ID
WHERE folder_ID = 'j' AND 
   folders.name LIKE 'k' AND 
   folders.info LIKE 'l' AND
   pic_ID LIKE 'x' AND 
   pics.name LIKE 'y' AND 
   pics.info LIKE 'Z'

Some notes:

Please always specify which columns you do want in your result set. Otherwise you will be confusing yourself because you don't know how many columns will there be in the result set.

folders.name LIKE 'k' is equivalent to folders.name = 'k'

if you want exact match then use folders.name ='k' otherwise use wild card operator % like below: folders.name LIKE '%k%'