|
|||||||||
|
|||||||||
| |||||||||
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
|
|
#1
July 2nd, 2004, 09:34 AM
|
|||
|
|||
|
selecting columns into other conditionally
I have a table that contains people with addresses, we will call it 'person'. Some of these people also have mailing addresses. Those who do have mailing addresses are marked with a flag field. The mailing addresses are found in a seperate table, that I will call 'mailadd'.
I am trying to select a group of people using a select statement and joining the 'mailadd' table address when somebody from the 'person' table is marked with the mailing address flag. I can get the result set fine, but what I want to do is sort the results by address, using the mailadd address for a person if it exists and otherwise using the address found in the 'person' table. I really want to treat the result set as if there was one address for each record and sort by it. Is there a way to select the mailing address into the regular (without altering the phyisical tables), or to do a conditional sort?
|
|
#2
July 2nd, 2004, 10:35 AM
|
|||
|
|||
|
I would use a stored procedure to select the mailing addresses and person names (and other non-address data) into a temp table or table variable. Then I would insert into that table/variable all the addresses and other data for people without mailing addresses. Finally, you can select from the table/variable with whatever sort order you need.
Lucas Alexander http://www.alexanderdevelopment.net
|
|
#3
July 2nd, 2004, 10:57 AM
|
|||
|
|||
|
Code:
select person.name, person.adress,mailadd.adress from person left join mailadd on person.primarykeyColumn = mailadd.foreignkeyColumn order by coalesce(mailadd.adress,person.adress) Change names as apropriate.
|
|
| Viewing: Dev Shed Forums > Databases > MS SQL Development > selecting columns into other conditionally |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
