Solved

Need help with SQL Query comparing a matched row

Posted on 2013-05-17
23
479 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
  • 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
 
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how the fundamental information of how to create a table.

747 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now