Results 1 to 3 of 3

Thread: Even trickier join :-)

  1. #1
    Join Date
    Jan 2003
    Posts
    2

    Unhappy Even trickier join :-)

    I seem to hang mySql every time I try a variation of this query, so I must be doing something wrong...

    Scenario - simplified to two tables:

    Table1: THINGY

    id | name
    ------------
    1 | aaa
    2 | bbb
    3 | ccc


    Table2: ATTRIBUTE

    id | thingy_id | type | value
    ----------------------------------
    1 | 1 | you | 500
    2 | 1 | silly | 600
    3 | 2 | you | 700
    4 | 2 |sausage| 800
    5 | 3 | silly | 900
    6 | 3 |sausage| 1000


    I want a query that will select *all* thingy's, and show the value of *a particular type* of their attribute, regardless of whether or not it exists (i.e. give me NULL if it doesn't).


    example: get the value of "you" for each thingy

    Result:

    name | type | value
    ------------------------
    aaa | you | 500
    bbb | you | 700
    ccc | NULL | NULL


    I tried this:

    select t.name, a.value from THINGY as t left join ATTRIBUTE as a on (a.thingy_id = IF(a.type="you", a.id, NULL));

    But that just hangs the server (overnight!)


    Any ideas?

    Thanks!!!

    Mark

    markw@illuminae.com

  2. #2
    Join Date
    Jan 2003
    Posts
    2
    I think the below should work.

    SELECT t.name, a.type, a.value
    FROM thingy t
    LEFT OUTER JOIN attribute AS a ON t.id = a.thingy_id AND a.type = 'you';

    Good luck.

  3. #3
    Join Date
    Jan 2003
    Posts
    2
    Yes, it does work. I just found this in the mysql online documentation too :-)

    thanks!

    mark

Posting Permissions

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