Joining where values do not equal one another

Question

Joining where values do not equal one another

jhinch

Valued Member

Points: 73
More actions
April 23, 2014 at 1:22 pm

#288164

I have two select statements; one for open purchase orders, one for open customer orders. I would like to be able to combine the query based on i.item in the top statement joined with c.item from the bottom statement. The i.item is related to a specific c.item, but they do not have the same values. In this case I want to join based on.
p.item=i.item where
1001099548=1001099550
84162359=84198545
84532300=84532293
47547523=47547951
305545A3=87433653
87444977=87444975
left side coming from p.item = right side coming from c.item.
Here are my two statements. I am a newbie with TSQL so I apologize for any formatting problems or less than best practices. Thank you in advance.
--#1 OPEN PO's
SELECT p.item
,(p.qty_ordered-p.qty_received) as POQtyRemaining
,i.item
,i.qty_on_hand
,p.po_num
,p.po_line
,p.po_release
,p.stat
,p.qty_ordered
,p.qty_received
,p.due_date
,p.promise_date
FROM [Ace_App].[dbo].[poitem] p
inner join [Ace_App].[dbo].[itemwhse] as i
on p.item = i.item
where (p.item = '1001099548' or p.item = '84162359' or p.item = '47547523' or p.item = '84532300' or p.item = '305545a3' or p.item = '87444977')
and (p.stat = 'P' or p.stat = 'O')
order by p.item, p.due_date
--#2 OPEN CO's
SELECT c.item
,(c.qty_ordered-c.qty_shipped) AS QtyRemaining
,i.qty_on_hand
,c.due_date
,c.co_num
,c.co_line
,c.co_release
,c.qty_ordered
,c.qty_shipped
,c.stat
,c.promise_date
,c.co_cust_num
FROM [Ace_App].[dbo].[coitem] c
inner join [Ace_App].[dbo].[itemwhse] as i
on c.item = i.item
where (c.item = '84198545' or c.item = '1001099550' or c.item = '84532293' or c.item = '47547951' or c.item = '87433653' or c.item = '87444975')
and (c.stat = 'P' or c.stat = 'O'
order by c.item, c.due_date

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Keith Tate SSCrazy Eights Points: 9938 More actions · Answer 1

Welcome to the forums. Please read the article in my signature for posting these types of questions to the forums. If you could provide create table statements and sample data along with desired output that would help get you a better answer quicker. Thanks

Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

jhinch (4/23/2014)
I have two select statements; one for open purchase orders, one for open customer orders. I would like to be able to combine the query based on i.item in the top statement joined with c.item from the bottom statement. The i.item is related to a specific c.item, but they do not have the same values. In this case I want to join based on.
p.item=i.item where
1001099548=1001099550
84162359=84198545
84532300=84532293
47547523=47547951
305545A3=87433653
87444977=87444975
left side coming from p.item = right side coming from c.item.
Here are my two statements. I am a newbie with TSQL so I apologize for any formatting problems or less than best practices. Thank you in advance.

Hi and welcome to the forums. You can't have a join match on dissimilar values. You could kludge this together in a horrendous fashion.

In you join condition.

on (p.item = 1001099548 and c.item = 1001099550)

OR (p.item = 84162359 and c.item = 84198545)

OR (p.item = 84532300 and c.item = 84532293)

OR (p.item = 47547523 and c.item = 47547951)

OR (p.item = 305545A3 and c.item = 87433653)

OR (p.item = 87444977 and c.item = 87444975)

OR (p.Item = c.Item)

This is really a poor format to say the least. This is killing the whole idea of letting the data work for you. Why do you have to join on values that are not the same???

If that doesn't do it we are likely going to need some more information to offer much help:

1. Sample DDL in the form of CREATE TABLE statements

2. Sample data in the form of INSERT INTO statements

3. Expected results based on the sample data

Please take a few minutes and read the first article in my signature for best practices when posting questions.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

I would create an additional table to include the item's codes conversion. That way you can add it to your joins to keep an equality.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

jhinch Valued Member Points: 73 More actions · Answer 4

I apologize for not using the proper forum etiquette. This was my first post ever, and I overlooked the etiquette section before posting. I will follow the proper procedure on future posts.

The reason I want to join differing values is because the p.item is a required material in the BOM for the c.item

I would like to order both statements by their due date so I can see if the raw material (p.item) quantity will be sufficient to produce the finished good (c.item). I just need a way to link the two values together based on the respective BOM requirement.

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

jhinch (4/23/2014)
I apologize for not using the proper forum etiquette. This was my first post ever, and I overlooked the etiquette section before posting. I will follow the proper procedure on future posts.
The reason I want to join differing values is because the p.item is a required material in the BOM for the c.item
I would like to order both statements by their due date so I can see if the raw material (p.item) quantity will be sufficient to produce the finished good (c.item). I just need a way to link the two values together based on the respective BOM requirement.

Without tables and sample data we can't even begin to guess at how you might do that. 😛

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

David Burrows SSC Guru Points: 65142 More actions · Answer 6

SELECTp.item

,(p.qty_ordered-p.qty_received) as POQtyRemaining

,ip.item

,ip.qty_on_hand

,p.po_num

,p.po_line

,p.po_release

,p.stat

,p.qty_ordered

,p.qty_received

,p.due_date

,p.promise_date

,c.item

,(c.qty_ordered-c.qty_shipped) AS QtyRemaining

,ic.qty_on_hand

,c.due_date

,c.co_num

,c.co_line

,c.co_release

,c.qty_ordered

,c.qty_shipped

,c.stat

,c.promise_date

,c.co_cust_num

FROM(VALUES

('1001099548','1001099550'),

('84162359','84198545'),

('84532300','84532293'),

('47547523','47547951'),

('305545A3','87433653'),

('87444977','87444975')

) l (pitem,citem)

JOIN [Ace_App].[dbo].[poitem] p ON p.item = l.pitem AND (p.stat = 'P' or p.stat = 'O')

JOIN [Ace_App].[dbo].[itemwhse] ip ON ip.item = p.item

JOIN [Ace_App].[dbo].[coitem] c ON c.item = l.citem AND (c.stat = 'P' or c.stat = 'O'

JOIN [Ace_App].[dbo].[itemwhse] ic ON ic.item = c.item

Far away is close at hand in the images of elsewhere.
Anon.

deepforest SSCommitted Points: 1780 More actions · Answer 7

You can insert the value pairs in the temp table (or a permanent one, if you do this on a regular basis):

declare @t table (t1 varchar(16),t2 varchar(16))

insert @t values ('1001099548','1001099550')

insert @t values ('84162359','84198545')

insert @t values ('84532300','84532293')

insert @t values ('47547523','47547951')

insert @t values ('305545A3','87433653')

insert @t values ('87444977','87444975')

and then join those tables into it:

select ...from

orders1 join @t t on orders1.item=t.t1

join orders2 on orders2.item=t.t2

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

jhinch (4/23/2014)
I apologize for not using the proper forum etiquette. This was my first post ever, and I overlooked the etiquette section before posting. I will follow the proper procedure on future posts.
The reason I want to join differing values is because the p.item is a required material in the BOM for the c.item
I would like to order both statements by their due date so I can see if the raw material (p.item) quantity will be sufficient to produce the finished good (c.item). I just need a way to link the two values together based on the respective BOM requirement.

Why not use the BOM tables as your link between raw material and finished goods?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden