Two simple SQL tasks, that i can't figure

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

Two simple SQL tasks, that i can't figure

Author

Topic

ande
Starting Member

1 Post

Posted - 2009-10-29 : 06:23:25

Hi!

Im completely new to SQL and fairly new to PHP too. I've been working on a small database for a school-project and there are two things i'd like to get out of this database using SQL.

1) Which wares have been ordered at least two times of the same customer.

2) Establish a list with all employees and customers that has the same e-mail.

The tables i have in the DB is: Employee, Customer, Wares and Order. The columns in each tables is yet to be set, so if u have a solution feel free to call em whatever u want.

Best Regards
Anders Levin

Sachin.Nand

2937 Posts

Posted - 2009-10-29 : 09:01:08

DECLARE @wares AS TABLE(waresid INT ,custid INT)
INSERT INTO @wares
SELECT 1,1 UNION ALL
SELECT 2,1 UNION ALL
SELECT 3,1 UNION ALL
SELECT 3,2 UNION ALL
SELECT 4,3

DECLARE @customers AS TABLE(custid INT,name VARCHAR(10),email VARCHAR(10))
INSERT INTO @customers
SELECT 1,'a','a@a.com'UNION ALL
SELECT 2,'b','b@b.com'UNION ALL
SELECT 3,'c','a@a.com'

SELECT c.custid,c.name FROM @wares w
INNER JOIN @customers c ON w.custid=c.custid
GROUP BY c.custid,c.name
HAVING COUNT(waresid)>2

SELECT custid,email FROM
(
SELECT custid,email,ROW_NUMBER()OVER(PARTITION BY email ORDER BY custid DESC)AS rowid1,
ROW_NUMBER() OVER(PARTITION BY email ORDER BY custid)AS rowid2
FROM @customers c

)t WHERE rowid1-rowid2<>0

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources