SQL where NOT DISTICNT

I have a shipping table with order number called FDDOCO. I need to select only orders which have 2 or more FDDOCO. what this mean is that each order is usually represented once in shipping, but sometimes orders are split up and shipped in 2 or 3 seperate shipments with the same FDDOCO num. any ideas???
timokeeffeAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
debi_melaConnect With a Mentor Commented:

--to list all orders with orderno = FDDOCO, and occurs more than once..

select * from tblshipping where orderno in
(SELECT distinct orderno from tblShipping where orderno = 'FDDOCO'
group by orderno  having count(orderno) > 1
)


-- In general for any orderno..

select * from tblshipping where orderno in
(SELECT distinct orderno from tblShipping
group by orderno  having count(orderno) > 1
)
 
 
0
 
SRigneyCommented:
-- select the distinct FDDOCO numbers that are in tblShipping more than once.
SELECT FDDOCO from tblShipping
group by FDDOCO
having count(FDDOCO) > 1

If you then want to select all orders that have an FDDOCO number that is duplicated then you would use a subselect
select * from tblShipping
inner join
(
SELECT FDDOCO from tblShipping
group by FDDOCO
having count(FDDOCO) > 1
) as tmp
on tblShipping.FDDOCO = tmp.FDDOCO
0
All Courses

From novice to tech pro — start learning today.