query problem...

  • hi, i have the following query problem:

    let's assume that there are two tables (a and b).

    table a contains two fields (f1 and f2) which contain

    unique ids pointing to table b. table b contains the

    details to a.f1 and a.f2. is there a possiblility to

    retrieve the detailed data from table b with a single

    sql statement? should be like: a.f1, a.f2, b.detail1,

    b.detail2. in other words: there is two links to table

    b per row in table a. hope this is clear enough...

    table a




    table b




    thank you for your help and suggestions...



  • This may not be the most efficient way, but it works:

    SELECT DISTINCT a.f1, b.detail, c.f2, c.detail

    FROM a JOIN b ON a.f1 = b.[id]

    JOIN (SELECT a.f2, b.detail

    FROM a JOIN b ON a.f2 = b.[id]) AS c

    ON a.f2 = c.f2

    Without the DISTINCT you will get duplicates.

    K. Brian Kelley


    K. Brian Kelley

  • Another Solution:

    SELECT [f1], [f2],temp1.detail1,temp2.detail2 FROM [a]

    left outer join (select [id] id1 ,detail detail1 from b) temp1 on temp1.id1=f1

    left outer join (select [id] id2,detail detail2 from b) temp2 on temp2.id2=f2

  • thanks guys! this worked great on my sql server! there's one more problems with these queries: it should work on access mdbs (jet-sql) and sql server at the same time... in access i get syntax error messages. i am relatively new to sql and therefor don't know much about the differences in all the dialects. anybody out there who can help me with the access specific solution? thank you in advance...

  • SELECT a.f1, a.f2, temp1.detail, temp2.detail

    FROM (a LEFT JOIN b AS temp1 ON a.f1 = temp1.id) LEFT JOIN b AS temp2 ON a.f2 = temp2.id;

  • thanks! you guys rule! it works just fine...

  • I would sugest a book on T-SQL, the guys here recommend one I personally use the O'Relly press T-SQL book and have for quite a while.


  • yep, i guess a book sounds like a smart idea... thanks for tho specific tip.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply