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

Does it give an error?

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

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 & "'

Select allOpen in new window

If enterNumber is an integer, take out the single quotes.  Or better yet, use a parameter instead of concatenating it into the query.

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.

@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

Dim EnterOEMNumber As String = txtEnterNumber.Text

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

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 & "'

Select allOpen in new window

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 & "'

Select allOpen in new window

try the one that is given by me just above in the comment ID: 39192374

@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

Select allOpen in new window

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?

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.

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.

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 & "'

Select allOpen in new window

@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

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

Select allOpen in new window

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

Select allOpen in new window

@Neo_Jarvis
That looks pretty good but i need my JD table selected to bring those results back. How can I add that?

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

Select allOpen 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

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

Select allOpen in new window

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.

@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

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

Select allOpen in new window

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

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 & "'"

Select allOpen in new window

what version of sql server please?

2008

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.

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'

Select allOpen in new window

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

https://www.experts-exchange.com/questions/28138157/Just-got-my-query-working-for-one-function-Now-i-need-to-get-my-other-function-working-just-like-it.html

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

k. thanks. please see if you can apply same concept to my UPDATE query :)

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 https://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.

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.