Help With Muti-table Query

[tako 0]

I am trying to get a category name form a table(categories) based form a id form another table(stories),

I can kinda do it but it gives me the wrong one because it's in more than one category. here's the two tables with only the fields I need to use and sample data in them.

 

categories[TABL]

catidparentcatidcategory other fields562Right one 1other datas572Right one 2 other datas60-1Wrong one 1other datas615Wrong one 2other datas[/TABL]

 

stories[TABL]

sidcatidother fields16060,56other datas[/TABL]

all the right categories will have a parentcatid of 2 and none ofthe wrong ones will.I tried doing it like this but I only get no results

SELECT c.category
FROM categories c, stories s
WHERE s.sid =160 AND c.catid IN(s.catid) AND c.parentcatid = 2;

I tried doing it like this but I only get "Wrong one 1"

SELECT c.category
FROM categories c, stories s
WHERE s.sid =160 AND c.catid IN(s.catid);

I'm not really any good at mysql, but is there any why I could do it so it would return "Right one 1"?

 

thanks alot.

Edited December 13, 2009 by tako (see edit history)

[yordan 10]

Maybe you should read your manuals again, paying more careful attention to the sql join syntax.

[bradzo 0]

I am trying to get a category name form a table(categories) based form a id form another table(stories),

I tried creating a small Access db, with stories.catid as a text field (so as to accommodate your multiple values ie: "60,56") but I get a type mismatch error from Access. I changed the stories.catid to a numeric field, and added a new row - it looks like this:

 

stories[TABL]

sidcatidother fields16060other datas16156other datas[/TABL]

 

 

You'll need to change the structure of your stories table obviously, because sid (story id) needs to be unique (it is the primary key)

 

Since you say parentcatid will be 2 for correct categories (the ones you want) why not query on that instead of catid in stories?

 

Do this:

 

SELECT c.categoryFROM categories c, stories swhere c.catid = s.catidand c.parentcatid = 2

You may need to rethink your design also - if the story having sid = 160 belongs in two categories (60 and 56 as per your original stories table) but catid = 60 will return "wrong one 1" then why do you have it listed in stories in the first place? On the surface, the data you've presented doesn't make sense to me.

 

Cheers

Brad