List:General Discussion« Previous MessageNext Message »
From:Shawn Green Date:December 17 2009 8:55pm
Subject:Re: Join using Table1 or Table2 - depending on content of rel table
View as plain text  
Miguel Vaz wrote:
> Hi,
> 
> 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
> afterwards?
> 
> 
> Thanks,
> 
> MV
> 

You can do it if you UNION your results together like this:

(
SELECT ...
FROM REL
INNER JOIN TABLE1
   ON REL.somecolumn = TABLE1.somecolumn
   AND REL.type = 'table1-type-value'
WHERE ...
) UNION (
SELECT ...
FROM REL
INNER JOIN TABLE2
   ON REL.somecolumn = TABLE2.somecolumn
   AND REL.type = 'table2-type-value'
WHERE ...
)

or, you can conditionally select which columns to return like this

SELECT ...
, if (REL.type = 'table1-type-value1', t1.column1, t2.column1) as column1
, ...
FROM REL
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'
WHERE ...

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


Thread
Join using Table1 or Table2 - depending on content of rel tableMiguel Vaz13 Dec
  • Re: Join using Table1 or Table2 - depending on content of rel tableShawn Green17 Dec