Table join to multiple columns?

kcarbone1970 · October 25, 2016, 5:56pm

Hello i have this table that has stored ID int data in many fields. Each of the int values can be referenced from another table that shows more info. Table A ID is a record ID btw. I want to join each of the "u" fields from Table A to the id of Table B. There are a lot fields more than in the description. I know i could do multiple joins to each field but i am wondering if there is a more efficient/elegant way of accomplishing this.

Excuse my crude tables
TABLE A
| ID | u1 | u2| u3 | u4 | u5 |

|100| 4 | 5 | 3 | 2 | 1 |

TABLE B
| id | Name | color |

| 1 | Apple | red |
| 2 | Pear | green|
| 3 | banana| yellow |

ScottPletcher · October 25, 2016, 6:05pm

There is a way that is likely to be more efficient ... whether you consider it elegant or not is up to you:

SELECT b.ID, b....,  oa1.*
FROM TABLEA b
OUTER APPLY (
    SELECT
        MAX(CASE WHEN a.u1 = derived.id THEN derived.name END) AS u1_name,
        MAX(CASE WHEN a.u1 = derived.id THEN derived.color END) AS u1_color,
        MAX(CASE WHEN a.u2 = derived.id THEN derived.name END) AS u2_name,
        MAX(CASE WHEN a.u2 = derived.id THEN derived.color END) AS u2_color,
        --...
    FROM (
        SELECT b.id, b.name, b.color
        FROM TABLEB b
        WHERE b.id IN (a.u1, a.u2, a.u3, a.u4, a.u5) --, ...
    ) AS derived
) AS oa1

kcarbone1970 · October 25, 2016, 6:37pm

Thank you Scott your suggestions have always served me well!

kcarbone1970 · October 25, 2016, 7:16pm

Hi Scott cant seem to find where you are joining table "a"?

kcarbone1970 · October 25, 2016, 7:33pm

Im getting this error "Multiple columns are specified in an aggregated expression containing an outer reference. If an expression being aggregated contains an outer reference, then that outer reference must be the only column referenced in the expression."

SELECT
u1a_name,
u1b_name,
u1c_name,
u1d_name,
u1e_name,
u2a_name,
u2b_name,
u2c_name,
u2d_name,
u2e_name,
u3a_name,
u3b_name,
u3c_name,
u3d_name,
u3e_name,
u4a_name,
u4b_name,
u4c_name,
u4d_name,
u4e_name,
u5a_name,
u5b_name,
u5c_name,
u5d_name,
u5e_name,
u6a_name,
u6b_name,
u6c_name,
u6d_name,
u6e_name,
u1a_color,
u1b_color,
u1c_color,
u1d_color,
u1e_color,
u2a_color,
u2b_color,
u2c_color,
u2d_color,
u2e_color,
u3a_color,
u3b_color,
u3c_color,
u3d_color,
u3e_color,
u4a_color,
u4b_color,
u4c_color,
u4d_color,
u4e_color,
u5a_color,
u5b_color,
u5c_color,
u5d_color,
u5e_color,
u6a_color,
u6b_color,
u6c_color,
u6d_color,
u6e_color,
oa1.*
FROM eMat e
OUTER APPLY
(
SELECT
MAX(CASE WHEN e.u1a = derived.id THEN derived.[Standard] END) AS u1a_name,
MAX(CASE WHEN e.u1b= derived.id THEN derived.[Standard] END) AS u1b_name,
MAX(CASE WHEN e.u1c= derived.id THEN derived.[Standard] END) AS u1c_name,
MAX(CASE WHEN e.u1d= derived.id THEN derived.[Standard] END) AS u1d_name,
MAX(CASE WHEN e.u1e= derived.id THEN derived.[Standard] END) AS u1e_name,
MAX(CASE WHEN e.u2a= derived.id THEN derived.[Standard] END) AS u2a_name,
MAX(CASE WHEN e.u2b= derived.id THEN derived.[Standard] END) AS u2b_name,
MAX(CASE WHEN e.u2c= derived.id THEN derived.[Standard] END) AS u2c_name,
MAX(CASE WHEN e.u2d= derived.id THEN derived.[Standard] END) AS u2d_name,
MAX(CASE WHEN e.u2e= derived.id THEN derived.[Standard] END) AS u2e_name,
MAX(CASE WHEN e.u3a= derived.id THEN derived.[Standard] END) AS u3a_name,
MAX(CASE WHEN e.u3b= derived.id THEN derived.[Standard] END) AS u3b_name,
MAX(CASE WHEN e.u3c= derived.id THEN derived.[Standard] END) AS u3c_name,
MAX(CASE WHEN e.u3d= derived.id THEN derived.[Standard] END) AS u3d_name,
MAX(CASE WHEN e.u3e= derived.id THEN derived.[Standard] END) AS u3e_name,
MAX(CASE WHEN e.u4a= derived.id THEN derived.[Standard] END) AS u4a_name,
MAX(CASE WHEN e.u4b= derived.id THEN derived.[Standard] END) AS u4b_name,
MAX(CASE WHEN e.u4c= derived.id THEN derived.[Standard] END) AS u4c_name,
MAX(CASE WHEN e.u4d= derived.id THEN derived.[Standard] END) AS u4d_name,
MAX(CASE WHEN e.u4e= derived.id THEN derived.[Standard] END) AS u4e_name,
MAX(CASE WHEN e.u5a= derived.id THEN derived.[Standard] END) AS u5a_name,
MAX(CASE WHEN e.u5b= derived.id THEN derived.[Standard] END) AS u5b_name,
MAX(CASE WHEN e.u5c= derived.id THEN derived.[Standard] END) AS u5c_name,
MAX(CASE WHEN e.u5d= derived.id THEN derived.[Standard] END) AS u5d_name,
MAX(CASE WHEN e.u5e= derived.id THEN derived.[Standard] END) AS u5e_name,
MAX(CASE WHEN e.u6a= derived.id THEN derived.[Standard] END) AS u6a_name,
MAX(CASE WHEN e.u6b= derived.id THEN derived.[Standard] END) AS u6b_name,
MAX(CASE WHEN e.u6c= derived.id THEN derived.[Standard] END) AS u6c_name,
MAX(CASE WHEN e.u6d= derived.id THEN derived.[Standard] END) AS u6d_name,
MAX(CASE WHEN e.u6e = derived.id THEN derived.[Standard] END) AS u6e_name,

MAX(CASE WHEN e.u1a = derived.id THEN derived.color END) AS u1a_color,
MAX(CASE WHEN e.u1b= derived.id THEN derived.color END) AS u1b_color,
MAX(CASE WHEN e.u1c= derived.id THEN derived.color END) AS u1c_color,
MAX(CASE WHEN e.u1d= derived.id THEN derived.color END) AS u1d_color,
MAX(CASE WHEN e.u1e= derived.id THEN derived.color END) AS u1e_color,
MAX(CASE WHEN e.u2a= derived.id THEN derived.color END) AS u2a_color,
MAX(CASE WHEN e.u2b= derived.id THEN derived.color END) AS u2b_color,
MAX(CASE WHEN e.u2c= derived.id THEN derived.color END) AS u2c_color,
MAX(CASE WHEN e.u2d= derived.id THEN derived.color END) AS u2d_color,
MAX(CASE WHEN e.u2e= derived.id THEN derived.color END) AS u2e_color,
MAX(CASE WHEN e.u3a= derived.id THEN derived.color END) AS u3a_color,
MAX(CASE WHEN e.u3b= derived.id THEN derived.color END) AS u3b_color,
MAX(CASE WHEN e.u3c= derived.id THEN derived.color END) AS u3c_color,
MAX(CASE WHEN e.u3d= derived.id THEN derived.color END) AS u3d_color,
MAX(CASE WHEN e.u3e= derived.id THEN derived.color END) AS u3e_color,
MAX(CASE WHEN e.u4a= derived.id THEN derived.color END) AS u4a_color,
MAX(CASE WHEN e.u4b= derived.id THEN derived.color END) AS u4b_color,
MAX(CASE WHEN e.u4c= derived.id THEN derived.color END) AS u4c_color,
MAX(CASE WHEN e.u4d= derived.id THEN derived.color END) AS u4d_color,
MAX(CASE WHEN e.u4e= derived.id THEN derived.color END) AS u4e_color,
MAX(CASE WHEN e.u5a= derived.id THEN derived.color END) AS u5a_color,
MAX(CASE WHEN e.u5b= derived.id THEN derived.color END) AS u5b_color,
MAX(CASE WHEN e.u5c= derived.id THEN derived.color END) AS u5c_color,
MAX(CASE WHEN e.u5d= derived.id THEN derived.color END) AS u5d_color,
MAX(CASE WHEN e.u5e= derived.id THEN derived.color END) AS u5e_color,
MAX(CASE WHEN e.u6a= derived.id THEN derived.color END) AS u6a_color,
MAX(CASE WHEN e.u6b= derived.id THEN derived.color END) AS u6b_color,
MAX(CASE WHEN e.u6c= derived.id THEN derived.color END) AS u6c_color,
MAX(CASE WHEN e.u6d= derived.id THEN derived.color END) AS u6d_color,
MAX(CASE WHEN e.u6e = derived.id THEN derived.color END) AS u6e_color

FROM
(

SELECT S.ID
	,s.[Standard]
	,s.color
FROM standards s
WHERE s.ID IN (
e.u1a, e.u1b, e.u1c, e.u1d, e.u1e, e.u2a, e.u2b, e.u2c, e.u2d, 
e.u2e, e.u3a, e.u3b, e.u3c, e.u3d, e.u3e, e.u4a, e.u4b, e.u4c, e.u4d, e.u4e, 
e.u5a, e.u5b, e.u5c, e.u5d, e.u5e, e.u6a, e.u6b, e.u6c, e.u6d, e.u6e
) 
) as derived

) as oa1

ScottPletcher · October 25, 2016, 8:13pm

Hmm, off the top of my head the easiest way to fix this is to add (all) the codes to the inner query. I'll keep thinking to see if I can come up with a better way:

...
OUTER APPLY
(
SELECT
MAX(CASE WHEN derived.u1a = derived.id THEN derived.[Standard] END) AS u1a_name,
MAX(CASE WHEN derived.u1b = derived.id THEN derived.[Standard] END) AS u1b_name,
MAX(CASE WHEN derived.u1c = derived.id THEN derived.[Standard] END) AS u1c_name,
...
FROM
(

SELECT S.ID
	,s.[Standard]
	,s.color
        /* add all the lookup codes here*/
        ,e.u1a, e.u1b, e.u1c, e.u1d, e.u1e
        ,e.u2a, e.u2b, e.u2c, e.u2d, e.u2e
        ,e.u3a, e.u3b, e.u3c, e.u3d, e.u3e
        ,e.u4a, e.u4b, e.u4c, e.u4d, e.u4e
        ,e.u5a, e.u5b, e.u5c, e.u5d, e.u5e
        ,e.u6a, e.u6b, e.u6c, e.u6d, e.u6e

FROM standards s
WHERE s.ID IN (
e.u1a, e.u1b, e.u1c, e.u1d, e.u1e, e.u2a, e.u2b, e.u2c, e.u2d, 
e.u2e, e.u3a, e.u3b, e.u3c, e.u3d, e.u3e, e.u4a, e.u4b, e.u4c, e.u4d, e.u4e, 
e.u5a, e.u5b, e.u5c, e.u5d, e.u5e, e.u6a, e.u6b, e.u6c, e.u6d, e.u6e
) 
) as derived
...same as before...

kcarbone1970 · October 25, 2016, 9:51pm

Getting the same error???..

ScottPletcher · October 25, 2016, 9:58pm

I ran that code against sample data and I didn't get that error.

If you want to post sample data in a usable format, I'll try the code again on that.

kcarbone1970 · October 25, 2016, 10:53pm

DECLARE @ID int = 2;

SELECT
oa1.*

FROM eMat e

OUTER APPLY
(

SELECT

MAX(CASE WHEN e.u1a = derived.id THEN derived.[Standard] END) AS u1a_name,
MAX(CASE WHEN e.u1b= derived.id THEN derived.[Standard] END) AS u1b_name,
MAX(CASE WHEN e.u1c= derived.id THEN derived.[Standard] END) AS u1c_name,
MAX(CASE WHEN e.u1d= derived.id THEN derived.[Standard] END) AS u1d_name,
MAX(CASE WHEN e.u1e= derived.id THEN derived.[Standard] END) AS u1e_name,

MAX(CASE WHEN e.u2a= derived.id THEN derived.[Standard] END) AS u2a_name,
MAX(CASE WHEN e.u2b= derived.id THEN derived.[Standard] END) AS u2b_name,
MAX(CASE WHEN e.u2c= derived.id THEN derived.[Standard] END) AS u2c_name,
MAX(CASE WHEN e.u2d= derived.id THEN derived.[Standard] END) AS u2d_name,
MAX(CASE WHEN e.u2e= derived.id THEN derived.[Standard] END) AS u2e_name,

MAX(CASE WHEN e.u3a= derived.id THEN derived.[Standard] END) AS u3a_name,
MAX(CASE WHEN e.u3b= derived.id THEN derived.[Standard] END) AS u3b_name,
MAX(CASE WHEN e.u3c= derived.id THEN derived.[Standard] END) AS u3c_name,
MAX(CASE WHEN e.u3d= derived.id THEN derived.[Standard] END) AS u3d_name,
MAX(CASE WHEN e.u3e= derived.id THEN derived.[Standard] END) AS u3e_name,

MAX(CASE WHEN e.u4a= derived.id THEN derived.[Standard] END) AS u4a_name,
MAX(CASE WHEN e.u4b= derived.id THEN derived.[Standard] END) AS u4b_name,
MAX(CASE WHEN e.u4c= derived.id THEN derived.[Standard] END) AS u4c_name,
MAX(CASE WHEN e.u4d= derived.id THEN derived.[Standard] END) AS u4d_name,
MAX(CASE WHEN e.u4e= derived.id THEN derived.[Standard] END) AS u4e_name,

MAX(CASE WHEN e.u5a= derived.id THEN derived.[Standard] END) AS u5a_name,
MAX(CASE WHEN e.u5b= derived.id THEN derived.[Standard] END) AS u5b_name,
MAX(CASE WHEN e.u5c= derived.id THEN derived.[Standard] END) AS u5c_name,
MAX(CASE WHEN e.u5d= derived.id THEN derived.[Standard] END) AS u5d_name,
MAX(CASE WHEN e.u5e= derived.id THEN derived.[Standard] END) AS u5e_name,

MAX(CASE WHEN e.u6a= derived.id THEN derived.[Standard] END) AS u6a_name,
MAX(CASE WHEN e.u6b= derived.id THEN derived.[Standard] END) AS u6b_name,
MAX(CASE WHEN e.u6c= derived.id THEN derived.[Standard] END) AS u6c_name,
MAX(CASE WHEN e.u6d= derived.id THEN derived.[Standard] END) AS u6d_name,
MAX(CASE WHEN e.u6e = derived.id THEN derived.[Standard] END) AS u6e_name,

MAX(CASE WHEN e.u1a = derived.id THEN derived.color END) AS u1a_color,
MAX(CASE WHEN e.u1b= derived.id THEN derived.color END) AS u1b_color,
MAX(CASE WHEN e.u1c= derived.id THEN derived.color END) AS u1c_color,
MAX(CASE WHEN e.u1d= derived.id THEN derived.color END) AS u1d_color,
MAX(CASE WHEN e.u1e= derived.id THEN derived.color END) AS u1e_color,

MAX(CASE WHEN e.u2a= derived.id THEN derived.color END) AS u2a_color,
MAX(CASE WHEN e.u2b= derived.id THEN derived.color END) AS u2b_color,
MAX(CASE WHEN e.u2c= derived.id THEN derived.color END) AS u2c_color,
MAX(CASE WHEN e.u2d= derived.id THEN derived.color END) AS u2d_color,
MAX(CASE WHEN e.u2e= derived.id THEN derived.color END) AS u2e_color,

MAX(CASE WHEN e.u3a= derived.id THEN derived.color END) AS u3a_color,
MAX(CASE WHEN e.u3b= derived.id THEN derived.color END) AS u3b_color,
MAX(CASE WHEN e.u3c= derived.id THEN derived.color END) AS u3c_color,
MAX(CASE WHEN e.u3d= derived.id THEN derived.color END) AS u3d_color,
MAX(CASE WHEN e.u3e= derived.id THEN derived.color END) AS u3e_color,

MAX(CASE WHEN e.u4a= derived.id THEN derived.color END) AS u4a_color,
MAX(CASE WHEN e.u4b= derived.id THEN derived.color END) AS u4b_color,
MAX(CASE WHEN e.u4c= derived.id THEN derived.color END) AS u4c_color,
MAX(CASE WHEN e.u4d= derived.id THEN derived.color END) AS u4d_color,
MAX(CASE WHEN e.u4e= derived.id THEN derived.color END) AS u4e_color,

MAX(CASE WHEN e.u5a= derived.id THEN derived.color END) AS u5a_color,
MAX(CASE WHEN e.u5b= derived.id THEN derived.color END) AS u5b_color,
MAX(CASE WHEN e.u5c= derived.id THEN derived.color END) AS u5c_color,
MAX(CASE WHEN e.u5d= derived.id THEN derived.color END) AS u5d_color,
MAX(CASE WHEN e.u5e= derived.id THEN derived.color END) AS u5e_color,

MAX(CASE WHEN e.u6a= derived.id THEN derived.color END) AS u6a_color,
MAX(CASE WHEN e.u6b= derived.id THEN derived.color END) AS u6b_color,
MAX(CASE WHEN e.u6c= derived.id THEN derived.color END) AS u6c_color,
MAX(CASE WHEN e.u6d= derived.id THEN derived.color END) AS u6d_color,
MAX(CASE WHEN e.u6e = derived.id THEN derived.color END) AS u6e_color

FROM
(

SELECT

s.ID
,s.[Standard]
,s.color
,e.u1a, e.u1b, e.u1c, e.u1d, e.u1e
,e.u2a, e.u2b, e.u2c, e.u2d, e.u2e
,e.u3a, e.u3b, e.u3c, e.u3d, e.u3e
,e.u4a, e.u4b, e.u4c, e.u4d, e.u4e
,e.u5a, e.u5b, e.u5c, e.u5d, e.u5e
,e.u6a, e.u6b, e.u6c, e.u6d, e.u6e

FROM standards s

WHERE s.ID IN (
e.u1a, e.u1b, e.u1c, e.u1d, e.u1e
,e.u2a, e.u2b, e.u2c, e.u2d, e.u2e
,e.u3a, e.u3b, e.u3c, e.u3d, e.u3e
,e.u4a, e.u4b, e.u4c, e.u4d, e.u4e
,e.u5a, e.u5b, e.u5c, e.u5d, e.u5e
,e.u6a, e.u6b, e.u6c, e.u6d, e.u6e
)

) as derived

) as oa1

WHERE e.ID = @ID

kcarbone1970 · October 26, 2016, 3:23am

this is usable

SELECT oa1.*
FROM TableA a
OUTER APPLY (
SELECT
MAX(CASE WHEN a.u1 = derived.id THEN derived.name END) AS u1_name,
MAX(CASE WHEN a.u1 = derived.id THEN derived.color END) AS u1_color,
MAX(CASE WHEN a.u2 = derived.id THEN derived.name END) AS u2_name,
MAX(CASE WHEN a.u2 = derived.id THEN derived.color END) AS u2_color,
MAX(CASE WHEN a.u3 = derived.id THEN derived.name END) AS u3_name,
MAX(CASE WHEN a.u3 = derived.id THEN derived.color END) AS u3_color,
MAX(CASE WHEN a.u4 = derived.id THEN derived.name END) AS u4_name,
MAX(CASE WHEN a.u4 = derived.id THEN derived.color END) AS u4_color,
MAX(CASE WHEN a.u5 = derived.id THEN derived.name END) AS u5_name,
MAX(CASE WHEN a.u5 = derived.id THEN derived.color END) AS u5_color

FROM (
    SELECT b.id, b.name, b.color

    FROM TableB b
    WHERE b.id IN (a.u1, a.u2, a.u3, a.u4, a.u5) 
) AS derived

) AS oa1

kcarbone1970 · October 26, 2016, 4:28am

So if i remove MAX from the statement it works but it produces multiple records...hence the MAX???

kcarbone1970 · October 26, 2016, 4:39am

So this worked

SELECT
MAX(u1_name),
MAX(u1_color),
MAX(u2_name),
MAX(u2_color),
MAX(u3_name),
MAX(u3_color),
MAX(u4_name),
MAX(u4_color),
MAX(u5_name),
MAX(u5_color)
FROM TableA a
outer APPLY (
SELECT

    CASE WHEN a.u1 = id THEN name END AS u1_name,
    CASE WHEN a.u1 = id THEN color END AS u1_color,
   CASE WHEN a.u2 = id THEN name END AS u2_name,
    CASE WHEN a.u2 = id THEN color END AS u2_color,
    CASE WHEN a.u3 = id THEN name END AS u3_name,
    CASE WHEN a.u3 = id THEN color END AS u3_color,
    CASE WHEN a.u4 = id THEN name END AS u4_name,
    CASE WHEN a.u4 = id THEN color END AS u4_color,
  CASE WHEN a.u5 = id THEN name END AS u5_name,
    CASE WHEN a.u5 = id THEN color END AS u5_color

FROM (
    SELECT b.ID, b.Name, b.Color

    FROM TableB b
    WHERE b.id IN (a.u1, a.u2, a.u3, a.u4, a.u5) 
) AS derived

) AS oa1
where ID = 100

Table join to multiple columns?

Excuse my crude tables TABLE A | ID | u1 | u2| u3 | u4 | u5 |

TABLE B | id | Name | color |

Excuse my crude tables
TABLE A
| ID | u1 | u2| u3 | u4 | u5 |

TABLE B
| id | Name | color |