Selecting items where they only exist once in a ta

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 items where they only exist once in a ta

Author

Topic

Wabby
Starting Member

27 Posts

Posted - 2009-07-09 : 06:20:13

Hi Guys,

I have two tables: Orders and OrderLines

I need to run a SQL statement to show me only ORDERS that have one orderline.

The order and orderlines are linked by orderid in orderlines.

So basically I want it to show me ALL orders where OrderID only exists against ONE line in OrderLines.

Any ideas?

Cheers

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-09 : 06:43:29

Try

select t1.Orderid from Orders as r1 inner join OrderLines as t2
on t1.Orderid =t2.Orderid
group by t1.Orderid
having count(t2.Orderid )=1

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-09 : 06:52:51

SELECT * FROM Orders WHERE TotalOrderLines = 1

The suggestion requires a persisted computed column named "TotalOrderLines", or a column named "TotalOrderLines" maintained by a trigger.

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

Wabby
Starting Member

27 Posts

Posted - 2009-07-09 : 08:45:53

What does the r1 part mean?

Wabby
Starting Member

27 Posts

Posted - 2009-07-09 : 08:51:41

Think I have sorted it.

How would I add more fields to the query (the group by doesnt like more fields for some reason?)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-09 : 09:02:51

quote:
Originally posted by Wabby

Think I have sorted it.

How would I add more fields to the query (the group by doesnt like more fields for some reason?)

select t1.* from Orders as r1 where Orderid =
(
select t2.Orderid from OrderLines as t2 where t1.Orderid =t2.Orderid
group by t2.Orderid having count(*)=1
)

Madhivanan

Failing to plan is Planning to fail

Wabby
Starting Member

27 Posts

Posted - 2009-07-09 : 09:03:10

Looks like I have to GROUPBY all the fields I want available. Will get typing then :D

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-09 : 09:07:35

quote:
Originally posted by Wabby

Looks like I have to GROUPBY all the fields I want available. Will get typing then :D

Did you see my other suggestion?

Madhivanan

Failing to plan is Planning to fail

Wabby
Starting Member

27 Posts

Posted - 2009-07-09 : 09:14:34

Cant get it too work :'(

Wabby
Starting Member

27 Posts

Posted - 2009-07-09 : 09:20:42

I don't get what the R1 stands for.

I know I have to substitute table names into the T1, T2 etc, but what about R1?

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-07-09 : 09:22:16

R1 is an alias, just like T1, T2 etc.

EDIT: Also, its a typo like Fred just said.

Hiya Fred!

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-09 : 09:22:17

It is typo.
Change R1 to T1.

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-09 : 09:28:45

Yes. It is Typo. Now try it

Madhivanan

Failing to plan is Planning to fail

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-09 : 09:30:36

Hiya Don!

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

Wabby
Starting Member

27 Posts

Posted - 2009-07-09 : 09:36:14

Incorrect Column Expressions 't1.*'

Wabby
Starting Member

27 Posts

Posted - 2009-07-09 : 09:39:47

Sorted - Left that opening part as t1. lol

Wabby
Starting Member

27 Posts

Posted - 2009-07-09 : 09:53:56

Its still including items where they have more than one ORDERID record within ORDERITEMS??

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-09 : 10:01:44

[code]
SELECT o.*
FROM Orders o
INNER JOIN (SELECT OrderID
FROM OrderLines
GROUP BY OrderID)ol
ON ol.OrderID=o.OrderID
[/code]

Wabby
Starting Member

27 Posts

Posted - 2009-07-09 : 10:05:50

What do I replace the o with? and the o1?

Cheers :)

Wabby
Starting Member

27 Posts

Posted - 2009-07-09 : 11:49:45

Doesn't work :'(

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-09 : 12:09:28

SELECT o.*, p.*
FROM Orders AS o
INNER JOIN (SELECT OrderID FROM OrderLines GROUP BY OrderID HAVING COUNT(*) = 1) AS x
ON x.OrderID = o.OrderID
INNER JOIN OrderLines AS p ON o.OrderID = o.OrderID

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

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources