Combine SQL Queries - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Combine SQL Queries

Author

Topic

radoslav
Starting Member

17 Posts

Posted - 2005-10-24 : 16:27:44

Hi Folks,

I have two tables – OrderLineItem and Shipment.
ShipmentID is foreign key in OrderLineItem. The OrderLineItem table is collecting all items in an order. Every shipment has unique ShipmentID.
The first query is returning Order No. 44036:

Select distinct i.OrderID, s.shipmentID
FROM shipment s, OrderLineItem i
WHERE s.orderiD = i.orderID
AND i.OrderID = 44036

with result:
OrderID ShipmentID
----------------------
44036 45661
44036 45735
44036 45984
44036 46681
----------------------
The second query is returning all of the shipments for one order:

Select i.FileNumber
From OrderLineItem i
Where ShipmentID = 45661

With result:

FileNumber
----------------------
312
----------------------

Is there a way in sql to combine both queries? In other words the second query to be based on the results of first query. So if I have four ShipmentID (45661, 45735, 45984, 46681 for one order (44036) like my example to run the second query based on that results and rerun it for every single shipmentID?
So the final result should be something like:
OrderID ShipmentID FileNumber
------------------------------------
44036 45661 243
44036 45735 234
44036 45984 323
44036 46681 329
------------------------------------

Thanks in advance for your help!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-10-24 : 17:04:39

What happens if you include i.FileNumber in the first query? Is the problem that you have multiple fileNumbers for each distinct (orderid, shipmentid)? If so which one do you want for your desired output example?


Select distinct i.OrderID, s.shipmentID, i.FileNumber
FROM   shipment s
join   OrderLineItem i
           on i.orderid = s.orderid
WHERE   i.OrderID = 44036

Be One with the Optimizer
TG

radoslav
Starting Member

17 Posts

Posted - 2005-10-24 : 17:27:41

Hi TG,

Yes, this is the problem. I have multiple fileNumbers for each distinct. I’ve made the query simple in my first post. Here is my original one (With your help from the last week. Thanks!).

----------------------------------------
declare @csv varchar(1000)
Select @csv = COALESCE(@csv + ', ' + FileNumber, FileNumber)
From OrderLineItem
Where ShipmentID = 46681
Select distinct i.OrderID, s.shipmentID, s.DateShipped, h.ShippingMethod,
(select @csv as FileNumber) as FileNumber
FROM shipment s, shipping h, orderlineitem i, Orders o
WHERE s.shippingID = h.shippingID
AND s.orderiD = i.orderID
AND i.Status <> 'Canceled'
AND i.OrderID = 44036
-----------------------------------------

Here is what this query return:
OrderID shipmentID DateShipped ShippingMethod FileNumber
44036 45661 2002-09-22 FAX 698, 343, 908
44036 45735 2002-09-23 FAX 698, 343, 908
44036 45984 2002-09-29 FAX 698, 343, 908
44036 46681 2002-10-15 FAX 698, 343, 908

As you can see the last row has the same result, because I’m using static value for ShipmentID (46681). This value I would like to make dynamic based on ShipingID. I don’t know if it’s possible or there is another way to implement it. I’ve never had such an issue before.
Thanks!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-10-24 : 21:15:54

radoslav, look again at the function example your original post.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=56728

The concept of a function taking an id and returning a csv would work for your situation. You would call it like this:


Select distinct i.OrderID, s.shipmentID, i.FileNumber, dbo.fn_GetFileNumbersByOrderID(i.orderid) as FileNumbers
FROM   shipment s
join   OrderLineItem i
           on i.orderid = s.orderid
WHERE   i.OrderID = 44036

Be One with the Optimizer
TG

radoslav
Starting Member

17 Posts

Posted - 2005-10-25 : 15:06:12

Hi,

I run the query base on your example http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=56728

It’s returning the following error: Ambiguous column name 'ShipmentID'.

Here is my query:

________________________
use MDA
go
create function dbo.udf_getProductsByOrderID(@shipmentID int)
returns varchar(8000)
as
begin
declare @csv varchar(8000)

select @csv = coalesce(@csv + ', ' + FileNumber, FileNumber)
from OrderLineItem
where ShipmentID = @ShipmentID

return @csv
end
go

select distinct s.shipmentID, s.ShippingWeight, s.DateShipped, h.ShippingMethod, dbo.udf_getProductsByOrderID(shipmentID) as FileNumber
FROM shipment s, shipping h, orderlineitem i
WHERE s.shippingID = h.shippingID
AND s.orderiD = i.orderID
AND i.Status <> 'Canceled'
AND i.OrderID = 44036
go
drop function dbo.udf_getProductsByOrderID
________________________

Once Again, the result is base on three tables – Shipment, Shipping and OrderLineItem. Every order has multiple items each could be shipped separately with ShipmentID. I want to show the content of each shipment (FileNumber) by ShipmentID.

OrderID | shipmentID | DateShipped | ShippingMethod | FileNumber
44036 | 45661 | 2002-09-22 | FAX | 698, 343, 908
44036 | 45735 | 2002-09-23 | FAX | 698, 343, 908
44036 | 45984 | 2002-09-29 | FAX | 698, 343, 908
44036 | 46681 | 2002-10-15 | FAX | 698, 343, 908

I’m doing something wrong but don’t know what.

Thanks in advance for your help!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-10-25 : 15:34:18

the error is due to not using a table alias in the argument of your function call:


select distinct ... dbo.udf_getProductsByOrderID(s.shipmentID) as

you should probably name your function something more appropriate like:
fn_FileNumbersByShipmentID

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-10-25 : 15:39:13

That distinct is kind of nasty especially when you include a function call in the select list. Maybe this will help a little:


select	a.OrderID
	,a.shipmentID
	,dbo.fn_FileNumbersByShipmentID(a.shipmentid) as FileNumbers
from	(
	Select distinct i.OrderID, s.shipmentID
	from	shipment s
	join	OrderLineItem i
			on i.orderID = s.orderid
	WHERE i.OrderID = 44036
	) a

Be One with the Optimizer
TG

radoslav
Starting Member

17 Posts

Posted - 2005-10-25 : 16:04:09

Hi TG,

Thanks again. This fixed the problem. I noticed that the query is compiling between 20 and 35 seconds. Is there a way to make it faster or this is a normal time to execute such a function?

Thanks!

radoslav
Starting Member

17 Posts

Posted - 2005-10-25 : 16:45:14

Hi TG,

You are right. The distinct is nasty. Using your previous statement reduced the speed to 2-3 seconds. This is awesome. I really appreciate your help!
My shipping method is in another table and I have to add it to the query. But don’t know how because you have braces in the statement and I don’t know the syntax in this way:

---------------------
select s.shipmentID, s.ShippingWeight, s.DateShipped, dbo.fn_FileNumbersByShipmentID(s.shipmentID) as FileNumber
FROM (
Select distinct s.shipmentID, s.ShippingWeight, s.DateShipped, h.ShippingMethod
FROM shipment s, OrderLineItem i, Shipping h WHERE s.shippingID = h.shippingID
AND s.orderiD = i.orderID
AND i.Status <> 'Canceled'
AND i.OrderID = 44036
) s
---------------------

I need to add h.ShippingMethod from shipping h. I tried to add it but I propably need to add “h” outside of braces like “s”

Thanks in advance!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-10-25 : 16:51:55

How many rows are returned for the 20-35 seconds? this kind of solution is definately not great for efficiency. I have used this solution for just a page of data at a time, like maybe 25 rows. In that case the performance was acceptable. However, if you are returning thousands of rows I would expect poor performance and you may want to consider other options.

Did you incorporate the last solution I posted to eliminate combining the function call with "distinct"? That will help a lot because you only want to apply that function to the final result set, not the pre-distinct results.

Be One with the Optimizer
TG

radoslav
Starting Member

17 Posts

Posted - 2005-10-25 : 17:00:33

Hi
Eliminating distinct from the function fixed the speed. Now it takes 2-3 seconds which is perfect for me. The only issue is including shipping table in the statement as a describe in my previous post.
Thanks a lot!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-10-25 : 17:17:50

You're welcome!
Yeah, to include shippingMethod just do the following. btw, I have been using the newer ansi-95 join syntax. You should too

I also changed the table alias for the derived table to remove the ambiguity.


select	tg.shipmentID
	,tg.ShippingWeight
	,tg.DateShipped
	,tg.ShippingMethod
	,dbo.fn_FileNumbersByShipmentID(tg.shipmentID) as FileNumber
FROM 	(
	Select distinct 
		s.shipmentID
		,s.ShippingWeight
		,s.DateShipped
		,h.ShippingMethod
	from	shipment s
	join	OrderLineItem i
			on s.orderiD = i.orderID
	join	Shipping h 
			on h.shippingID = s.shippingID
	where	i.Status <> 'Canceled'
	and	i.OrderID = 44036
	) tg

Be One with the Optimizer
TG

radoslav
Starting Member

17 Posts

Posted - 2005-10-25 : 18:05:21

Hi Again,

Using the newer ansi-95 join syntax is interesting point. I don’t use it because once I put the query in C# class as string it’s doesn’t work properly all the time and I don’t know why. With some queries does, with others doesn’t.

Here is how I enveloped the query in class:

string sql = "use MDA ";
sql += "go ";
sql += "create function dbo.fn_FileNumbersByShipmentID(@shipmentID int) ";
sql += "returns varchar(1000) ";
sql += "as ";
sql += "begin ";
and etc --------

To run this query in SQL I need for instance “go” to be in new row. I’m not sure how SQL read my string as showing above. Are there any special symbols I have to use to explain SQL that “go” is in the new row? Something likes using </br> in a string to explain the Web browsers that have a new line.

Thanks!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-10-25 : 20:41:45

I think all access to the database from applications should be done via stored procedures. There are a lot of advantages to doing it that way. However, even if you don't and continue to use "in-line" sql from code assembled in your application you should still compile the functions in sql. Just use query analyzer to compile the function then its there and available for use. You shouldn't be creating it at runtime.

The next time you have problems with the new join syntax, capture the code that's being sent to sql using sql profiler and try executing the "captured" code directly in query analyzer. If you still can't figure out the problem, post the code here. Someone here can help you out faster than you can say "sqlCommand object".

Be One with the Optimizer
TG

radoslav
Starting Member

17 Posts

Posted - 2005-10-28 : 09:55:29

I now it’s better to use store procedures but my application was developed using embedded sql statements. I have to keep it consistent. Thanks for advises!

I’ve never use functions in embedded sql statement and now I have following problem:

In SQL Query Analyser

----this is correct--------
go
create function dbo.fn_FileNumbersByShipmentID(@shipmentID int)
----this is incorrect------
go create function dbo.fn_FileNumbersByShipmentID(@shipmentID int)

in my embedded statement I have:
sql += "go ";
sql += "create function dbo.fn_FileNumbersByShipmentID(@shipmentID int) ";
I thing my embedded statement is failed because once sql is trying to read the statement as:
go sql += "create function dbo.fn_FileNumbersByShipmentID(@shipmentID int)
Is there a symbol I can put between the two parts of embedded statements so sql can treat it as new row when read it?

Thanks a lot!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-10-28 : 10:21:59

I think you are confusing "using" functions in in-line sql with "creating" functions in in-line sql. Why don't you just create the function at design time in a QA window and leave it there? You should consider sql functions as part of the DDL (like tables and views). If you do that, you won't need any "go" statements in your in-line code.

Be One with the Optimizer
TG

radoslav
Starting Member

17 Posts

Posted - 2005-10-28 : 10:48:43

Hi
I’m little confused. Let me explain. My solution is web application, containing two projects. The first one contains all of the web pages. The second one is all of the classes. I’m trying to implement the in-line statement in one of the classes. After that I’m compiling all of the classes in single dll and this dll is used by project 1.
This class with the function also uses dynamic value as a part of the sql statement.
How should I create the function at design time with QA with dynamic value?

Thanks

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-10-28 : 11:02:28

you should be able make the dynamic value one of the function arguments (like @shipmentid).

If you need to build CSVs based on different columns you should create a function for each type data. ie:
fn_FileNumbersByShipmentID(@shipmentid int)
fn_ShipmentIDsByOrderID(@orderid int)
etc...

Be One with the Optimizer
TG

radoslav
Starting Member

17 Posts

Posted - 2005-10-28 : 12:11:07

Finally I got it.
TG, this was a great help in my SQL discovery!
Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources