Selecting information (Quiet technical, I think)

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

General SQL Server Forums

New to SQL Server Programming

Selecting information (Quiet technical, I think)

Author

Topic

Wabby
Starting Member

27 Posts

Posted - 2009-01-28 : 09:14:14

Hi Guys,

I have the following tables:

Order
OrderItem

Both tables are linked via ID fields.

I wish to display ALL Orders where on OrderItem line = a certain code, AND there are other itemlines on the ORDER.

Any ideas where to start?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-28 : 09:20:30

[code]
SELECT o1.columns....
FROM Orders o1
JOIN(
SELECT o.ID
FROm Orders o
INNER JOIN OrderItem oi
ON oi.OrderID=o.ID
WHERE oi.Codefield=@YourCode
GROUP BY o.ID
HAVING COUNT(oi.ID)>1
)t
ON t.ID=o1.ID
[/code]

Wabby
Starting Member

27 Posts

Posted - 2009-01-28 : 09:48:43

Best way to get this information out of a MSSQL 200 database?

MSQUERY? Crystal? (Reporting facilities I have for the program are not that great)

:)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-28 : 09:53:05

T-SQL query ofcourse..and use it as source for your report.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-28 : 10:11:58

Visakh's suggestion will not work.
It will only show you the orders having more than one matching lineitem by code.

Try this

SELECT		OrderID
FROM		OrderItem
GROUP BY	OrderID
HAVING		MAX(CASE WHEN LineCode = @MyCode THEN 1 ELSE 0 END) = 1
		AND MIN(CASE WHEN LineCode = @MyCode THEN 1 ELSE 0 END) = 0

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-28 : 12:21:45

yup. peso's right. i misinterpreted it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources