September 29, 2005 at 2:22 pm
I hope I can explain this OK. I can give more details but I'll try to simplify it first.
I've got a query which is returning more rows than I want. It also has a few outer joins in it. To break it down, where I'm getting 2 rows, I really only want one.
Example:
TABLE_A
trailer_id.......qty
AA...............5
BB................4
CC...............6
TABLE_B
trailer_id............pup_trailer_id
AA.........................BB
So, I want my result to be something like
trailer_id..........pup_trailer_id.....trailer_qty....pup_qty
AA..................BB....................5..................4
CC.........................................6..................
September 29, 2005 at 2:37 pm
When you have difficulty with multiple outer joins, you can always embed and do a join with an intermediate result set:
FROM tablea a
JOIN lookuptablea la ON a.key = la.key
LEFT OUTER JOIN (
SELECT ...
FROM tablea b
JOIN lookuptablea lb ON lb.key = b.key
) w on w.key = a.key
I find this the easiest to code, though in some circumstances it may not execute faster than another solution.
September 29, 2005 at 2:40 pm
May not be the best solution.
Create table #TableA (Trailer_ID varchar(2), Qty int)
INSERT INTO #TableA Values ('AA',5)
INSERT INTO #TableA Values ('BB',4)
INSERT INTO #TableA Values ('CC',6)
Create table #TableB (Trailer_ID varchar(2), pup_trailer_id varchar(2))
INSERT INTO #TableB Values ('AA','BB')
select a.Trailer_ID, b.pup_trailer_id, a.qty, c.qty
FROM #TableA a
LEFT OUTER JOIN #TableB b ON a.Trailer_ID = b.Trailer_ID
LEFT OUTER JOIN #TableA c ON b.pup_trailer_id = c.Trailer_ID
WHERE a.Trailer_ID NOT IN (
select b.pup_trailer_id
FROM #TableA a
LEFT OUTER JOIN #TableB b ON a.Trailer_ID = b.Trailer_ID
LEFT OUTER JOIN #TableA c ON b.pup_trailer_id = c.Trailer_ID
WHERE b.pup_trailer_id IS NOT NULL)
DROP TABLE #TableA
DROP TABLE #TableB
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply