bit o' SQL help

Man of Honour
Joined
18 Oct 2002
Posts
13,262
Location
Northallerton/Harrogate
I have a few tables that use <location1ID> and <location2ID> - these are FK's from another table, <locations> - this has columns:
ID, Port, Slot, Station, Module

For each row in this table, you'd have
ID, something, something, null, null
or
ID, null, null, something, something

What I want to do is use the somethings (their text values) rather than the numerical ID when displaying the results of a query..


how do I do that?
 
As above, but something like this will work, whether or not it's the best way who knows :p..

PHP:
SELECT * FROM [table] tbl
LEFT OUTER JOIN [locations] loc ON loc.ID = tbl.location1ID
LEFT OUTER JOIN [locations] loc2 ON loc.ID = tbl.location2ID

left outer join means that if either location1 or location2 is NULL the query will still work.
 
Do you mean you want to show the Port, Slot, Station, Module fields in the results rather than the ID?
Code:
Select coalesce([locations].Port, [locations].Slot, [locations].Station, [locations].Module)
From [locations]
Inner join [otherTable]
on [otherTable].Location1ID = [locations].ID


will show the first non null field in the list.
 
Back
Top Bottom