[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Select DISTINCT items from multiple columns based upon match in another column

Posted on 2004-10-26
5
Medium Priority
?
4,662 Views
Last Modified: 2012-05-05
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
0
Comment
Question by:ampapa
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 58

Expert Comment

by:amit_g
ID: 12417097
Try this ...

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'
0
 
LVL 6

Expert Comment

by:curtis591
ID: 12417100
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'
0
 
LVL 6

Expert Comment

by:curtis591
ID: 12417102
Looks like we posted that at the same time.
0
 
LVL 32

Accepted Solution

by:
Brendt Hess earned 2000 total points
ID: 12417122
SELECT Distinct Field2
FROM OrgStructure
WHERE Field1 = 'James'
    AND Field2 Is Not Null

UNION ALL

SELECT Distinct Field3
FROM OrgStructure
WHERE Field1 = 'James'
    AND Field3 Is Not Null

UNION ALL

SELECT Distinct Field4
FROM OrgStructure
WHERE Field1 = 'James'
    AND Field4 Is Not Null

UNION ALL

SELECT Distinct Field5
FROM OrgStructure
WHERE Field1 = 'James'
    AND Field5 Is Not Null
0
 
LVL 8

Author Comment

by:ampapa
ID: 12417261
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.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question