Solved

SQL where NOT DISTICNT

Posted on 2004-03-29
2
275 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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

713 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