select value based upon another value

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

Transact-SQL (2008)

select value based upon another value

Author

Topic

smh
Yak Posting Veteran

94 Posts

Posted - 2009-08-04 : 19:10:48

I have 2 select queries that I would like to combine but don't know how.

SELECT tblOrderDetail.OrderDetailID, tblProduct.ProductCode,
tblProductClass.ProductClassCode, tblProduct.StorageTableID
FROM tblOrderDetail
INNER JOIN
tblProduct ON tblOrderDetail.ProductID = tblProduct.ProductID
INNER JOIN
tblProductClass ON tblProduct.ProductClassID = tblProductClass.ProductClassID
WHERE OrderID = @OID
ORDER BY ProductCode

Here is the second and in this one that parameter @SID is the value of the tblProduct.StorageTableID in the first query.

select UsedForSale from tblStorageTables
where StorageTableID = @SID

I guess I could use a function in the first query passing it the storagetableID but I wonder if that would slow the query down. There are many other fields in the first query from the 3 tables that are joined but I have not included them because they do not relate to the problem but I wanted you to see that this first query has 2 joins.

Thanks
smhaig

Sachin.Nand

2937 Posts

Posted - 2009-08-05 : 09:51:06

[code]
May be this

SELECT * FROM
(SELECT tblOrderDetail.OrderDetailID, tblProduct.ProductCode,
tblProductClass.ProductClassCode, tblProduct.StorageTableID
FROM tblOrderDetail
INNER JOIN
tblProduct ON tblOrderDetail.ProductID = tblProduct.ProductID
INNER JOIN
tblProductClass ON tblProduct.ProductClassID = tblProductClass.ProductClassID
WHERE OrderID = @OID

)T1
INNER JOIN
(
SELECT UsedForSale,StorageTableID FROM tblStorageTables
WHERE StorageTableID = @SID
)T2
ON T1.StorageTableID=T2.StorageTableID
ORDER BY ProductCode
[/code]

PBUH

X002548
Not Just a Number

15586 Posts

Posted - 2009-08-05 : 13:27:02

Perhaps



	SELECT    d.OrderDetailID
			, p.ProductCode
			, c.ProductClassCode
			, p.StorageTableID
	  FROM	  tblOrderDetail	d
INNER JOIN	  tblProduct		p
		ON	  d.ProductID		= p.ProductID
INNER JOIN	  tblProductClass	c 
		ON	  p.ProductClassID	= c.ProductClassID
INNER JOIN	  tblStorageTables	s 
		ON	  p.StorageTableID	= s.StorageTableID
	 WHERE	  OrderID			= @OID
	   AND	  StorageTableID	= @SID
  ORDER BY	  ProductCode

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

smh
Yak Posting Veteran

94 Posts

Posted - 2009-08-05 : 22:11:38

Hi Idera and X002458:

Thanks for your help on this. In the part:

SELECT UsedForSale,StorageTableID FROM tblStorageTables
WHERE StorageTableID = @SID

the @sid would be the value, StorageTableID, which is the value of tblProduct.StorageTableID that is returned in the first part of the query.

So there is only one parameter here which is the @oid for the orderID.

The first query returns the storageTableId which is then plugged into the second query to return the UsedForSale field.

So I could use a function in the first query passing the tblproduct.storagetableID to the function and using the function to look up the UsedforSale in the TblStorageTables using the StoragetableID parameter, but I wondered if this could all be done in one query.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources