need sql to creating a view/ temp table

[bcdixit]

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

thanks
bcd

 

[LKBrwnDBA]

This is a simple query, have you tried coding anything yet?




----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[bcdixit]

I am sorry for not being clear, but I need a view where I can see
orderids that have more than one
orderdetailids

sorry for the confusion.

 

[r937]

select orderid
     , orderdetailid
  from daTable
 where orderid 
    in ( select orderid
           from daTable
         group
             by orderid
         having count(*) > 1 )

r937.com | rudy.ca

 

[PHV]

Another way:
SELECT A.orderid, A.orderdetailid
FROM yourTable A INNER JOIN (
SELECT orderid FROM yourTable GROUP BY orderid HAVING COUNT(*)>1
) B ON A.orderid = B.orderid

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[bcdixit]

thanks for the solutions