List:General Discussion« Previous MessageNext Message »
From:John List Date:July 26 2010 4:24am
Subject:Re: Need Help Writing Simple Query
View as plain text  
On 07/25/2010 09:29 PM, Mark Phillips wrote:
> I have been away from sql for awhile, and can't seem to figure out how to
> write a simple query for two tables.
>
> Table 1 has many columns, two of which are hID and vID. Table 2 has two
> columns, ID and name. The hID and vID in table 1 correspond to the IDs in
> table 2. I want to make a query so I get all the columns from table 1, but
> substitute the names from table 2 for the hID and vID values. For example,
>
> Table 1:
> col 1, col 2, hID, vID, col 3
> A        B        1     2     C
>
> Table 2:
> ID, name
> 1    fred
> 2    sam
>
> Query result:
> col1, col 2, hName, vName, col 3
> A       B        fred      sam       C
>   

select a, b, c,
hTable.name as hName,
vTable.name as vName
from Table1,
Table2 as hTable,
Table2 as vTable
where hId = hTable.ID and vID = vTable.ID

or

select a, b, c,
hTable.name as hName,
vTable.name as vName
from Table1
left join Table2 as hTable on hID = hTable.ID
left join Table2 as vTable on vID = vTable.ID



Thread
Need Help Writing Simple QueryMark Phillips26 Jul
  • Re: Need Help Writing Simple QueryNguyen Manh Cuong26 Jul
    • Re: Need Help Writing Simple QueryMark Phillips26 Jul
  • Re: Need Help Writing Simple QueryJohn List26 Jul