Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rico1931
Starting Member
37 Posts |
 Posted - 2011-03-08 : 13:10:56
|
| Hi Everyone,I'm having a bit of a problem. I have two Tables and I want to join them using a primary key but for some reason my joins( and yes i've tried unions) keep populating repeating records. Here is what my problem is:Table 1(T1) has 3 fields: T1.ITEM_Num, T1.Loc, and T1.QTY (Contains 14K records)Table 2(T2) has 3 fields: T2.ITEM_Num, T2.Loc and T2.QTY (Contains 19k records)What I'm trying to do is the following SELECT T1.ITEM_Num, T1.Loc, and T1.QTYFROM T1LEFT JOIN T2 ON T1.ITEM_NUM = T2.ITEM_NumWHERE T1.QTY <> T2.QTYGOAL: Display Items from T1 that QTY DO NOT EQUAL T2 QTYCan someone point me in the right direction of how to solve this problem? Thanks again for you help.. I really thought this would be super simple but I keep getting back an error or it keeps looping and never ending! |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-03-08 : 14:41:39
|
If ITEM_Num is really the primary key on both tables, you should not get any duplicate records. Otherwise, there may be multiple records in T2 with the same ITEM_Num, which can cause the problem you are seeing.I have two other comments:1. The way you have written the query it is like an inner join because T2.Qty is in the where clause. If you really want a left join - to pick up records from T1 for which there are no corresponding records in T2, change it toLEFT JOIN T2 ON T1.ITEM_NUM = T2.ITEM_NumWHERE and T1.QTY <> T2.QTY 2. >>but I keep getting back an error or it keeps looping and never ending!What is the error message? Also, I don't see any loops in the query, so I didn't quite follow what you meant by this. |
 |
|
|
rico1931
Starting Member
37 Posts |
Posted - 2011-03-08 : 16:53:20
|
quote:
Originally posted by sunitabeck
If ITEM_Num is really the primary key on both tables, you should not get any duplicate records. Otherwise, there may be multiple records in T2 with the same ITEM_Num, which can cause the problem you are seeing.I have two other comments:1. The way you have written the query it is like an inner join because T2.Qty is in the where clause. If you really want a left join - to pick up records from T1 for which there are no corresponding records in T2, change it toLEFT JOIN T2 ON T1.ITEM_NUM = T2.ITEM_NumWHERE and T1.QTY <> T2.QTY 2. >>but I keep getting back an error or it keeps looping and never ending!What is the error message? Also, I don't see any loops in the query, so I didn't quite follow what you meant by this.
YOU ROCK! thank you so MUCH! I didn't notice until after you told me that there are multiple Items with the same name! this fixes EVERYTHING! thanks ! |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-03-09 : 11:52:15
|
| I rock, don't I? *grins*Just kidding rico! Thanks for the kind "pick your spirits up" comment on an otherwise drab, dreary, dreadful, depressing, running out of adjectives kind of day.But, I still worry about your primary keys. If you indeed had ITEM_Num as the primary key on each table, this should not be happening. You know your data and system better than I do, so I will shut up. |
|
|
|
|
|
|
|
|
Joins for 2 tables