Using MAX in a subquery 1

[hanchilicious]

Hi,

Can you help me figure this out?

I have a table which looks a bit like this:

TABLE_A

Col1   Col2  Col3		   Col4	   Col5            Col6	  Col7	Col8
----------------------------------------------------------------------------
800	UK	ACCESSORIES	PPM9572	Not assigned	200225	IN02	50
800	UK	ACCESSORIES	PPM9572	Not assigned	200430	IN02	40
800	UK	ACCESSORIES	PPM9572	Not assigned	903183	IN02	20
800	UK	ACCESSORIES	PPM9572	Not assigned	903572	IN02	50

And another table which looks like this:
TABLE_B

Col1   Col2  Col3       Col4
----------------------------
800	UK	PPM9572	400

Here's the issue:
I need to use a full join to join these two tables together, as there are exclusive rows in each. The join is based on the columns with values 800 and PPM%.

If I do a straight full join based on these two columns, I'm going to get four instances of the TABLE_B join, when there is in fact only one.

TABLE_A.Col6 is stopping me from being able to 1-to-1 join at 800 and PPM level. How do I go about just joining to one row? (Which one of the four isn't important).

I'm currently doing:

FULL OUTER JOIN TABLE_B
ON TABLE_A.Col1 = TABLE_B.Col1
AND TABLE_A.Col4 = TABLE_B.Col4
AND TABLE_A.Col6 = 
(select max(Col6) from TABLE_A
group by Col1,Col4,Col8)

But even though these are distinct dimensions, the subquery returns more than one value due to the measure difference. And I can't group by measures...

 

[hanchilicious]

The last line of that code should read:
"group by Col1,Col4,Col7)

 

[bborissov]

Can you post what you want from that data you post here?

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[hanchilicious]

I want to take TABLE_B.Col4 and join it onto TABLE_A, but without duplicating the value.

So, a subset of the join result using the examples above would look like:

...
Col7   Col8   Col9
------------------
IN02   50     400
IN02   40     NULL
IN02   20     NULL
IN02   50     NULL

Thanks

 

[bborissov]

Try:

DECLARE @TableA TABLE (Col1 int, Col2 char(2), Col3 varchar(50), Col4 char(7), col5 varchar(50), col6 int, col7 char(4), col8 int)
DECLARE @TableB TABLE (Col1 int, Col2 char(2), Col3 varchar(50), Col4 int)


INSERT INTO @TableB VALUES(800,'UK','PPM9572',400)

INSERT INTO @TableA VALUES(800,'UK','ACCESSORIES','PPM9572','Not assigned',200225,'IN02',50)
INSERT INTO @TableA VALUES(800,'UK','ACCESSORIES','PPM9572','Not assigned',200430,'IN02',40)
INSERT INTO @TableA VALUES(800,'UK','ACCESSORIES','PPM9572','Not assigned',903183,'IN02',20)
INSERT INTO @TableA VALUES(800,'UK','ACCESSORIES','PPM9572','Not assigned',903572,'IN02',50)

SELECT TableA.*,
       TableB.Col9
FROM @TableA TableA
LEFT JOIN (SELECT TblB.Col1,
                  TblB.Col3,
                  TblB.Col4 AS Col9,
                  MAX(TblA.col6) AS col6
            FROM @TableB TblB
            INNER JOIN @TableA TblA ON TblB.Col1 = TblA.Col1 AND
                                       TblB.Col3 = TblA.Col4
            GROUP BY TblB.Col1,
                     TblB.Col3,
                     TblB.Col4) TableB
ON TableA.Col1 = TableB.Col1 AND
   TableA.Col4 = TableB.Col3 AND
   TableA.Col6 = TableB.Col6

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[hanchilicious]

Thanks bborissov.

After tweaking to my needs, I get a slight discrepancy with the joined results, but only of about 1%. So it definitely looks like this is the way forward.

Cheers