query - 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

General SQL Server Forums

New to SQL Server Programming

query

Author

Topic

kwacz23
Starting Member

44 Posts

Posted - 2012-12-07 : 01:22:17

Hello !!

Please advise me in that issue. Please find below table

Order_Number Order_line Vendor
1 1 A
1 1.1 A
1 1.2 A
1 2 B
1 3 C
2 1 A
2 1.1 A
3 1 A
3 1.1 A
3 2 C

Order line means (for exapmle1 is a laptop and 1.1 is a keyboard from Vendor A)

How I can retrive data for whole order when vendor is for example only C.
When I use:
Select * from table
where vendor=C

I only got 2 records like:

1 3 C
3 2 C

but I need :
1 1 A
1 1.1 A
1 1.2 A
1 2 B
1 3 C
3 1 A
3 1.1 A
3 2 C

I hope it makes sense.

Thanks!

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-12-07 : 01:54:48

DECLARE @Orders TABLE(Order_Number int, Order_line varchar(6), Vendor char(1))
INSERT INTO @Orders
SELECT 1, '1', 'A' union all
SELECT 1, '1.1', 'A' union all
SELECT 1, '1.2', 'A' union all
SELECT 1, '2', 'B' union all
SELECT 1, '3', 'C' union all
SELECT 2, '1', 'A' union all
SELECT 2, '1.1', 'A' union all
SELECT 3, '1', 'A' union all
SELECT 3, '1.1', 'A' union all
SELECT 3, '2', 'C'

SELECT *
FROM @Orders
WHERE Order_Number IN (SELECT Order_Number FROM @Orders WHERE Vendor = 'C')

--
Chandu

kwacz23
Starting Member

44 Posts

Posted - 2012-12-07 : 06:03:13

Thank you for your response !!!

Is it posible to display only order where is only one vendor
I mean in the order you have only things from one order.

I my table there aren't these example. So query shouldn't give any result

Regards

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-12-07 : 06:09:32

quote:
Originally posted by kwacz23

Thank you for your response !!!

Is it posible to display only order where is only one vendor
I mean in the order you have only things from one order.

I my table there aren't these example. So query shouldn't give any result

Regards

Can you post sample data which is in ur table and expected output?

--
Chandu

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-12-07 : 06:36:49

See this:
Here Vendor 'D' having only one Order_number 2. So assuming that you want data related to vendor 'D'..


DECLARE @Orders TABLE(Order_Number int, Order_line varchar(6), Vendor char(1))
INSERT INTO @Orders
SELECT 1, '1', 'A' union all
SELECT 1, '1.1', 'A' union all
SELECT 1, '1.2', 'A' union all
SELECT 1, '2', 'B' union all
SELECT 1, '3', 'C' union all
SELECT 2, '1', 'D' union all
SELECT 2, '1.1', 'D' union all
SELECT 3, '1', 'A' union all
SELECT 3, '1.1', 'A' union all
SELECT 3, '2', 'C'

SELECT * FROM @Orders 
WHERE Order_Number IN (SELECT Order_Number 
			FROM @Orders 
			GROUP BY Order_Number
			HAVING COUNT(distinct vendor) = 1

)

--
Chandu

kwacz23
Starting Member

44 Posts

Posted - 2012-12-08 : 12:27:45

Thanks a lot !

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-12-10 : 00:14:23

quote:
Originally posted by kwacz23

Thanks a lot !

Welcome

--
Chandu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-14 : 02:17:21

[code]
SELECT *
FROM
(
SELECT *,
MAX(Vendor) OVER (PARTITION BY Order_number) AS MaxVendor,
,MIN(Vendor) OVER (PARTITION BY Order_number) AS MinVendor
FROM TAble
)t
Where MaxVendor = MinVendor
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources