Solved

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

Posted on 2004-10-26
4,661 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
Question by:ampapa
    5 Comments
     
    LVL 58

    Expert Comment

    by:amit_g
    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
    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
    Looks like we posted that at the same time.
    0
     
    LVL 32

    Accepted Solution

    by:
    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
    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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Cisco Complete Network Certification Training

    If you’re an IT engineer or technician, it's time you take your career to the next level. This elite training bundle is brimming with all of the information you need to learn to sit for Cisco CNNA, CCNP, and CCENT certification exams.

    Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

    913 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

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now