Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    Regular Coder
    Join Date
    Jun 2004
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SELECT from a table depending on a result from another table

    This may sound a bit weird so i'll explain:

    I have three tables:
    Code:
    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?

  • #2
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    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?

    Code:
    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

  • #3
    Regular Coder
    Join Date
    Jun 2004
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Fumigator View Post
    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?

    Code:
    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

    thnx!

    (here's your rep )


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •