Mysql join

[themistral]

Hi,

 

I am having a bit of trouble in finding some mysql logic.

 

I have a categories table and a content table but the content can be assigned 3 categories. I want to join the table if either of the 3 categories match the category in the categories table.

 

This is just a small part of a bigger query.

 

This is basically what I think I need...

 

SELECT a.cat1, a.cat2, a.cat3

FROM tablea a

LEFT JOIN tableb b ON (b.id = b.cat1 OR b.id = a.cat2 or b.id = a.cat3)

 

I'm not even sure if this is possible but any pointers will be gratefully received and tried!!

[btherl]

You might want to use a union here.. something like:

 

SELECT a.cat1
FROM tablea a
JOIN tableb b ON (b.id = a.cat1)
UNION ALL
SELECT a.cat2
FROM tablea a
JOIN tableb b ON (b.id = a.cat2)
UNION ALL
SELECT a.cat3
FROM tablea a
JOIN tableb b ON (b.id = a.cat3)

 

JOIN makes more sense to me here rather than LEFT JOIN.  I'm not 100% sure of the syntax there as I am not a mysql user.

[themistral]

Thanks for that btherl!

 

On it's own, that works.

However, I am trying to add in to a query that already exists.

 

The full query is

 

SELECT j.field1...j.field20, m.field1, n.field1 
FROM tablej j 
LEFT JOIN tablem m ON m.field1 = j.field10
LEFT JOIN tablen n ON n.field1 = j.field2

 

WHERE statement is dynamically generated depending on user input

 

I have tried to do the whole query using single SELECT statements and the UNION to join them, but I keep getting an error saying "The used SELECT statements have a different number of columns"

 

How do I incorporate the UNION part shown above?

[btherl]

Hmm.. it's possible that mysql is misinterpreting the way that you combined everything.

 

It's hard to guess without seeing the full query and where it should be combined, but you may have to repeat the WHERE condition for EVERY select .. that will give you an ugly query but mysql won't mind.

 

While you can factor out the "where", performance may suffer unless the query optimizer knows it can apply it to each of the unioned subqueries.

 

Can you post the attempt you made at combining?

[themistral]

I think I may have solved it - will post if it works, but out of curiosity, which is more efficient - a query with multiple joins or a bunch of queries combined using union?

[fenway]

I think I may have solved it - will post if it works, but out of curiosity, which is more efficient - a query with multiple joins or a bunch of queries combined using union?

Depends... usually you can't exactly replicate UNION withj JOINS.s