July 18, 2012 at 11:46 pm
Hey Everyone,
I am experiencing a complex situation where i am writing query to move huge(1 million rows)data from one table and joining with other tables into single table.
While using joins i am joining almost 10 to 12 tables and also join with AND to other tables.
Ex:
select A.ex,B.ex,C.ex....from base table C join
D on D.c=C.c
join E on E.d=C.d
join A on A.f=C.f
AND Z.g=C.g
AND Y.h=C.h
join
similar to above join......
Its a generic query.It didnt come well.Hope you can take it in general scenario.;-)
NOW THE PROBLEM HERE IS WE ARE GETTING ALMOST LESS NO OF ROWS THAN EXISTING.
i TRIED TO USE LEFT JOIN FOR LAST JOINS BUT WE ARE GETTING "NULL" VALUES FOR NOT NULL FIELDS.
Any process for debugging joins or any other alternative to get all desired corrected rows.
Thanks in Advance.:-)
-Hemanth.
July 19, 2012 at 6:35 am
For debugging purpose comment all the joins except first two joins and see the result, is it returning all the rows, if yes then uncomment 3rd join also and see the result and the same process need to be followed until you get lesser number of rows. And the join at which you get lesser number of rows that join has a problem.
And apply left join at other table also instead of applying on last table only.
Hope this helps you...
July 19, 2012 at 8:10 am
base table C has (let's say) 100 rows.
If any of the identifying fields in base table c do not match with the table it is joined to, you will get less rows. If any of the other joined tables do not have matching rows in their tables it will decrease their rows, thereby decreasing the rows in total returned. LEFT JOIN will return nulls for fields where there is missing data.
To investigate, use your left joins and find out why there is not data where there should be in said missing table.
Sample:
CREATE TABLE #base (id int, baseText VARCHAR(10))
INSERT INTO #base
SELECT 1, 'testing1'
UNION ALL
SELECT 2, 'testing2'
CREATE TABLE #leftJoin (newId int, baseId int, joinText VARCHAR(10))
INSERT INTO #leftjoin
SELECT 100, 1, 'goodToGo'
UNION ALL
SELECT 200, 1, 'yetAgain'
SELECT b.id, b.baseText, c.newId, c.joinText
FROM #base b
LEFT JOIN #leftJoin c
ON b.id = c.baseId
WHERE c.baseId IS NULL
This will show you which id exists in your base table, but not in the join table. Work with this to solve your issues.
Jared
CE - Microsoft
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply