Problems quering 3 tables in one statment (GROUP BY)

[jggretton]

Hello, below is my rather complecated sql query. It does two things:

a) Gets all the articles in catagory '4'
b) Gets all the articles in catagories with parent catagory '4'

(a) and (b) run fine on their own, but when i combine them with an OR statement, i get multiple (seemingly) redundant results. Only one of which has the desired catagory information. If i use "GROUP BY a.`id`" I get the right results, but not the right catagory information for (b).

Appologies if this is uninteligible!!! I could provide a database dump if it would help?

Query below:

Thanks,

James

SELECT a.* , a_c.`catagory`
FROM
`articles` AS a, `article_catagories` AS a_c, `catagories` AS c
WHERE (
a_c.`catagory` =4
OR (
c.`id` = a_c.`catagory`
AND c.`parent` =4
)
)
AND a.`id` = a_c.`article`

 

[piti]

just a guess

SELECT a.* , a_c.`catagory`
FROM
`articles` AS a 
left join `article_catagories` AS a_c on a.`id` = a_c.`article` 
left join `catagories` AS c on c.`id` = a_c.`catagory`
WHERE 
a_c.`catagory` =4
OR c.`parent` =4

if this is not what you are looking for, maybe post table structure (relevant info) and some data examples

 

[jggretton]

Awesome thanks piti. That works like a charm. I was under the false impression that my mySQL version didn't support joins - I couldn't have been more wrong! Because I didn't think I supported them, i've never taken the time to learn about them before. That'll change now!

Many thanks,

James

 

[guelphdad]

mysql has always supported joins. Most likely you have seen them in the deprecated list join syntax like this:

select
foo,
bar,
qux
from table1, table2
where somecolumn=someothercolumn