Solved

Need help with SQL Query comparing a matched row

Posted on 2013-05-17
23
481 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that undeā€¦
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how the fundamental information of how to create a table.

863 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

18 Experts available now in Live!

Get 1:1 Help Now