Miguel Vaz wrote:
> How would one go about doing this:
> - I have 3 tables:
> - A relationship table(REL), then TABLE1 and TABLE2:
> REL TABLE has fields:
> . ID
> . TYPE - type of event
> . ID_EVENT - id of event, but this id will either point to TABLE1 or TABLE2,
> depending on the content of the field "TYPE"
> Is it possible to do everything on the same select? I mean, the join will
> use a different table depending on the content of one of the fields. This
> join will retrieve the name of the event, either from TABLE1 or 2. Or should
> i just do a select to get the first row content, and then get the rest
You can do it if you UNION your results together like this:
INNER JOIN TABLE1
ON REL.somecolumn = TABLE1.somecolumn
AND REL.type = 'table1-type-value'
) UNION (
INNER JOIN TABLE2
ON REL.somecolumn = TABLE2.somecolumn
AND REL.type = 'table2-type-value'
or, you can conditionally select which columns to return like this
, if (REL.type = 'table1-type-value1', t1.column1, t2.column1) as column1
LEFT JOIN TABLE1 t1
on t1.somecolumn = REL.somecolumn
and REL.type = 'table1-type-value'
LEFT JOIN TABLE1 t2
on t2.somecolumn = REL.somecolumn
and REL.type = 'table2-type-value'
But typically,if your REF table refers to two separate tables, it will
be much faster to access your data if you split it into two REF tables,
one that points only to TABLE1 rows and one that points only to TABLE2
rows. That kind of separation of purpose is also known as "normalization".
Shawn Green, MySQL Senior Support Engineer
Sun Microsystems, Inc.
Office: Blountville, TN