SQLTeam.com | Weblogs | Forums

Table join to multiple columns?

tsql

#1

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 |


#2

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

#3

Thank you Scott your suggestions have always served me well! :slight_smile:


#4

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


#5

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

#6

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...

#7

Getting the same error???.. :frowning:


#8

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.


#9

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


#10

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


#11

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


#12

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