Solved

Need help with SQL Query comparing a matched row

Posted on 2013-05-17
23
487 Views
Last Modified: 2013-05-17
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

0
Comment
Question by:bignadad
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 14
  • 8
23 Comments
 
LVL 2

Author Comment

by:bignadad
ID: 39175833
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

0
 
LVL 2

Author Comment

by:bignadad
ID: 39175879
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

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39176509
why is that?
The OR condition is killing you here:
OR a.OEMPartNumber = b.OEMPartNumber
and here:
OR b.OEMsubnumber = c.OEMItem
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 2

Author Comment

by:bignadad
ID: 39176511
Is there another way i can do it?
0
 
LVL 2

Author Comment

by:bignadad
ID: 39176512
create another sql query?
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39176624
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

0
 
LVL 2

Author Comment

by:bignadad
ID: 39176628
c.OEMItem

That is invalid

should it be ASN.OEMItem
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39176629
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
0
 
LVL 2

Author Comment

by:bignadad
ID: 39176631
That returned NULL to imports.AMIPartNumbers

trying left joins now
0
 
LVL 2

Author Comment

by:bignadad
ID: 39176633
with all left joins i still get NULL values in imports.AMIPartNumbers
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39176636
ok, thanks I'll try some tests not sure how long
0
 
LVL 2

Author Comment

by:bignadad
ID: 39176637
thank you very much.
0
 
LVL 2

Author Comment

by:bignadad
ID: 39176641
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?
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39176643
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
0
 
LVL 2

Author Comment

by:bignadad
ID: 39176648
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
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 39176652
UPDATE imports
SET imports.AMIPartNumber = coalesce(apn.Item,asn.Item)
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
;

select
*
from imports

Open in new window

http://sqlfiddle.com/#!3/ff223/1
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39176655
in addition: you may need to take care to NOT override already existing data e.g. add

WHERE imports.AMIPartNumber is null
0
 
LVL 2

Author Comment

by:bignadad
ID: 39176659
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?
0
 
LVL 2

Author Comment

by:bignadad
ID: 39176661
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?
0
 
LVL 2

Author Closing Comment

by:bignadad
ID: 39176662
great work!
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39176667
>>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)
0
 
LVL 2

Author Comment

by:bignadad
ID: 39176683
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

0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39176693
yes, you have the concept now - good work
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question