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
FRM 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 * FRM SomeView
WHERE orderid IN (1234, 5421)
RDER BY orderid, orderdetailid

Denis the SQL Menace

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.*
FRM YourTable y join(
SELECT orderid
FRM YourTable
GRUP BY orderid
HAVING ****(orderdetailid) 1) x on y.orderid = x.orderid

or this way, a little faster

SELECT y.*
FRM YourTable y
where exists (select orderid from YourTable where orderid = y.orderid
GRUP BY orderid
HAVING ****(orderdetailid) 1)

here is the complete script to play around with

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

SELECT y.*
FRM YourTable y join(
SELECT orderid
FRM YourTable
GRUP BY orderid
HAVING ****(orderdetailid) 1) x on y.orderid = x.orderid

SELECT y.*
FRM YourTable y
where exists (select orderid from YourTable where orderid = y.orderid
GRUP BY orderid
HAVING ****(orderdetailid) 1)

Denis the SQL Menace

bhooshan.dixit@gmail.com wrote:
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.

If you just want the orderids that have more than one
orderdetailids:

select orderid, orderdetailid
from YourTable
GRUP BY orderid, orderdetailid
HAVING ****(*) 1

for all rows:

select distinct orderid, orderdetailid
from YourTable

/Lennart