CODEWITHSUNDEEP
sqlsql-serverjoin
user2784067
user2784067

Reputation: 69

Extracting different data from the same table as different fields with additional tables as lookups

I have two tables. One gives me basic information about demographics. One of the categories in my demographics table is a subset of people, which is housed in ATID 530 (there are several hundred different ATIDs) of this table:

enter image description here

As you can see the PK of this table is ADefID. My other table uses this as a FK. It houses indexes to additional definitions for records in the original table. However those additional definitions are also just records in the original table. The second table just provides pointers.

enter image description here

So if we pick a record, let's say ADefID=4684423, and look it up in the second table, we are returned this:

enter image description here

The CategoryADefID will then point back to the original table's ADefID for another record:

enter image description here

(note the ATID of this ADefID differs from the original ADefID that this is related to)

So. Let's say I want to pull out a set of records from the first row, say

WHERE ATID = 530 AND CycleID = 9600

But I also want to pull the ADesc (and maybe ADEValue) from the related definition as a separate field.

So the end result would look sort of like this:

enter image description here

I understand enough to make the join to the second table and return the CategoryADefID, but I dont know how to use that to call back to another ADefID in the original table. The other limitation is that I would use the ATID field in the WHERE clause (ATID=530) and the related definition will have a different ATID.

Upvotes: 0

Views: 49

Answers (1)

Charles Bretana
Charles Bretana

Reputation: 146499

Just add another join back to the original table:

 Select *
 From tableA a
   join tableB b on b.ADefID=a.ADefID
   join tableA a2 on a2.ADefID = b.CategoryADefID  
 Where a.ADefID = 4684423

Upvotes: 1

Related Questions