...

View Full Version : SELECT from a table depending on a result from another table



maghiel
04-11-2007, 03:03 PM
This may sound a bit weird so i'll explain:

I have three tables:


tbl1 foo bar
------ ------ ------
id id id
type enum('foo', 'bar') info info
typeid


Now I want to get the info from either foo or bar depending on tbl1.type
Is it possible to do this in one select statement?

Fumigator
04-11-2007, 05:51 PM
Is there a foreign key in foo or bar that connects rows to tbl1? Or do you want to select every row in foo or bar when your enum field equals foo or bar?



SELECT CASE t.enumfield
WHEN 'foo' THEN (SELECT f.field1 FROM foo as f WHERE ????)
WHEN 'bar' THEN (SELECT b.field1 FROM bar as b WHERE ????)
END
FROM tbl1 as t

maghiel
04-12-2007, 09:22 AM
Is there a foreign key in foo or bar that connects rows to tbl1? Or do you want to select every row in foo or bar when your enum field equals foo or bar?



SELECT CASE t.enumfield
WHEN 'foo' THEN (SELECT f.field1 FROM foo as f WHERE ????)
WHEN 'bar' THEN (SELECT b.field1 FROM bar as b WHERE ????)
END
FROM tbl1 as t


typeid is the foreign key so yeah got it :)

and thnx really much m8! never new constructions like these were possible in mysql, probably will be using them a lot now :) good thing i asked :D

thnx!

(here's your rep ;))



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum