Solved

Trying to get this query to work. Select, Coalesce, From, Inner Join, Where

Posted on 2013-05-23
36
292 Views
Last Modified: 2013-05-24
I have a search on my aspx page that allows users to search and OEM Part Number.
This Number could be an OEMPartNumber or OEMSubnumber or OEMSubNumber2.

It needs to match my AMIPartNumber Table to OEMItem and Display Item (which is my part number)

Here is what i have but giving me no results

EnterNumber is my textbox on form

SELECT * ,
coalesce(AMIA.Item,AMIB.Item,AMIC.Item)
FROM JD as I
INNER JOIN amipartnumbers as AMIA 
ON AMIA.OEMItem = I.OEMPartNumber
INNER JOIN amipartnumbers as AMIB 
ON AMIB.OEMItem = I.OEMPartNumber
INNER JOIN amipartnumbers as AMIC 
ON AMIC.OEMItem = I.OEMSubNumber2
where coalesce(I.OEMPartNumber,I.OEMSubNumber,I.OEMSubNumber2)= '" & enterNumber & "'

Open in new window

0
Comment
Question by:bignadad
  • 17
  • 6
  • 5
  • +3
36 Comments
 
LVL 15

Expert Comment

by:David L. Hansen
ID: 39192304
Does it give an error?
0
 
LVL 2

Author Comment

by:bignadad
ID: 39192315
No, just tells me no results found in my message box.

MessageBox("No AMI Part Number is Availabe for the OEM Part Number Entered")

That is initiated if no number is retrieved

i built the query in sql server manager studio and it produced no errors
0
 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39192319
try this

SELECT * ,
coalesce(AMIA.Item,AMIB.Item,AMIC.Item)
FROM JD as I
INNER JOIN amipartnumbers as AMIA 
ON AMIA.OEMItem = I.OEMPartNumber
INNER JOIN amipartnumbers as AMIB 
ON AMIB.OEMItem = I.OEMPartNumber
INNER JOIN amipartnumbers as AMIC 
ON AMIC.OEMItem = I.OEMSubNumber2
where I.OEMPartNumber = '" & enterNumber & "' or I.OEMSubNumber = '" & enterNumber & "' or I.OEMSubNumber2 = '" & enterNumber & "'

Open in new window

0
 
LVL 9

Expert Comment

by:joshbula
ID: 39192321
If enterNumber is an integer, take out the single quotes.  Or better yet, use a parameter instead of concatenating it into the query.
0
 
LVL 15

Expert Comment

by:David L. Hansen
ID: 39192332
My guess is that you are INNER JOIN'ing to the point where all records are filtered out.  Run it without any INNER JOIN's then if you get results, run it with the first INNER JOIN and so on.  When you get zero returned records you'll know why.
0
 
LVL 2

Author Comment

by:bignadad
ID: 39192364
@Neo_Jarvis
That displayed no results as well

@jashbula
enterNumber is a string
Dim EnterOEMNumber As String = txtEnterNumber.Text

@sl8rz
How do i run it without the inner join's? if i take "INNER JOIN" out i get errors
0
 
LVL 9

Expert Comment

by:joshbula
ID: 39192370
Dim EnterOEMNumber As String = txtEnterNumber.Text

That's declaring "EnterOEMNumber" as the variable... should you be using that instead of "enterNumber" ?
0
 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39192374
I think I got it this time

check the below one

SELECT * ,
coalesce(AMIA.Item,AMIB.Item,AMIC.Item)
FROM JD as I
INNER JOIN amipartnumbers as AMIA 
ON AMIA.OEMItem = I.OEMPartNumber
where AMIA.OEMPartNumber = '" & enterNumber & "' or AMIA.OEMSubNumber = '" & enterNumber & "' or AMIA.OEMSubNumber2 = '" & enterNumber & "'

Open in new window

0
 
LVL 2

Author Comment

by:bignadad
ID: 39192385
i tried using RIGHT JOIN's and i get results now
BUT
if i search a number that is in OEMSubNumber i don't get results

SELECT * ,
coalesce(AMIA.Item,AMIB.Item,AMIC.Item) 
FROM JD as I 
RIGHT JOIN amipartnumbers as AMIA 
ON AMIA.OEMItem = I.OEMPartNumber 
RIGHT JOIN amipartnumbers as AMIB 
ON AMIB.OEMItem = I.OEMPartNumber 
RIGHT JOIN amipartnumbers as AMIC 
ON AMIC.OEMItem = I.OEMSubNumber2 
where coalesce(I.OEMPartNumber,I.OEMSubNumber,OEMSubNumber2) = '" & enterNumber & "'

Open in new window

0
 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39192397
try the one that is given by me just above in the comment ID: 39192374
0
 
LVL 2

Author Comment

by:bignadad
ID: 39192405
@joshbula
Sorry. That is where i originally establish that variable.
I call a function where is uses enterNumber
Public Function fnSearchAndPopulate(ByVal enterNumber As String) As DataTable

@neo_jarvis
I got errors on all AMIA.*** in WHERE clause
OEMPartNumber, SubNumber and SubNumber2 are in JD table

JD
---------------------------
OEMPartNumer    | OEMDescription | OEMSubNumber| OEMSubNumber2 (etc)
AR65123         |   Gear       |     AR77530     |     AR25684
AR12345         |   Gear       |     AR56242     |     AR42154

amipartnumbers
---------------------------
Item            | OEMItem   | Description
AMAR65123       | AR65123   | Axle
AMAR56242       | AR42154   | Spindle

Open in new window

0
 
LVL 26

Expert Comment

by:Shaun Kline
ID: 39192415
Are you assured that if OEMSubNumber has a value that OEMPartNumber will be null? Similarly, if OEMSubNumber2 has a value, are you assured that OEMPartNumber and OEMSubNumber will be null?
0
 
LVL 15

Expert Comment

by:David L. Hansen
ID: 39192418
Right Joins preserve the table you are joining to...nothing is lost..at all.  Repeat: NOTHING is lost.  Matches from the joining table are match up where there happens to be a match (nulls are "matched" to everything else).  Inner Joins, however, REMOVE ALL rows from both tables that have no match.  Hope that helps.

Also,
SELECT *, ..... FROM ....INNER JOIN.....

This approach will let you test each join.  Just add the next one on then the next.  When doing this test leave the WHERE clause out completely.
0
 
LVL 26

Expert Comment

by:Shaun Kline
ID: 39192429
COALESCE is used for finding the first item in the list of values that is NOT NULL.

Consider using a UNION of SELECT statements, if you do not need to bring back all fields from all tables.
0
 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39192435
then you have to write down your query as below

SELECT * ,
coalesce(AMIA.Item,AMIB.Item,AMIC.Item)
FROM JD as I
LEFT JOIN amipartnumbers as AMIA 
ON AMIA.OEMItem = I.OEMPartNumber
LEFT JOIN amipartnumbers as AMIB 
ON AMIB.OEMItem = I.OEMSubNumber
LEFT JOIN amipartnumbers as AMIC 
ON AMIC.OEMItem = I.OEMSubNumber2
where I.OEMPartNumber = '" & enterNumber & "' or I.OEMSubNumber = '" & enterNumber & "' or I.OEMSubNumber2 = '" & enterNumber & "'

Open in new window

0
 
LVL 2

Author Comment

by:bignadad
ID: 39192512
@skaun_Kline
OEMPartNumber will always have a value. The Subs value will vary. Sometimes there, sometimes not.
How would i use the UNION or SELECT in my query? Maybe i dont need the coalesce? I was just thinking since i have to find the AMI.Item that was not null that matched the OEM number enterNumber was looking for that would do it.

@sl8rz
Going to play around with that. Thanks for the tip

@Neo_Jarvis
Sorry, that is now working either. No error but when i search it does not match the number i enter to the AMI.Item.


**This is the process**
The number entered by user should find that number in JD.OEMPartNumber, JD.OEMSubNumber or JD.OEMSubNumber2.
AFter that it should match one of those 3 numbers to the AMI.OEMItem and bring back the AMI.Item
0
 
LVL 2

Author Comment

by:bignadad
ID: 39192557
Also. this is the query i have working on my CSV option for user to import file and it sends it back to user after running this query.

I get the results i need from that.

This query im doing on this question relates to populating textbox's for users to enter one number at a time

http://bakerabilene.com/interchange.aspx

UPDATE imports 
SET imports.AMIPartNumber = 
coalesce(AMIA.Item,AMIB.Item,AMIC.Item,AMID.Item,AMIE.Item), 
Imports.AMIDescription = 
coalesce(AMIA.Description,AMIB.Description,AMIC.Description,AMID.Description,AMIE.Description) 
FROM imports as I 
LEFT JOIN JD as JDA 
ON I.OEMPartNumber = JDA.OEMPartNumber 
LEFT JOIN amipartnumbers as AMIA 
ON JDA.OEMPartNumber = AMIA.OEMItem 
LEFT JOIN JD as JDB 
ON I.OEMPartNumber = JDB.OEMSubNumber 
LEFT JOIN amipartnumbers as AMIB 
ON JDB.OEMSubNumber = AMIB.OEMItem 
LEFT JOIN JD as JDC 
ON I.OEMPartNumber = JDC.OEMSubNumber2 
LEFT JOIN amipartnumbers as AMIC 
ON JDB.OEMSubNumber2 = AMIC.OEMItem 
LEFT JOIN JD as JDD 
ON I.OEMPartNumber = JDD.OEMSubNumber3 
LEFT JOIN amipartnumbers as AMID 
ON JDB.OEMSubNumber2 = AMID.OEMItem 
LEFT JOIN JD as JDE 
ON I.OEMPartNumber = JDE.OEMSubNumber4 
LEFT JOIN amipartnumbers as AMIE 
ON JDB.OEMSubNumber2 = AMIE.OEMItem ;

select * from imports

Open in new window

0
 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39192558
ok, my bad trying to run into the previous provided logic does not work for your scenario...

check this out

select ami.item
from amipartnumbers ami,
(
select OEMPartNumber as OEMItem from JD WHERE OEMPartNumber = '" & enterNumber & "'
union
select OEMSubNumber as OEMItem from JD WHERE OEMSubNumber = '" & enterNumber & "'
union
select OEMSubNumber2 as OEMItem from JD WHERE OEMSubNumber2 = '" & enterNumber & "'
) A
where ami.OEMItem = a.OEMItem

Open in new window

0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 2

Author Comment

by:bignadad
ID: 39192577
@Neo_Jarvis
That looks pretty good but i need my JD table selected to bring those results back. How can I add that?
0
 
LVL 2

Author Comment

by:bignadad
ID: 39192592
i tried this. is this right?

select ami.item, *
from amipartnumbers ami, JD,
(
select OEMPartNumber as OEMItem from JD WHERE OEMPartNumber = '" & enterNumber & "'
union
select OEMSubNumber as OEMItem from JD WHERE OEMSubNumber = '" & enterNumber & "'
union
select OEMSubNumber2 as OEMItem from JD WHERE OEMSubNumber2 = '" & enterNumber & "'
) A
where ami.OEMItem = a.OEMItem

Open in new window


If so, when searching it does take a little longer than normal (30 seconds) and brings back bad results from JD table. the results are right from amipartnumber table but not for JD table.

after searching many items its bring back the same results for JD everytime. like i said AMI info is right though
0
 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39192602
then you can try this one out
note: this one will have one extra column saying OEMItem1 in the result set

select ami.item,a.*
from amipartnumbers ami,
(
select OEMPartNumber as OEMItem1,* from JD WHERE OEMPartNumber = '" & enterNumber & "'
union
select OEMSubNumber as OEMItem1,* from JD WHERE OEMSubNumber = '" & enterNumber & "'
union
select OEMSubNumber2 as OEMItem1,* from JD WHERE OEMSubNumber2 = '" & enterNumber & "'
) A
where ami.OEMItem = a.OEMItem1

Open in new window

0
 
LVL 15

Expert Comment

by:David L. Hansen
ID: 39192608
Forget everything (variables, WHERE clause, UNIONS) we've got to get a handle on your data. Take apart your query make sure you understand what each piece is returning.  Then we can be confident in the final query.  Just getting rows back doesn't mean it is right.
0
 
LVL 2

Author Comment

by:bignadad
ID: 39192621
@NeoJarvis
Ill try that in just a bit

@sl8rz
Im going to break everything down and map it out.

Will be in touch in just a little while
0
 
LVL 2

Author Comment

by:bignadad
ID: 39192967
im trying to wrap my head around this and don't think its as hard as im making it out to be.

the query below (as i mentioned above) is doing exactly what is should. only difference between that and what im trying to do now it that its updating a table and im just selecting to display on my aspx page.

Also I.OEMPartNumber (aka JD) is the number being compared in that query and for this one i need the string enterNumber to be compared.

So how can i just convert the following query to work for what i need now?

UPDATE imports 
SET imports.AMIPartNumber = 
coalesce(AMIA.Item,AMIB.Item,AMIC.Item,AMID.Item,AMIE.Item), 
Imports.AMIDescription = 
coalesce(AMIA.Description,AMIB.Description,AMIC.Description,AMID.Description,AMIE.Description) 
FROM imports as I 
LEFT JOIN JD as JDA 
ON I.OEMPartNumber = JDA.OEMPartNumber 
LEFT JOIN amipartnumbers as AMIA 
ON JDA.OEMPartNumber = AMIA.OEMItem 
LEFT JOIN JD as JDB 
ON I.OEMPartNumber = JDB.OEMSubNumber 
LEFT JOIN amipartnumbers as AMIB 
ON JDB.OEMSubNumber = AMIB.OEMItem 
LEFT JOIN JD as JDC 
ON I.OEMPartNumber = JDC.OEMSubNumber2 
LEFT JOIN amipartnumbers as AMIC 
ON JDB.OEMSubNumber2 = AMIC.OEMItem 
LEFT JOIN JD as JDD 
ON I.OEMPartNumber = JDD.OEMSubNumber3 
LEFT JOIN amipartnumbers as AMID 
ON JDB.OEMSubNumber2 = AMID.OEMItem 
LEFT JOIN JD as JDE 
ON I.OEMPartNumber = JDE.OEMSubNumber4 
LEFT JOIN amipartnumbers as AMIE 
ON JDB.OEMSubNumber2 = AMIE.OEMItem ;

select * from imports

Open in new window

0
 
LVL 2

Author Comment

by:bignadad
ID: 39193032
I drew up this trying to see if it sparked somthing and im still at a loss. Im just not well versed enough in sql query to know what i need to do.

Looking at this diagram it does seem much easier than i was making it to be (in my mind at least)

hope this helps someone figure this out

query diagram
0
 
LVL 2

Author Comment

by:bignadad
ID: 39193058
i actually got this query to work but there is a flaw with it.

It only works if the JD.OEMPartNumber matches the AMI.OEMItem

So if i search a number and its found in JD.OEMPartNumber or JD.OEMSubNumber AS LONG AS the JD.OEMPartNumber matches the AMI.OEMItem it will work.

BUT if the number is found in in JD.OEMPartNumber or JD.OEMSubNumber AND THE JD.OEMSubNumber matches the AMI.OEMItem it won't work.

"SELECT * FROM JD Inner Join amipartnumbers on " &
            " amipartnumbers.oemitem=JD.oempartnumber WHERE " &
            " JD.oempartnumber= '" & enterNumber & "' or " &
            " JD.oemsubnumber= '" & enterNumber & "' or " &
            " JD.oemsubnumber2= '" & enterNumber & "'"

Open in new window

0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39193148
what version of sql server please?
0
 
LVL 2

Author Comment

by:bignadad
ID: 39193152
2008
0
 
LVL 2

Author Comment

by:bignadad
ID: 39193226
did more testing and the query i posted above and said was working on my import task is really not working. only brings back some results. I dont understand why this is so hard. looking at that diagram i did it seems like it should be straight forward.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39193257
see if this helps, it transposes the JD table using a cross apply technique so only one field needs to be used in the filter
select
  coalesce(APN.Item,APN1.Item) Item
, coalesce(APN.Description,APN1.Description) Description
, JD.OEMPartNumer
, xapply.OEMRef
, xapply.lvl
, JD.OEMDescription
from JD
cross apply (
  values 
   (1, OEMPartNumer )
  ,(2, OEMSubNumber )
  ,(3, OEMSubNumber2)
) as xapply (lvl, OEMRef)
left join amipartnumbers as APN  on JD.OEMPartNumer = APN.OEMItem
left join amipartnumbers as APN1 on xapply.OEMRef = APN1.OEMItem
where OEMRef = 'AR25684'

Open in new window

0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 39193381
maybe this is better?
declare @Seek as varchar(20)
set @seek = 'AR42154'

select
*
from amipartnumbers as APN 
left join (
            select *
            from JD
            cross apply (
                values 
                 (1, OEMPartNumer )
                ,(2, OEMSubNumber )
                ,(3, OEMSubNumber2)
                --,(4, OEMSubNumber3)
                --,(5, OEMSubNumber4)
              ) as xapply (lvl, OEMRef)
           ) JDT on APN.OEMItem = JDT.OEMRef
where OEMPartNumer = @Seek
or OEMSubNumber    = @seek
or OEMSubNumber2   = @seek
--or OEMSubNumber3   = @seek
--or OEMSubNumber4   = @seek

Open in new window

http://sqlfiddle.com/#!3/8ac12/48
0
 
LVL 2

Author Closing Comment

by:bignadad
ID: 39194095
THANK YOU!!! This query works 100% perfect. It matches any oem or sub number with the AMI.Item perfectly. I could have never come up with this on my own.

Also, it is fast. only a couple seconds and result it there!

COULD YOU PLEASE help me with my next question relating to this same concept but using UPDATE instead of SELECT

http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/Q_28138157.html
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39194115
just as a note, I had left join above by error - it shoud have been inner join
it won't make any difference to results
0
 
LVL 2

Author Comment

by:bignadad
ID: 39194124
k. thanks. please see if you can apply same concept to my UPDATE query :)
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39194180
well, yes I could but I thought that update was OK

the real problem here is that table JD is "non-normalised" and what the accepted solution here does is to "force" it into a normalised form. That's fine I think for a small select query accessed via a web form, but it might not perform too well on a larger set of data.

ps: if you would like more on this technique see http://www.experts-exchange.com/blogs/PortletPaul/B_7511-Unpivotting-by-CROSS-APPLY-and-VALUES.html
I'll look at the other question, but it's late in the night for me.
0
 
LVL 2

Author Comment

by:bignadad
ID: 39194211
Yes, as i mentioned above in question ID: 39193226, after more testing i found that it wasn't working for all my numbers where OEMItem didnt match the number being searched.

im testing more extensively to make sure im getting the write info. i tested your query that worked quite a bit and can find no flaw in it.

I understand its late. Thanks for what you have done so far. ill take a look at that link and see what i can figure out. if you get time late check out that question and see if its still up or if i figured it out.
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

758 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

19 Experts available now in Live!

Get 1:1 Help Now