November 6, 2007 at 6:43 am
i have two tables, T1 and T2.
T1 has the columns:
RowID,
partID,
SellerCountryCode,
price...
...
T2 has the same columns.
I want to find the rows in T1 that are not in T2, based on PartID and sellerCountryCode. HOw do I do this using a join? I tried the below but it doesnt seem to work:
select * from T1 (nolock)
join T2 (nolock) on T1.partnumber = T2.partnumber
and T1.usagecountrycode = T2.usagecountrycode
where T1.partnumber <> T2.partnumber
and T1.usagecountrycode <> T2.usagecountrycode
and T1.rowID= 200
November 6, 2007 at 6:51 am
SELECT t1.*
FROM T1
LEFT JOIN T2
On T1.partID = T2.partID
And T1.SellerCountryCode = T2.SellerCountryCode
WHERE T2.partID is NULL
November 6, 2007 at 6:55 am
This is a fairly common request and it always amazes me that folks would have two identical tables and be worried about synching up their contents. The only time that should happen is if you have a staging table with new data and they're trying to merge that new data into the final table...
... so, I'm curious... what is the reason why you want to do this?
The answer to your question lies in a simple OUTER JOIN (read about them in Books Online... they're a very important tool in SQL)...
SELECT * FROM T1 WITH (NOLOCK)
LEFT OUTER JOIN T2 WITH (NOLOCK)
ON T1.PartNumber = T2.PartNumber
AND T1.UsageCountryCode = T2.UsageCountryCode
AND T1.RowID= 200
WHERE T1.PartNumber IS NULL
--Jeff Moden
Change is inevitable... Change for the better is not.
November 6, 2007 at 7:05 am
the contents arnt identical, but for the purposes of the issue here, they can be thought of as identical. T1 is an orders table. orders are created here. they are submitted and sent to T2. T2 stores orders that are about to be invoiced. the difference is that although there is only one order, the system has a way of renewing orders each year by reviewing complex business rules. so in T1 you can have many orders for invoicing that relate to just one order in the T1 table.
Outer join!! never entered my head. gota learn to deal with pressure and think rather than panic and lose all reason! thanks
November 6, 2007 at 9:24 am
i wrote a query but it took ages, so i came up with this instead:
select RowID, partnumber, countrycode
from tableA
where RowID = 200
except
select RowID, partnumber, countrycode
from tableB
where RowID = 200
this seems to work, however id like to know why my join didnt work. it just kept running and running until i killed it after 8 minutes. anyone have any ideas? here is my join:
select * from tableA as a(nolock)
left outer join tableB as b(nolock)
on a.partnumber = b.partnumber
and a.usagecountrycode = b.usagecountrycode
and a.purchaseorderid = 25084521
where b.purchaseorderid is null
November 6, 2007 at 10:06 am
The EXCEPT syntax is going to use all fields mentioned in the select (meaning ROWID+partnumber+countrycode). If you don't have an index with all three fields, that's going to turn into a hash join. Good news is that since you're going this on small subsets (rowid=200)- it remains somewhat efficient (or rather - it's small enough to be able to run right through it).
That being said - if you also don't have the right indexing scheme on the two tables, the outer join will also turn into a hash join (on a much large recordset, so that will be BAD). I'd think you need an index on both tables, with partnumber+usagecountrycode+purchaseOrderID.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 6, 2007 at 10:08 am
my join is actually returning data, but its returning thousands of rows, unlike the except query. they should be the same.
November 6, 2007 at 10:07 pm
winston Smith (11/6/2007)
this seems to work, however id like to know why my join didnt work. it just kept running and running until i killed it after 8 minutes. anyone have any ideas? here is my join:select * from tableA as a(nolock)
left outer join tableB as b(nolock)
on a.partnumber = b.partnumber
and a.usagecountrycode = b.usagecountrycode
and a.purchaseorderid = 25084521
where b.purchaseorderid is null
If partnumber and usagecountry code aren't unique, you end up with a partial Cartesian join which spawns many more internal rows than most would believe. Also, I believe you'll need to move a.purchaseorderid = 25084521 to the where clause...
--Jeff Moden
Change is inevitable... Change for the better is not.
November 6, 2007 at 10:34 pm
Hi,
try below querry, it should work
Select a.* from T1 a
left join T@ b on a.PartNumber = b.PartNumber
and a.UsageCountryCode = b.UsageCountryCode
where b.PartNumber is null
more over it is better to have you one unique key on partnumber and usagecountrycode.
November 7, 2007 at 2:30 am
as the tables are huge, and i cant go creating indexes on them as they are in a production envrionment, i just created temp tables and done a join on the temp tables that contain the specific data.
select * into #Table1
from MainTable1
where purchaseorderid = 200
select * into #Table2
from MainTable2
where salescountrycode = 'uk'
select a.RowID,a.partnumber,a.salesCountryCode,a.Quantity..............
from #Table1 a(nolock)
left outer join #Table2 b(nolock)
on a.partnumber = b.partnumber
and a.salesCountryCode = b.salesCountryCode
where b.RowID is null
and a.rowID = 200
This works perfectly. thanks for all the help guys.
(apologies if there are any syntactical errors above, i altered my code to display fake rows/tables that represent the real structure, as I cant put production code up on an online forum!)
November 7, 2007 at 6:17 am
Perfect use of "divide and conquer"... nicely done...
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply