tSQL help

[Bonegavel]

Table: order
fields: vendor, orderstatus, location

I want to select only records where:

orderstatus = 2 and the vendor and locations are identical.

example table (obviously not my actual table)


# | vendor | location | orderstatus
------------------------------------
1 | ABC | townA | 2
2 | 123 | townB | 2
3 | ABC | townA | 1
4 | ABC | townA | 2
5 | 123 | townB | 2


the query i'm trying to build will only return 1,2,4,5 (with the pairs being 1 and 4 and 2 and 5).

I'm guessing I need to do a join of some sort and would appreciate any help even if that help is a site.

I'm using MS Sql server 2000 (which is tSql just for clarification).

[sachmo71]

the data in the vendor and location columns are identical? how about a group by statement?

Do you have a list of the vendors you want?

so
select * from order where orderstatus = 2 and vendors in (n,n,n,etc)

[VPI97]

Try this:

select [order].vendor, [order].location, [order].orderstatus
from [order],
(select vendor, location, orderstatus, count(*) as cnt
from [order]
where (orderstatus = 2)
group by vendor, location, orderstatus
having count(*) > 1) AS dups
where [order].vendor = dups.vendor and [order].location = dups.location and [order].orderstatus = 2

[Raven Hawk]

Seems like relatively straight forward 'Where' and 'Group By' clauses unless I'm missing something. I'm not too familiar with tSQL, much more PL SQL, but this what I'd be thinking:

WHERE order.status = 2
GROUP BY order.vendor, order.location, order.status

[Bonegavel]

Thanks everyone for the assistance!

VPI97's example worked perfectly (with slight tweaking to take other things into consideration that i didn't bog this post down with).

Raven Hawk, I think your example would still show me everyone with a status of 2 regardless if they weren't duplicate.