CODEWITHSUNDEEP
mysqlsql
Gambles
Gambles

Reputation: 157

SQL - How to join foreign keyed data, with the column names of the parent table

I have two tables:

RUNS

ID  numb   bay0  bay1  bay2
-----------------------------
1    55     aa    bb    cc
2    66     gg   NULL   dd
3    77     dd    bb   NULL

DATA
ID  run_id  serial  data
------------------------------
1     2       gg     xx
2     2       dd     xx

DATA.run_id is a foreign key pointing to RUNS.ID.

'serial' and the value of 'bay%' are foreign keys pointing to another table 'products'.

What I would like is an output like this:

OUTPUT
ID  run_id  serial  data  bay
------------------------------
1     2       gg     xx   bay0
2     2       dd     xx   bay2

Such that the column headers of the first table are put into the output rows of the second tables data.

I have been playing around with alot of queries to get this to work like getting the column headers:

SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME =  'a'
AND TABLE_SCHEMA = 'b'
AND COLUMN_NAME LIKE 'bay%'

But ultimately I can't see how I am going to get/merge/concatenate this output. Can someone help me?

Thanks

Upvotes: 4

Views: 93

Answers (2)

Andrew
Andrew

Reputation: 1866

If number of columns is fixed you can do this:

SELECT
  data.id, data.run_id, data.serial, data.data,
  CASE data.serial
      WHEN runs.bay0 THEN 'bay0'
      WHEN runs.bay1 THEN 'bay1'
      WHEN runs.bay2 THEN 'bay2'
      ELSE NULL
  END AS bay
FROM
  data
  JOIN runs ON (data.run_id = runs.id)

Just list all possible variants in CASE statement. Not elegant, but will work until you add/remove bayX column to data table.

Upvotes: 1

mohan111
mohan111

Reputation: 8865

DECLARE @Table1 TABLE 
    ( ID  int,  run_id  int,  serial  varchar(2),  data  varchar(2))
;

INSERT INTO @Table1
    ( ID ,  run_id ,  serial ,  data )
VALUES
    (1, 2, 'gg', 'xx'),
    (2, 2, 'dd', 'xx')
;


DECLARE @Table2 TABLE 
    ( ID  int,  serial  int,  bay0  varchar(2),  bay1  varchar(2), bay2  varchar(2))
;

INSERT INTO @Table2
    ( ID ,  serial ,  bay0 ,  bay1 ,bay2)
VALUES
    (1, 55, 'aa', 'bb','cc'),
    (2, 66, 'gg', NULL,'dd')
;


SELECT T.ID,T.run_id,T.serial,T.DATA,tt.COL FROM @Table1 t
INNER JOIN 
(
SELECT COL,VAL FROM @Table2
cross apply (values('bay0',bay0),('bay1',bay1),('bay2',bay2))cs(COL,VAL)
WHERE col <> 'BAY1')tt
on T.serial = TT.VAL

Upvotes: 1

Related Questions