[RESOLVED] Join issues.

leatherback

Hi All,

Can somebody please help me with this.. I just cannot get my head around it:

I am cleaning up some old code in a website I build years ago. So please do not mind the colum names. They will eventually be adapted too, I suppose.

I have three tables:
w_images (Id, Name)
w_imgtotheme (ImgNr, ThemeNr)
w_theme (Id, Themes)

This effectively create the option to have many-to-many relations: Each image can belong to each theme, and each theme can be linked to each image.

How I want to select images & themes from these tables. THis is easy enough with a join syntax. But when I start adding conditions to the statement, it fails. I am sure there is somwething wrong with the join type, but do not know what:

This

SELECT * FROM w_images 
inner join w_imgtotheme 
  on w_images.Id = ImgNr 
inner join w_theme 
  on w_theme.Id = ThemeNr 
ORDER BY w_images.Id

results in: Showing rows 0 - 29 (431 total)
Which is expected, with 181 images, and a number of themes per image.

However, this:

SELECT *
FROM w_images
inner join w_imgtotheme
on w_images.Id = ImgNr
inner join w_theme
on w_theme.Id = ThemeNr
where Themes = 'Landmarks'
and Themes = 'Buildings'

gives me 0 results. This is because it thinks there cannot be this option. I know there are images which belong to both Landmarks and Buildings. How do I change the query so that it will retrieve all the combinations?
So:
img1, Themes: Landmarks, buildings, human
img2, Themes: Buildings, Human
Query: select ... Themes = Landmarks

Output:
Img1 - Landmarks
Img1 - Buildings
Img1 - human

Hope this is clear enough. Let me know if you need more info.

Roger_Ramjet


 SELECT *
FROM w_images
inner join w_imgtotheme
on w_images.Id = ImgNr
inner join w_theme
on w_theme.Id = ThemeNr
where Themes LIKE '%Landmarks%'
and Themes LIKE '%Buildings%'

I'm pretty sure that your joining 3 tables like that is going to return the wrong results at least some of the time though.

leatherback

Hi ROger,

It is not a syntax-type issue. The way I posted my query does work in general: If I only use ONE condition:

SELECT *
FROM w_images
inner join w_imgtotheme
on w_images.Id = ImgNr
inner join w_theme
on w_theme.Id = ThemeNr
where Themes = 'Landmarks'

it gives me the records that match. (I really do not like the "like %..%" kind of statements, since I do know exactly which categories exists.) I would just like to select for multiple categories, and find out in which categories I still have matches.

Oh, yeah.. Why do you think that the joiining like that should give me the wrong results?

xblue

Please correct me if I'm wrong, but shouldn't it be OR?

leatherback

Hi Xblue,

Well, yes and no..
What I am trying to achieve is to select all the pictures that have multiple labels. See it as a recursive tree I am building: You can have a picture that matches both trees as grass as natural areas. So in this case I would need an AND.

Perhaps I am just wrong, and this is not possible in one query, but I find it hard to believe.

Roger_Ramjet

Sorry, I misunderstood your table layouts: thought that keywords were a list all in one column. 😃

Using your current syntax you elliminate every row in w_image because no theme is going to be equal to BOTH 'landmarks' AND 'buildings'. This needs to be an OR to allow the joins to return any rows at all with that syntax - not what you want.

Several ways suggest themselves, multiply the joins and use table aliases, group by and count(), even subqueries. Try this, should work:

 SELECT *
FROM w_images
inner join w_imgtotheme
on w_images.Id = ImgNr
inner join w_theme AS t1
on t1.Id = ThemeNr
inner join w_theme AS t2
on t2.Id = ThemeNr
HAVING t1.theme='Landmarks' AND t2.theme='Buildings'

leatherback

Hi,

did not work :-(

I suppose I will try with double queries or so..

leatherback

Well, in case somebody runs into the same sort of problems: this works (If somebody knows a better weay to do it, I am still interested!

select distinct I_id, I_name 
from f_images inner join 
( f_theme inner join f_imgtotheme on ( ( T_id = It_themenr) 
and ( ( T_themes = 'Human' ) or ( T_themes = 'Buildings') ) )
) on ( I_id = It_imgnr )
group by I_id having count(I_id) = 2

please note: the tables I am using have been restructured to match my standard setup, which is different from the first post.