CODEWITHSUNDEEP
sqlsql-server-2008
Nathan Kummel
Nathan Kummel

Reputation: 98

SQL Server : selecting data based on ids pointing to a single table

I am trying to create a view based on two tables.

Member:

ID  Lang1_ID  Lang2_ID - all tinyint
1   2         3
2   2         4
3   1         4

Language:

ID  Title - tinyint, varchar(50)
1   English
2   French
3   Spanish
4   Portuguese

I wish to create a view with all the titles containing:

member_id, Lang1_Title, Lang2_Title

Needed result:

1  French  Spanish
2  French  Portuguese
3  English Portuguese

Upvotes: 0

Views: 50

Answers (1)

peterm
peterm

Reputation: 92785

SELECT m.id member_id, l.Title Lang1_Title, l2.Title Lang2_Title
  FROM Member m LEFT JOIN 
       Language l ON m.Lang1_ID = l.ID LEFT JOIN
       Language l2 ON m.Lang2_ID = l2.ID

Output

| MEMBER_ID | LANG1_TITLE | LANG2_TITLE |
-----------------------------------------
|         1 |      French |     Spanish |
|         2 |      French |  Portuguese |
|         3 |     English |  Portuguese |

sqlfiddle example

And the view

CREATE VIEW viewname
AS
SELECT m.id member_id, l.Title Lang1_Title, l2.Title Lang2_Title
  FROM Member m LEFT JOIN 
       Language l ON m.Lang1_ID = l.ID LEFT JOIN
       Language l2 ON m.Lang2_ID = l2.ID

And use it

SELECT * FROM viewname

sqlfiddle example

Upvotes: 1

Related Questions