Finding Reoccuring Values

[Commandolomo]

Hello All

Is it possible within an SQL statement to only bring back those records which have multiple instances of a value?

For example, the code below returns all orders as per the conditions :-

select (vc.Title + ' ' + vc.Fname + ' ' + vc.Sname) as clientname,
(vl.house + ' ' + vl.street + ' ' + vl.town + ' ' + vl.county + ' ' + vl.postcode) as client_address, (mo.status) as Service_Status, (vc.Sname + vc.Fname + mo.our_ref ) as sortkey, mo.report_to as DSO, mo.our_ref as Swift_ID, mo.order_id as Order_ID,
mo.client_id as MO_Client_ID, vc.client_id as VC_Client_ID
from vclient vc, vlocation vl, main_order mo, client_extra ce
where vc.location_id = vl.location_id
and
mo.client_id = vc.client_id
and
ce.client_id = vc.client_id
and
mo.status = '1'

- but I only want to bring back those records where there is multiple instances of the same Order ID - MO.Client_ID.

I have had to hit the ground running with regards to my SQL usage, so I apologise for my ignorance!!

If anyone has any pointers I would be most grateful.

Cheers


PJL

 

[PHV]

You may try something like this:
SELECT (vc.Title + ' ' + vc.Fname + ' ' + vc.Sname) AS clientname
, (vl.house + ' ' + vl.street + ' ' + vl.town + ' ' + vl.county + ' ' + vl.postcode) AS client_address
, (mo.status) AS Service_Status, (vc.Sname + vc.Fname + mo.our_ref) AS sortkey
, mo.report_to AS DSO, mo.our_ref AS Swift_ID, mo.order_id AS Order_ID
, mo.client_id AS MO_Client_ID, vc.client_id AS VC_Client_ID
FROM (vclient vc
INNER JOIN vlocation vl ON vc.location_id = vl.location_id)
INNER JOIN main_order mo ON vc.client_id = mo.client_id
WHERE mo.status = '1'
AND (mo.client_id + ',' + mo.order_id) In (
SELECT client_id + ',' + order_id FROM main_order
GROUP BY client_id, order_id HAVING Count(*) > 1)

You may want to add WHERE status = '1' in the subquery


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Commandolomo]

PHV

Many thanks for your reply, yet I am not getting the results I expect! The query is returning no records, yet I have previously exported the data into excel, and via some manual calculations, I know there should be about 20 records where the same mo.Client_ID appears more than once.

As per your suggestion, I also added the Where status = 1 in the sub query, but to no avail.

Any further ideas are most welcome!!

Regards


PJL

 

[PHV]

How many records in client_extra ce for this 'duplicates' ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Commandolomo]

The dataset is about 4k records, but those instances where there are more than 1 instance of the client_id per record should only be about 1 or 2 - that should only be around 2instances of a duplicated client_id.

Thanks for your time

PJL

 

[PHV]

In another words, remove the client_extra table from the FROM clause of your original query and see if you still get duplicates.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[PHV]

To be sure, what is the resultset of the following query ?
SELECT client_id, order_id, Count(*) AS Nb
FROM main_order
GROUP BY client_id, order_id
HAVING Count(*) > 1

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Commandolomo]

I have ran the above query, and it has returned no rows/records. I have manually doubled checked, and there are definitly separate records that have the same client_id - thus dupes.

This continues to confound me!

Many thanks for your continued time and assistance PHV

 

[PHV]

separate records that have the same client_id
and same order_id ? Returned by a query based on solely main_order ?


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Commandolomo]

I think I may have clouded what I want to achieve - I apologise.

I want to run an SQL script to return just those records from the dataset where the Client_ID appears more than once. In the example below..........

Client_id Reference Number Name Order_ID
1 79741 Smith 99
2 51318 Jones 66
2 68541 Jones 33
3 65623 Brown 44
4 218486 Yates 11
5 878646 Parkinson 22
5 51648 Parkinson 77
6 73635 Bains 88


....I would expect the SQL to return records for Client ID's 2 and 5, since these are the only records in which the Client_ID appears more than once.

Does this make sense?!?

 

[PHV]

SELECT (vc.Title + ' ' + vc.Fname + ' ' + vc.Sname) AS clientname
, (vl.house + ' ' + vl.street + ' ' + vl.town + ' ' + vl.county + ' ' + vl.postcode) AS client_address
, (mo.status) AS Service_Status, (vc.Sname + vc.Fname + mo.our_ref) AS sortkey
, mo.report_to AS DSO, mo.our_ref AS Swift_ID, mo.order_id AS Order_ID
, mo.client_id AS MO_Client_ID, vc.client_id AS VC_Client_ID
FROM (vclient vc
INNER JOIN vlocation vl ON vc.location_id = vl.location_id)
INNER JOIN main_order mo ON vc.client_id = mo.client_id
WHERE mo.status = '1'
AND mo.client_id In (
SELECT client_id FROM main_order
GROUP BY client_id HAVING Count(*) > 1)


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Commandolomo]

Bingo!!! This works a treat, thank you so much for your time and effort PHV

Regards


PJL