SQL Server Query Help Needed
Home > IT Answers > SQL Server > SQL Server Query Help Needed
Arlin
70 pts.
0
Q:
SQL Server Query Help Needed
SQL Server Query, SQL queries, SQL Query, SQL, SQL Server development, JOIN statement, LEFT JOIN, RIGHT JOIN, INNER JOIN
I have a table with Inventory transacation records.
The table contains both Shipment (outgoing) and Receiving (incoming) transactions.
I'm trying to write a report that lists both Sales Orders that result in Shipment records and Purchase Orders that result in Receiving records.
If I link the Inventory Transaction table to the Shipment (Sales Order) tables and to the Receiving (Purchase Order) tables, I only get one or the other (Sales or Purchasing) records, not both. Is there a trick to this with Left, Inner and Right joins on the tables or do I need some other approach?

Thanks for the help,
Arlin
ASKED: Feb 5 2009  10:39 PM GMT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
RELATED QUESTIONS
0
Carlosdl
29805 pts.
0
A:
 RATE THIS ANSWER
+1
Click to Vote:
  •   1
  •  0
  • AddThis Social Bookmark Button
How about a UNION of two queries, one from sales,transactions (shipments) and the other from purchases,transactions (receivings) ?
Last Answered: Feb 5 2009  11:35 PM GMT by Carlosdl   29805 pts.
  •   
0
0
RSS - Discussions Help
Discuss This Answer:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _



_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

Sivu   105 pts.  |   Feb 6 2009  9:32AM GMT

That depends on your data, if you are maintaining separate keys for sales and purchase orders of the each product then there should be no problem as such, else :
1. you do a full outer join on these tables with your inventory table
2. write a two queries for sales & purchase and run both with UNION ALL in between two SQLs.

 

Arlin   70 pts.  |   Feb 6 2009  4:46PM GMT

Hi Sivu,
I have GUID’s in the Inventory Table for Sales and Purchasing.
When I link the Sales table, I don’t see the link I’ve made to the Purchasing table and vice versa.
I’ll try the Union All.
Thanks for the tip.
Arlin

 
0