Selecting records based upon one matching record? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 records based upon one matching record?

Author

Topic

wabby11
Starting Member

2 Posts

Posted - 2011-05-27 : 09:43:39

Hi,

I have two tables, one called Order, one called OrderItems.

One order can contain many OrderItems, by means of the OrderItems table having multiple Order.OrderID's.

I need to perform a query that returns ALL Orders that have more than 1 OrderItem, but then I need to be able to specify that ONE of those OrderItems has to be a specific item.

So for example, I want to show all invoices where a pencil has been sold with any other item. I need to specify the pencil part.

Any ideas on how I can acheive this?

Regards

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-05-27 : 09:57:40

Sounds suspiciously like homework but

select *
from Order
where OrderID in (select OrderID from OrderItem group by OrderID having count(*) > 1)
and OrderID in (select OrderID from OrderItem where ProductName = 'Pencil')

or maybe

select *
from Order o
where exists (select * from OrderItem oi where oi.OrderID = o.OrderID and oi.ProductName = 'Pencil')
and exists (select * from OrderItem oi where oi.OrderID = o.OrderID and oi.ProductName <> 'Pencil')

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

wabby11
Starting Member

2 Posts

Posted - 2011-05-27 : 10:03:11

Not homework - trying to get some data for a pivot table and currently running two reports and comparing - wanted to streamline the information :)

Will try them and see how I get on.

Cheers :)

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-05-29 : 18:30:15

Please post real DDL. Learn to use ISO-11179 rules for the data element names, avoid needless dialect and use ISO-8601 temporal formats, codes and so forth. People cannot read your mind, so post your code and clear specs if you really want help.

CREATE TABLE Orders
(order_nbr CHAR(15) NOT NULL PRIMARY KEY,
);

CREATE TABLE OrderDetails
(order_nbr CHAR(15) NOT NULL
REFERENCES Orders(order_nbr)
sku CHAR(10) NOT NULL, -- or upc, ean or other industry standards, please
PRIMARY KEY (order_nbr, sku),
order_qty INTEGER NOT NULL,
..);

>> I need to perform a query that returns ALL Orders that have more than 1 OrderItem (sku), but then I need to be able to specify that ONE of those OrderItems (sku) has to be a specific item.<<

SELECT order_nbr
FROM (SELECT order_nbr, COUNT(sku) OVER(PARTITION BY order_nbr)
FROM OrderDetails AS D
WHERE sku = @in_sku)
AS X(order_nbr, order_size)
WHERE order_size > 1;

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources