Addie Baker
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
This is my table layouts
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
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
ASKER
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
why is that?
The OR condition is killing you here:
OR a.OEMPartNumber = b.OEMPartNumber
and here:
OR b.OEMsubnumber = c.OEMItem
The OR condition is killing you here:
OR a.OEMPartNumber = b.OEMPartNumber
and here:
OR b.OEMsubnumber = c.OEMItem
ASKER
Is there another way i can do it?
ASKER
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
ASKER
c.OEMItem
That is invalid
should it be ASN.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
actually all joins may need to be left joins, try it as above, if that's not good, then try all left joins please
ASKER
That returned NULL to imports.AMIPartNumbers
trying left joins now
trying left joins now
ASKER
with all left joins i still get NULL values in imports.AMIPartNumbers
ok, thanks I'll try some tests not sure how long
ASKER
thank you very much.
ASKER
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?
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
see: http://sqlfiddle.com/#!3/fcf67/1
ASKER
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
I need it to go to imports.AMIPartNumbers
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
in addition: you may need to take care to NOT override already existing data e.g. add
WHERE imports.AMIPartNumber is null
WHERE imports.AMIPartNumber is null
ASKER
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?
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?
ASKER
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?
so im good there right?
ASKER
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)
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)
ASKER
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
yes, you have the concept now - good work
ASKER
Open in new window