CODEWITHSUNDEEP
sql
TheBosnianGuy
TheBosnianGuy

Reputation: 5

SQL query complication

Hi i'm trying to apply this query to find any order duplicates in my database.

Select order_info.id_num, order_info.order_id, order_info.cargo, order_info.weight, order_info.companyid_sending, order_info.companyid_receiving, order_info.from_city, order_info.destination_city, order_info.miles, order_info.time, order_info.fuel, order_info.damage, order_info.travel_cost, order_info.payment, order_info.first_name, order_info.date, order_info.truck_id_num
From order_info
Group By order_info.id_num
Having Count(order_info.id_num) > 1

The problem is that this query only shows one of the lines of the duplicate and not all of them. So it shows only this:

e1005|o1001|tractors|13|c1003|c1002|Graz|Dijon|640|857|327|0|712|24229|ben|03/03/2015|t1001

Rather than the three instances it is.

e1005|o1001|tractors|13|c1003|c1002|Graz|Dijon|640|857|327|0|712|24229|ben|03/03/2015|t1001
e1005|o1001|tractors|13|c1003|c1002|Graz|Dijon|640|857|327|0|712|24229|ben|03/03/2015|t1001
e1005|o1001|tractors|13|c1003|c1002|Graz|Dijon|640|857|327|0|712|24229|ben|03/03/2015|t1001

How do I change my existing SQL to give me the result as above rather than a single line?

Upvotes: 0

Views: 62

Answers (2)

John
John

Reputation: 1004

SELECT order_info.id_num
    ,order_info.order_id
    ,order_info.cargo
    ,order_info.weight
    ,order_info.companyid_sending
    ,order_info.companyid_receiving
    ,order_info.from_city
    ,order_info.destination_city
    ,order_info.miles
    ,order_info.TIME
    ,order_info.fuel
    ,order_info.damage
    ,order_info.travel_cost
    ,order_info.payment
    ,order_info.first_name
    ,order_info.DATE
    ,order_info.truck_id_num
FROM order_info
INNER JOIN (
    SELECT order_info.id_num
    FROM order_info
    GROUP BY order_info.id_num
    HAVING Count(order_info.id_num) > 1
    ) order_info2 ON order_info.id_num = order_info2.id_num

Upvotes: 0

D Stanley
D Stanley

Reputation: 152596

You can use your duplicate search as a subquery and return all items that match the id_num:

Select order_info.id_num, order_info.order_id, order_info.cargo, order_info.weight, order_info.companyid_sending, order_info.companyid_receiving, order_info.from_city, order_info.destination_city, order_info.miles, order_info.time, order_info.fuel, order_info.damage, order_info.travel_cost, order_info.payment, order_info.first_name, order_info.date, order_info.truck_id_num
From order_info
where id_num IN
(
    Select order_info.id_num
    From order_info
    Group By order_info.id_num
    Having Count(order_info.id_num) > 1
)

Upvotes: 2

Related Questions