creating temp table/ view

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