ampapa
asked on
Select DISTINCT items from multiple columns based upon match in another column
I hope the heading isn't too confusing.
I have a table that consists of 11 fields Field1, Field2, Field3, etc. I would like to have a query that would get the unique items/names from the table based upon a given criteria.
The data in the table consists of an organizational structure, so if a user were to select a supervisor in Field1 his/her subordinates would be listed in Fields2 - Fields11. A supervisor can be on multiple rows and have multiple subordinates.
Field1 Field2 Field3 Field4 Field5
James Freda Nancy
Kirk Jim Lonie Gretta Marsha
Shelly Bob Mark
James Bill Kelly Todd Pete
So, if a user wanted all the subordinates for James they would get a list of Freda, Nancy, Bill, Kelly, Todd, Pete
I hope this makes sense?
This is what I tried but I'm not good at all on SQL statements.
SELECT DISTINCT Field1 from OrgStructure where 'James' IN (Field1) AND (Field2 IS NOTNULL) OR (Field3 IS NOTNULL) OR (Field4 IS NOTNULL) OR (Field5 IS NOTNULL) Order by Field1
I have a table that consists of 11 fields Field1, Field2, Field3, etc. I would like to have a query that would get the unique items/names from the table based upon a given criteria.
The data in the table consists of an organizational structure, so if a user were to select a supervisor in Field1 his/her subordinates would be listed in Fields2 - Fields11. A supervisor can be on multiple rows and have multiple subordinates.
Field1 Field2 Field3 Field4 Field5
James Freda Nancy
Kirk Jim Lonie Gretta Marsha
Shelly Bob Mark
James Bill Kelly Todd Pete
So, if a user wanted all the subordinates for James they would get a list of Freda, Nancy, Bill, Kelly, Todd, Pete
I hope this makes sense?
This is what I tried but I'm not good at all on SQL statements.
SELECT DISTINCT Field1 from OrgStructure where 'James' IN (Field1) AND (Field2 IS NOTNULL) OR (Field3 IS NOTNULL) OR (Field4 IS NOTNULL) OR (Field5 IS NOTNULL) Order by Field1
select distinct field2 from table where field1='James'
union all
select distinct field3 from table where field1='James'
union all
select distinct field4 from table where field1='James'
union all
select distinct field5 from table where field1='James'
union all
select distinct field3 from table where field1='James'
union all
select distinct field4 from table where field1='James'
union all
select distinct field5 from table where field1='James'
Looks like we posted that at the same time.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank's for the extremely fast post as well as at the same time! All 3 answers work except for bhess1 also included the "IS NULL" criteria from my original attempt so I've got to go with his answer for completeness.
Thank's again.
Thank's again.
Select distinct Field2 from OrgStructure where Field1 = 'James'
Union
Select distinct Field3 from OrgStructure where Field1 = 'James'
Union
Select distinct Field4 from OrgStructure where Field1 = 'James'
Union
Select distinct Field5 from OrgStructure where Field1 = 'James'