?
Solved

Need help with SQL Query comparing a matched row

Posted on 2013-05-17
23
Medium Priority
?
498 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
How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

 
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 49

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 49

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 49

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 49

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 49

Accepted Solution

by:
PortletPaul earned 2000 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 49

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 49

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 49

Expert Comment

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

Featured Post

How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

752 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