Post reply

query problem...

  • July 13, 2001 at 10:06 am

    #77367

    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

    -------

    f1

    f2

    table b

    -------

    id

    detail

    thank you for your help and suggestions...

    cheers,

    stefan

  • July 13, 2001 at 10:44 am

    #417069

    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

    bkelley@agfirst.com

    K. Brian Kelley
    @kbriankelley

  • July 14, 2001 at 4:36 pm

    #417075

    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

  • July 16, 2001 at 4:36 am

    #417082

    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...

  • July 16, 2001 at 9:09 am

    #417088

    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;

  • July 16, 2001 at 9:42 am

    #417089

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

  • July 16, 2001 at 10:32 pm

    #417110

    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.

    Wes

  • July 17, 2001 at 2:12 am

    #417113

    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