Can this be done??

[Laeg]

tblThings
=======================
ID | thing | typeID
=======================
1 | car | 1
2 | dog | 2
3 | phone | 1

tblTypes
==============
typeID | type
==============
1 | old
2 | new

What I want to do is to give columns different names in my SELECT statement depending on the type they are.

So rather than SELECT * FROM tblThings where all things old and new will be returned under the header "thing" I'd like to be able to do something like...

SELECT thing as oldthing or newthing
FROM tblThings a, tblTypes b
WHERE a.typeID = b.typeID
AND b.type = old or new

3 rows returned...
===================
oldthing | newthing
===================
car | NULL
phone | NULL
NULL | dog


Is there a better way to tackle this in one SQL statement?

Thanks,
Laeg

 

[PHV]

SELECT thing as oldthing, '' as newthing
FROM tblThings a, tblTypes b
WHERE a.typeID = b.typeID
AND b.type = 'old'
UNION
SELECT '', thing
FROM tblThings a, tblTypes b
WHERE a.typeID = b.typeID
AND b.type = 'new'

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[r937]

select case when b.type='old' 
            then a.thing 
            else null end as oldthing 
     , case when b.type='new' 
            then a.thing 
            else null end as newthing
  from tblThings a
inner
  join tblTypes b
    on a.typeID = b.typeID

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)