SELECT question... 2

[denoxis]

Hi,

I have a table that keeps which item from which order has been dropshipped by who. Something like this:

CREATE TABLE [dbo].[new_tbl_drop_ships] (
[drop_ship_id] [int] IDENTITY (1, 1) NOT NULL ,
[vendor_id] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[order_id] [int] NOT NULL ,
[product_sku] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[qty] [smallint] NOT NULL ,
[to_here] [bit] NULL
) ON [PRIMARY]
GO


I need to find the orders that have been dropshipped by more than one dropshipper. Here is an example:

vendor_id order_id product_sku qty
-------------------------------------
vendor1 1200 product1 1
vendor1 1200 product2 1
vendor1 2300 product3 1
vendor2 2300 product5 1
vendor2 3400 product1 2
vendor2 3400 product1 2

When I run the query on this table, it should return 2300, which is dropshipped by more than one vendor.

Any help would be appreciated.

 

[swampBoogie]

select order_id from new_tbl_drop_ships
group by order_id
having count(distinct vendor_id) > 1

 

[SQLSister]

hopefully this will help:

Select order_id,count(vendor_id) from new_tbl_drop_ships group by order_id having count(distinct(vendor_id))>1

 

[denoxis]

Thank you both! I didn't think about using DISTINCT with COUNT. Now makes sense