Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2003
    Posts
    11

    Unanswered: need help with query

    i'm actually using postgreSQL but sql language doesnt change for mySQL, right? and nobody replied over at the postgre section so i tought maybe somebody here would

    i'll describe my problem with a simplier example
    lets say i have the following tables:

    create table names(
    id serial PRIMARY KEY,
    name text
    );

    create table x(
    x1 integer REFERENCES names (id),
    x2 integer REFERENCES names (id),
    x3 integer REFERENCES names (id)
    );

    so... my names table is populated with

    id | name
    -----------
    1 | name1
    2 | name2
    3 | name3
    4 | name4
    5 | name5

    and my x table contains the following:

    x1 | x2 | x3
    --------------
    2 | 3 | 5
    1 | 4 | 2


    now, what i need is to make a query similar to:
    select name from names,x where x.x1=names.id;


    but the tricky thing is that i need to list the 3 names
    for example for the first row of the x table (2, 3,5), i would need to get a list looking like this:

    name | name | name
    ----------------------------
    name2 | name3 | name5




    i have tried a lot of combinations of queries but havent succeded, the closest i get is

    name
    --------
    name2
    name3
    name5


    if this is not possible let me know so i can stop trying

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    hmmm, i missed your post in the postgresql forum, i would've responded earlier, must have been the [hic] christmas spirits, eh
    Code:
    select n1.name as name1
         , n2.name as name2
         , n3.name as name3
      from x
    inner
      join names as n1
        on x1 = n1.id   
    inner
      join names as n2
        on x2 = n2.id      
    inner
      join names as n3
        on x3 = n3.id
    rudy
    http://r937.com/

  3. #3
    Join Date
    Dec 2003
    Posts
    11
    thanks!!!
    i would have never come up with that

Posting Permissions

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