Re: SQL Newbie - Join Question

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: jack.lee (lee_j_at_21cn.com)
Date: 11/10/04 

Date: Wed, 10 Nov 2004 13:49:38 +0800

Hi Robert,
        You should use self join like that.
            select d.UserID,c1.desc as Desc1, c2.desc as Desc2 from
Data-Table d inner join Code-Table c1
        on d.code1 = c1.code inner join Code-Table c2 on d.code2 = c2.code

Thanks!
Jack

"Robert" <Robert@thomasgeorge.com> ????
news:3811e7d4.0411092041.5cee9d71@posting.google.com...
> I have a table with codes in it and I want to return the lookup values
> all in one query....an example is probably easiest:
>
> Code-Table:
>
> code desc
> ---- ----
> 1 desc1
> 2 desc2
> 3 desc3
>
>
> Data-Table:
>
> UserID Code1 Code2
> ------ ----- -----
> ABC 1 2
> DEF 2 3
>
>
> What I want to return is the following:
>
> UserID Desc1 Desc2
> ------ ----- -----
> ABC desc1 desc2
> DEF desc2 desc3
>
>
> I am assuming there is some type of JOIN I should be using, but I
> can't figure out how to get the two differet Desc values from the
> Code-Table in one query. A regular JOIN would get me one of the two
> Desc values, but I need to get two different Desc values by using two
> different WHERE clauses on the same table.
>
> Any solutions would be greatly appreciated!

Relevant Pages

  • Re: Abbreviation List Tables Design, aka OTLT
    ... each lookup "file" is a separate item in a master file of files. ... > just un-normalized OTLT. ... Your ID can be the name of the file with the codes being associated ... It is almost always dismissed as a terrible design by ...
    (comp.databases.pick)
  • Re: nss_ldap and openldap on the same server.
    ... Look for Status codes and Actions ... Reading getgrouplistturns out the following: ... The getgrent() and getgrent_rfunctions make no attempt to suppress duplicate information if multiple sources are specified in nsswitch.conf. ... So after following the way through all man pages, it turns out that the behavior is fully correct as a lookup is done to find out all groups to which the specified slapd user belongs to. ...
    (freebsd-questions)
  • Re: Abbreviation List Tables Design, aka OTLT
    ... instance of any type of lookup code is a separate item in a file. ... Maybe that's just un-normalized OTLT. ... How do you select all states vs all status codes? ... It is almost always dismissed as a terrible design by relational or SQL folks, likely for good reasons if you are thinking in terms of constraints on attributes. ...
    (comp.databases.pick)
  • Re: "code" tables?
    ... "We are planning on creating 'account opening sources' lookup table to ... 'account source' codes and corresponding descriptions. ... The desire to conceal complexity is not the same as the desire to remove ...
    (comp.databases)
  • Re: nss_ldap and openldap on the same server.
    ... Look for Status codes and Actions ... Reading getgrouplistturns out the following: ... The getgrent() and getgrent_rfunctions make no attempt to suppress ... behavior is fully correct as a lookup is done to find out all groups to ...
    (freebsd-questions)