Combine a 'Distinct' SQL Query with a single value query

I have an existing sql query that I'd like to apply to every record returned from a "distinct" query.

I guess something like looping through each of the returned records, storing it as a string, and开发者_开发百科 using that value in the other query. How would I go about this?

sudo queries:

Select ...

for each record returned as X,

Select ... etc ... where ... LIKE X

Edit: not sure how to make it clearer, but I know I'm probably not making it obvious. I'll try:

The distinct will return a single column, with many records. I need to apply each value to the second sql query.

So like.. Select X and Y, but Y is returned from the 2nd query I have, using X

Edit2: If the distinct select returns

1
2
3
4

And the second query returns a single record "A" when the where clause looks like ... = '1', "B" when the where clause looks like ... = '2', "C" when the where clause looks like ... = '3', and C when the where clause looks like ... = '4'

Then I'd like my final output to look like

1 | A
2 | B
3 | C
4 | C

Edit 3: first query:

SELECT DISTINCT [user_id] from dbo.sap_empl_subset

second query:

SELECT [name_pref_mixed]
FROM dbo.sap_empl_subset AS E
WHERE E.sap_position_no IN 
        (SELECT P.sap_position_no 
         FROM dbo.sap_position AS P 
         WHERE (LTRIM(RTRIM(P.sap_position_desc)) LIKE '%[VICE ]PRESIDENT%') 
              OR (LTRIM(RTRIM(P.sap_position_desc)) LIKE 'CHIEF%'))
  AND E.sap_org_code = 
        (SELECT 
              CASE
                    WHEN S.sap_org_code_level2 = 0 THEN S.sap_org_code 
                    WHEN S.sap_org_code_level3 = 0 THEN S.sap_org_code_level1
                    ELSE S.sap_org_code_level2
              END
         FROM dbo.sap_org_structure AS S 
         WHERE S.sap_org_code = 
              (SELECT E1.sap_org_code 
               FROM dbo.sap_empl_subset AS E1 
               WHERE E1.[user_id] = '<each item from first query needs applied here>'))

SELECT  *
FROM    (
        SELECT  DISTINCT value
        FROM    mytable
        ) x
JOIN    othertable  y
ON      y.value LIKE '%' || x.value || '%'

Update:

If you first query is

SELECT  my_x
FROM    mytable
WHERE   my_y = '…'

and the second one is

SELECT  other_z
FROM    othertable
WHERE   other_y = my_x

the you just need a join:

SELECT  my_x, other_z
FROM    mytable
JOIN    othertable
ON      other_y = my_x
WHERE   my_y = '…'

It would be much more easy to answer if you just posted the queries.

Update 2:

Try this:

SELECT  es.user_id, esp.name_pref_mixed
FROM    sap_empl_subset es
JOIN    sap_org_structure os
ON      os.sap_org_code = es.sap_org_code
JOIN    sap_empl_subset esс
ON      esc.sap_org_code =
        CASE
                WHEN os.sap_org_code_level2 = 0 THEN os.sap_org_code 
                WHEN os.sap_org_code_level3 = 0 THEN os.sap_org_code_level1
                ELSE os.sap_org_code_level2
        END
WHERE   esc.sap_position_no IN
        (
        SELECT  sap_position_no
        FROM    sap_position sp
        WHERE   (LTRIM(RTRIM(sp.sap_position_desc)) LIKE '%[VICE ]PRESIDENT%') 
                OR (LTRIM(RTRIM(sp.sap_position_desc)) LIKE 'CHIEF%'))
        )

DISTINCT seems to be redundant here. You have a condition in your second query:

WHERE S.sap_org_code = 
      (
      SELECT  E1.sap_org_code 
      FROM    dbo.sap_empl_subset AS E1 
      WHERE   E1.[user_id] = '<each item from first query needs applied here>')
      )

which would throw an error if there were duplicates on sap_empl_subset.user_id

A join was not necessary to combine the two queries. All I needed was the nested select syntax as shown below, where the first line is the first query, and the first nested select is the second query. A join was not necessary.

SELECT Distinct U.[user_id] AS "User ID", (
SELECT [empl_last_name]
FROM dbo.sap_empl_subset AS E
WHERE E.sap_position_no IN 
        (SELECT P.sap_position_no 
         FROM dbo.sap_position AS P 
         WHERE (LTRIM(RTRIM(P.sap_position_desc)) LIKE '%[VICE ]PRESIDENT%') 
              OR (LTRIM(RTRIM(P.sap_position_desc)) LIKE '%CHIEF%')
              OR (LTRIM(RTRIM(P.sap_position_desc)) LIKE '%[EXECUTIVE ]VP%')
              )
  AND E.sap_org_code = 
        (SELECT 
              CASE
                    WHEN S.sap_org_code_level2 = 0 THEN S.sap_org_code 
                    WHEN S.sap_org_code_level3 = 0 THEN S.sap_org_code_level1
                    ELSE S.sap_org_code_level2
              END
         FROM dbo.sap_org_structure AS S 
         WHERE S.sap_org_code = 
              (SELECT E1.sap_org_code 
               FROM dbo.user_id AS E1 
               WHERE E1.[user_id] = U.[user_id]))) As "VP"
               From dbo.user_id As U WHERE U.[user_id] <> ''
               ORDER BY [User ID]