Dont know if this is a good place to ask this.

I have a table that looks like this.

orderid | orderdetailid | desc
0000 1 razr
0001 1 razr
0002 1 v3
0003 1 nokia
0004 1 motorola
...
...
...
1234 1 nokia
1234 2 razr
1234 3 samsung
....
....
5421 1 samsung
5421 2 nokia

I want create a temp table or view to look something like this

orderid | orderdetailid
1234 1
1234 2
1234 3
5421 1
5421 2

Can someone suggest me the SQL?

CREATE VIEW SomeView AS
SELECT orderid, orderdetailid
FROM YourTable

The view will just return the orderid and orderdetailid
IF you just want 1234 and 5421 then do add WHERE orderid IN (1234,
5421) when selecting from the view like this

SELECT * FROM SomeView
WHERE orderid IN (1234, 5421)
ORDER BY orderid, orderdetailid

Denis the SQL Menace
http://sqlservercode.blogspot.com/

thanks but I need someting more generic,
I mean the view should show me orderids that have more than one
orderdetailids

sorry for the confusion.

way one

SELECT y.*
FROM YourTable y join(
SELECT orderid
FROM YourTable
GROUP BY orderid
HAVING COUNT(orderdetailid) > 1) x on y.orderid = x.orderid


or this way, a little faster

SELECT y.*
FROM YourTable y
where exists (select orderid from YourTable where orderid = y.orderid
GROUP BY orderid
HAVING COUNT(orderdetailid) > 1)

here is the complete script to play around with

CREATE Table YourTable (orderid int, orderdetailid int)
INSERT INTO YourTable values(0,1)
INSERT INTO YourTable values(1,1)
INSERT INTO YourTable values(2,1)
INSERT INTO YourTable values(3,1)
INSERT INTO YourTable values(4,1)
INSERT INTO YourTable values(1234,1)
INSERT INTO YourTable values(1234,2)
INSERT INTO YourTable values(1234,3)
INSERT INTO YourTable values(5421,1)
INSERT INTO YourTable values(5421,2)



SELECT y.*
FROM YourTable y join(
SELECT orderid
FROM YourTable
GROUP BY orderid
HAVING COUNT(orderdetailid) > 1) x on y.orderid = x.orderid


--or

SELECT y.*
FROM YourTable y
where exists (select orderid from YourTable where orderid = y.orderid
GROUP BY orderid
HAVING COUNT(orderdetailid) > 1)

Denis the SQL Menace
http://sqlservercode.blogspot.com/

bhooshan.dixit (AT) gmail (DOT) com wrote:
If you just want the orderids that have more than one
orderdetailids:

select orderid, orderdetailid
from YourTable
GROUP BY orderid, orderdetailid
HAVING COUNT(*) > 1

for all rows:

select distinct orderid, orderdetailid
from YourTable


/Lennart