CODEWITHSUNDEEP
sqlsql-server
Sprout
Sprout

Reputation: 650

Joining two tables, with a primary key to many foreign keys

I have two tables in SQL and I want to make a query where I can get all the columns from the first table and the associated colour (based on the id) from the second table.

The result should concatenate the colours from the second table so that each row has a unique primary key (I know you can't have multiple primary key values)

enter image description here

I have tried an inner join but this results in duplicate primary keys. (See below)

I have also tried a union but I receive an error saying there needs to be an equal amount of columns for a union.

SELECT [Table1].[ID], [Table1].[Name], [Table1].[Job], [Table2].[Colour]
FROM 
[Table1]
INNER JOIN
[Table2] ON [Table1].[Id]  = [Table2].[Id]

Upvotes: 0

Views: 1655

Answers (2)

Mohamed Azizi
Mohamed Azizi

Reputation: 162

Try this one : 

;with T1 as (
SELECT Id,  
Colour=STUFF  
(  
     (  
       SELECT ', ' + CAST(Colour AS VARCHAR(MAX))  
       FROM Table2 t2   
       WHERE t2.Id = t1.Id
       FOR XML PATH('')  
     ),1,1,''  
)  
FROM Table1 t1  
GROUP BY Id
)

Select A.Id,Name,Job,Colour 
from Table1 A,T1 B
where A.Id = B.Id

Here is the result I got running this query :

Id  Name    Job      Colour
---------------------------------------
1   Jane    Worker   Black, Yellow, Red
2   John    Worker   Purple, Blue, White
3   Kim     Worker   Grey, Green
4   Corit   Worker   Brown, Pink
5   Tata    Worker   Orange, Black

Upvotes: 0

Ilyes
Ilyes

Reputation: 14928

This should do the job:

CREATE TABLE T1 (
    ID INT,
    Name VARCHAR (25),
    Job VARCHAR (25)
    );
CREATE TABLE T2 (
    ID INT,
    Color VARCHAR (25)
    );

INSERT INTO T1 VALUES
(1, 'John', 'Worker'),
(2, 'Jane', 'Worker');

INSERT INTO T2 VALUES
(1, 'Blue'),
(1, 'Yellow'),
(1, 'Green'),
(2, 'Orange');

SELECT  *,
        ( 
            SELECT  T2.color + ' '
            FROM    T2  INNER JOIN 
                    T1 ON T2.ID = T1.ID
            WHERE TT.ID = T2.ID
            FOR XML PATH('')
        ) AS Colors
FROM    T1 TT

Results:

+----+------+--------+--------------------+
| ID | Name |  Job   |       Colors       |
+----+------+--------+--------------------+
|  1 | John | Worker | Blue Yellow Green  |
|  2 | Jane | Worker | Orange             |
+----+------+--------+--------------------+

Demo

Upvotes: 1

Related Questions