Solved

SQL where NOT DISTICNT

Posted on 2004-03-29
2
273 Views
Last Modified: 2009-07-29
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???
0
Comment
Question by:timokeeffe
2 Comments
 
LVL 15

Expert Comment

by:SRigney
ID: 10705643
-- 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
 
LVL 3

Accepted Solution

by:
debi_mela earned 500 total points
ID: 10706053

--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

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question