Link to home
Create AccountLog in
Avatar of Addie Baker
Addie BakerFlag for United States of America

asked on

Need help with SQL Query comparing a matched row

I have the code below working and bringing back the OEMPartNumber.

My problem is the OEMSubNumber. AS you can see in the table I cannot match the OEMSubNumber like i did the in sql i got working. I have to match the OEMSubNumber to OEMPartNumber then back to OEM Item.. How can i do this?

sql that is working
UPDATE a 
SET a.AMIPartNumber = c.Item 
FROM imports as a 
INNER JOIN jdsubs as b 
ON a.OEMPartNumber = b.OEMPartNumber 
INNER JOIN amipartnumbers as c 
ON b.OEMPartNumber = c.OEMItem

Open in new window


This is my table layouts
imports
----------------
OEMPartNumber  | AMIPartNumber
AR77530        |
AR12345        |

JDSubs
---------------------------
OEMPartNumer    | OEMSubNumber
AR65123         | AR77530
AR12345         | AR56242

AMI
---------------------------
Item            | OEMItem   | Description
AMAR65123       | AR65123   | Axle
AMAR56242       | AR12345   | Spindle

Open in new window

Avatar of Addie Baker
Addie Baker
Flag of United States of America image

ASKER

I thought i would have it working on this one, but still wont work

UPDATE a 
SET a.AMIPartNumber = c.Item 
FROM imports as a 
INNER JOIN jdsubs as b 
ON a.OEMPartNumber = b.OEMPartNumber 
INNER JOIN jdsubs 
ON a.OEMPartNumber = b.OEMsubnumber 
INNER JOIN amipartnumbers as c 
ON b.OEMPartNumber = c.OEMItem 
INNER JOIN amipartnumbers 
ON b.OEMsubnumber = c.OEMItem

Open in new window

okay, i finally got this working but it takes a while compared to the working query in last post. why is that?

UPDATE a 
SET a.AMIPartNumber = c.Item 
FROM imports as a 
INNER JOIN jdsubs as b 
ON a.OEMPartNumber = b.OEMsubnumber 
OR a.OEMPartNumber = b.OEMPartNumber 
INNER JOIN amipartnumbers as c 
ON b.OEMPartNumber = c.OEMItem 
OR b.OEMsubnumber = c.OEMItem 

Open in new window

Avatar of Anthony Perkins
why is that?
The OR condition is killing you here:
OR a.OEMPartNumber = b.OEMPartNumber
and here:
OR b.OEMsubnumber = c.OEMItem
Is there another way i can do it?
create another sql query?
please try this as a select, I think the field you need in the update will be via the coalesce below
SELECT
  I.OEMPartNumber i_OEMPartNumber
, I.AMIPartNumber
, PN.OEMPartNumer pn_OEMPartNumer
, PN.OEMSubNumber pn_OEMSubNumber
, SN.OEMPartNumer sn_OEMPartNumer
, SN.OEMSubNumber sn_OEMSubNumber
, apn.Item apn_Item
, asn.Item asn_Item
, apn.description apn_desc
, asn.description asn_desc
, coalesce(apn.Item,asn.Item) item_to_use -- I think
FROM imports as I 
INNER JOIN jdsubs as PN         ON I.OEMPartNumber = PN.OEMPartNumber 
LEFT JOIN amipartnumbers as APN ON PN.OEMPartNumber = APN.OEMItem 
INNER JOIN jdsubs as SN         ON I.OEMPartNumber = SN.OEMSubNumber 
LEFT JOIN amipartnumbers as ASN ON SN.OEMPartNumber = c.OEMItem

Open in new window

c.OEMItem

That is invalid

should it be ASN.OEMItem
yes, sorry

actually all joins may need to be left joins, try it as above, if that's not good, then try all left joins please
That returned NULL to imports.AMIPartNumbers

trying left joins now
with all left joins i still get NULL values in imports.AMIPartNumbers
ok, thanks I'll try some tests not sure how long
thank you very much.
okay i ran the query in my management studio and i see it creates a column items_to_use and those are the number is use. And it works fast!

How can i get that column into imports.AMIPartNumbers?
this worked for me:
SELECT
  I.OEMPartNumber i_OEMPartNumber
, I.AMIPartNumber
, coalesce(apn.Item,asn.Item) item_to_use -- I think
, null coldiv
, PN.OEMPartNumer pn_OEMPartNumer
, PN.OEMSubNumber pn_OEMSubNumber
, SN.OEMPartNumer sn_OEMPartNumer
, SN.OEMSubNumber sn_OEMSubNumber
, apn.Item apn_Item
, asn.Item asn_Item
, apn.description apn_desc
, asn.description asn_desc
FROM imports as I 
LEFT JOIN jdsubs as PN          ON I.OEMPartNumber = PN.OEMPartNumer 
LEFT JOIN amipartnumbers as APN ON PN.OEMPartNumer = APN.OEMItem 
LEFT JOIN jdsubs as SN          ON I.OEMPartNumber = SN.OEMSubNumber 
LEFT JOIN amipartnumbers as ASN ON SN.OEMPartNumer = ASN.OEMItem

Open in new window

see: http://sqlfiddle.com/#!3/fcf67/1
Well as i mentioned in my last comment, the last query worked but its putting results into item_to_use.

I need it to go to imports.AMIPartNumbers
ASKER CERTIFIED SOLUTION
Avatar of PortletPaul
PortletPaul
Flag of Australia image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
in addition: you may need to take care to NOT override already existing data e.g. add

WHERE imports.AMIPartNumber is null
thank you SO MUCH!

That works about 20x's faster than the OR

One more question if you dont mind.

I also have to compare to ihsubs, nhsubs and a few more.

Should i just add those to this same query by duplicating everything but select * from imports and changing 'jd' to 'ih' etc...
OR should i make separate sql querys and run them all? what is more effective?
as far as overriding. this table is just used temporarily to store data and is cleared every time user uploads their file.

so im good there right?
great work!
>>this table is  just used temporarily to store data ... so im good there right?
yes

>>I also have to compare to ihsubs, nhsubs and a few more. Should i just add those to this same query by duplicating everything but select * from imports and changing 'jd' to 'ih' etc...
OR should i make separate sql querys and run them all? what is more effective?


for the select? don't know for sure - possibly (maybe: good chance)
Yep, this works great by adding 'ih'

UPDATE imports
SET imports.AMIPartNumber = coalesce(apn.Item,asn.Item,aqn.Item,arn.Item)
FROM imports as I 
LEFT JOIN jdsubs as PN ON I.OEMPartNumber = PN.OEMPartNumber 
LEFT JOIN amipartnumbers as APN ON PN.OEMPartNumber = APN.OEMItem 
LEFT JOIN jdsubs as SN ON I.OEMPartNumber = SN.OEMSubNumber 
LEFT JOIN amipartnumbers as ASN ON SN.OEMPartNumber = ASN.OEMItem
LEFT JOIN ihsubs as QN ON I.OEMPartNumber = QN.OEMPartNumber 
LEFT JOIN amipartnumbers as AQN ON QN.OEMPartNumber = AQN.OEMItem 
LEFT JOIN ihsubs as RN ON I.OEMPartNumber = RN.OEMSubNumber 
LEFT JOIN amipartnumbers as ARN ON RN.OEMPartNumber = ARN.OEMItem
;

select
*
from imports

Open in new window

yes, you have the concept now - good work