CODEWITHSUNDEEP
sql-serversql-server-2014
AS91
AS91

Reputation: 527

Tables joined without common fields

I have a SQL Server Database which has 2 tables

Client Table

Name    Employment Race
Ronald  867         452
Arnold  845         123

Lookup Table

lookup_id    Detail
867         Unemployed
845         Part time employed
452         White
123         Black

The numeric entries point to the primary key of LookUp table called lookup_id. Corresponding to each lookup_id (867,856 etc.) is the actual Employment or Race description in text - Unemployed, White etc. More Client fields follow the same referencing.

However, the lookup_id field by itself does not exist in the Client table. It seems to me that the two tables are linked without a PK,FK reference. Is this possible? Passing a key entry directly into a field to join tables? How do I query these tables together?

Upvotes: 0

Views: 49

Answers (1)

DhruvJoshi
DhruvJoshi

Reputation: 17126

You can do that via a query like this.

new query:

SELECT Name, L1.Detail AS Employment, L2.Detail as Race
FROM Client C LEFT OUTER JOIN Lookup L1
    ON C.Employment=L1.lookup_id
LEFT OUTER JOIN Lookup L2
    ON C.Race=L2.lookup_id

old query:

SELECT Name, Detail 
FROM Client C LEFT OUTER JOIN Lookup L
 ON C.Employment=L.lookup_id

A FK need not have same name as PK in the FK table, like here Employment and Race columns are foreign key value for Lookup.

Also you should but not necessarily have a FK PK relation defined between tables. This is a common practice in Data warehousing.

Upvotes: 1

Related Questions