CODEWITHSUNDEEP
sqljoinaggregate
CP335
CP335

Reputation: 1

TableA (not aggregated) left join on a aggregated TableB

I have two tables

enter image description here and I want to left join Table_A to Table_B (result: all Rows from Table_A and only matching rows from Table_B)

Requirements

I already tried this forum here and found another thread with join an a aggregated table, but this did not work because it aggregated Table_A as well and trying the same without aggregating Table_A did not work.

My try:

Select * From TABLE-A
left join
(select TABLE-B.Key, TABLE-B.Text from TABLE-B
   group by TABLE-B.Key, TABLE-B.Text
 )
 On Left TABLE-A.Key = TABLE-B.Key

Upvotes: 0

Views: 38

Answers (1)

Rob Dogo Spahitz
Rob Dogo Spahitz

Reputation: 32

First, you join tables, but your select is returning field values and trying to join that to a table. You also didn't indicate what database you're using, which may have variations.

Here's what I did in MS-Access:

  1. Create a new View (query) to handle the aggregate; I called it vTableB:

    SELECT [TABLE-B].Key, [TABLE-B].Text1
FROM [TABLE-B]
GROUP BY [TABLE-B].Key, [TABLE-B].Text1;

  2. Perform your desired join against table A and the view:

    SELECT [TABLE-A].*
FROM [TABLE-A] 
LEFT JOIN vTableB ON [TABLE-A].Key = vTableB.Key;

Make changes to use your desired field names (note: your field names I don't recommend because of potential conflicts with other keywords) and see if you get the desired results.

Upvotes: 0

Related Questions