Selecting values off off multiple tables

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 2005 Forums

Transact-SQL (2005)

Selecting values off off multiple tables

Author

Topic

trackjunkie
Starting Member

31 Posts

Posted - 2009-09-09 : 15:36:07

I am making a stored proceedure for a crystal report. I want to use two parameters and return two seperate values from two tables which are not logically connected in any way. I have:

ALTER Procedure [dbo].[Flex_RPT_FlexCert1] (
@order nvarchar(10)
, @lot nvarchar(10)

)
AS
SELECT lot.item, coh.cust_num
FROM lot, coh

where ltrim (lot.lot) = @lot and ltrim (coh.co_num) = @order

I know this is very wrong but it should give an idea what I want. What do I need here?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-09 : 15:43:53

When the tables are not logically connected in any way then I can see no sense in that.

No, you're never too old to Yak'n'Roll if you're too young to die.

trackjunkie
Starting Member

31 Posts

Posted - 2009-09-09 : 15:54:06

I'm going to have the user enter these two parameters and then use the two results to populate a number of sub reports. It's sort of a strange application, I know...

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-09-09 : 16:44:01

Do you have some sample data and expected output you can provide to us?
[url]http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx[/url]

I don't understand how you want to get the data back. If lot.Item doesn't relate in any way to coh.cust_num, then how do they combine to form a row? Just a shot in the dark but maybe something like:

SELECT lot.item, NULL as cust_num
FROM lot 
where ltrim (lot.lot) = @lot 

UNION ALL

SELECT NULL, coh.cust_num 
FROM coh 
where ltrim (coh.co_num) = @order

Also, are the TRIMs needed?

trackjunkie
Starting Member

31 Posts

Posted - 2009-09-09 : 17:06:40

Each of the two values I want to return will be based on one parameter and one table. Either one would be simple on it's own, I'm just having trouble returning both at once.

My tables are like (the dashes are just to prevent spaces from being removed in this post)
lot:

lot------Item
2222-----8160
2597-----8170
2354-----8470

coh:

co_num----cust_num
5321------23
6598------45
5412------11

So if the input parameters were @order=5412 and @lot=2597 the desired output would be

item---cust_num
8170---11

The code provided by Lamprey got me the correct item number, but the cust_num was null

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-09-09 : 17:55:40

Is there only ever one result for each query? If so, try this:

SELECT
	(SELECT lot.item FROM lot where ltrim (lot.lot) = @lot) AS Item,
	(SELECT coh.cust_num FROM coh where ltrim (coh.co_num) = @order) AS cust_num

trackjunkie
Starting Member

31 Posts

Posted - 2009-09-10 : 08:41:55

That last code worked correctly Lamprey. Thank you. If you don't mind educating me a little bit (clearly I need it), what is the function of the "AS item" and "AS cust_num" at the end of the SELECT statements?

trackjunkie
Starting Member

31 Posts

Posted - 2009-09-10 : 08:51:34

Never mind, got it. Thanks again.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources