MySQL syntax help needed

Question

I am completely stuck on this and would appreciate help from someone who is more fluent in sql than me!

Situation: I have Images which can belong to up to 3 Categories. I have to look them up using the category name (e.g. 'School'). I have an Image table, a Category table, and a CategoryImage table as a join table

The essential fields are

Category
    Id
    categoryname
Image
    Id
    imagename
    description
CategoryImage
    categoryId
    imageId

If Image '001' belongs to the categories called 'School' and 'Home' it will have 2 entries in CategoryImage.

I need the sql to find all images which are in BOTH the 'School' AND the 'Home' category. I can see that this sql is probably trying to return a single image where the category is 'School AND 'Home' which is clearly impossible. [ Changing the AND to an OR finds images which be log to the 'School' category plus also images which belong to the 'Home' category, which is not what I need.

SELECT 
   DISTINCT t0.description, 
   t0.imagename
FROM 
   Image t0, Category T2, CategoryImage T1 
WHERE 
  (T2.name = "School" 
  AND T2.name = "Home")  
  AND T1.categoryId = T2.id 
  AND t0.id = T1.imageId

Thanks in advance for any suggestions.

Suggestion #0, stop using implict joins. Use explicit `JOIN` syntax. — Kermit, May 01 '14 at 22:20
And how `T2.name = "School" AND T2.name = "Home"` can be possible? — potashin, May 01 '14 at 22:22
Perhaps you meant "IN('x','y') ... GROUP BY ... HAVING COUNT(*) = 2 — Strawberry, May 01 '14 at 22:22
Either change the AND to OR in your `T2.Name =` test, or change it to `WHERE T2.Name IN ('School', 'Home')` — Ken White, May 01 '14 at 22:23
[This post](http://stackoverflow.com/questions/44917/explicit-vs-implicit-sql-joins). — Brandon, May 01 '14 at 22:23
Find images of one category and theother one separately and UNION results. — Naeel Maqsudov, May 01 '14 at 22:35

potashin · Answer 1 · 2015-04-25T16:45:39.250

0

Try something like this :

select distinct t0.description 
     , t0.imagename 
from categoryimage T1 
join image t0 on t0.id = T1.imageid 
join category T2 on T1.categoryid = T2.id 
where  T2.name in ( 'School', 'Home')

edited Apr 25 '15 at 16:45

answered May 01 '14 at 22:21

potashin

44,205
11
83
107

score 0 · Answer 2 · answered May 01 '14 at 22:29

0

this will get you all images which are in both the school and home categories

SELECT 
   i.description, 
   i.imagename
FROM 
   Image i
WHERE EXISTS (SELECT * 
              FROM Category c 
              INNER JOIN CategoryImage ci ON ci.categoryid=c.id 
              WHERE ci.imageid=i.id AND c.name="School")
   AND EXISTS (SELECT * 
              FROM Category c 
              INNER JOIN CategoryImage ci ON ci.categoryid=c.id 
              WHERE ci.imageid=i.id AND c.name="Home")

answered May 01 '14 at 22:29

attila

2,219
1
11
15

IMHO, two correlated subqueries look worse then UNION of two HASH JOINS – Naeel Maqsudov May 01 '14 at 22:33
@NaeelMaqsudov - I am pretty sure your SQL will include all images which exist in either school or home categories. The OP is looking for ones which exist in both. – attila May 01 '14 at 22:37

score 0 · Answer 3 · answered May 01 '14 at 22:29

SELECT 
  t0.description, t0.imagename
FROM 
  Image t0 
  INNER JOIN CategoryImage T1 ON (t0.id = T1.imageId)
  INNER JOIN Category T2 ON (T1.categoryId = T2.id)
WHERE 
  T2.name = "School"
UNION
SELECT 
  t0.description, t0.imagename
FROM 
  Image t0 
  INNER JOIN CategoryImage T1 ON (t0.id = T1.imageId)
  INNER JOIN Category T2 ON (T1.categoryId = T2.id)
WHERE 
  T2.name = "Home"

score 0 · Answer 4 · answered May 01 '14 at 22:33

0

SELECT DISTINCT(i.description), i.imagename 
FROM CategoryImage ci 
LEFT JOIN Category c
ON ci.categoryId = c.Id
LEFT JOIN Image i 
ON ci.imageId = i.Id
WHERE c.categoryname IN ( 'School', 'Home' )

answered May 01 '14 at 22:33

Kirk Logan

733
2
8
23

MySQL syntax help needed

4 Answers4