Solved

SQL where NOT DISTICNT

Posted on 2004-03-29
2
279 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Major Incident Management Communications

Major incidents and IT service outages cost companies millions. Often the solution to minimizing damage is automated communication. Find out more in our Major Incident Management Communications infographic.

Question has a verified solution.

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

Suggested Solutions

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how the fundamental information of how to create a table.

739 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