SQLTeam.com | Weblogs | Forums

Help with a query of multiple foreign keys


I have a table which contains 5 foreign keys, all referencing the same table.
For example
Select distinct city1_id, city2_id, city3_id, city4_id, city5_id from states.
(I know that doesn't do it, but that shows what I'm trying to do.)
I want a distinct list of all city_id's from all records in the states table.
Thank you!


please provide DDL and DML

insert into #sample
select 'Seattle' union
select 'LA'


Select distinct city_id
from dbo.states
cross apply ( values(city1_id), (city2_id), (city3_id), (city4_id), (city5_id) ) city_ids(city_id)
order by city_id


Create table Person
Person_ID identity(1,1),
constraint pk_Person_ID primary key (Person_ID)

Create table NickNames
Nic_Person_ID1 int,
constraint fk_Nic_Person1_ID foreign key (Nic_Person1_ID) references Person(Person_ID)
Nic_Person_ID2 int,
constraint fk_Nic_Person2_ID foreign key (Nic_Person2_ID) references Person(Person_ID)

I want a list of all Person_ID's being referenced in the nicknames table.


Please provide sample data