Join using Table1 or Table2 - depending on content of rel table

--000325556bba235f39047aa3e50a
Content-Type: text/plain; charset=ISO-8859-1

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

--000325556bba235f39047aa3e50a--

Re: Join using Table1 or Table2 - depending on content of rel table

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



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org