CODEWITHSUNDEEP
mysqlsql
Allegra
Allegra

Reputation: 11

SQL Return All Rows Where 2 Values Are Repeated

I am sure this question has already been answered, but I can't find it or the answer was too complicated. I am new to SQL and am not sure how to word this generically.

I have a mySQL database of software installed on devices. My query to pull all the data has more fields and more joins, but for brevity I just included a few. I need to add another dimension to create a report that lists every case where a device has more than one installation of software from the same product family.

sample

Right now I have code kind of like this and it is not doing what I need. I have seen some info on exists but the examples didn't account for multiple joins so the syntax escapes me. Help?

select
devices.name,
sw_inventory.product, 
products.family_name,
sw_inventory.ignore_usage,

from sw_inventory

inner join products
on sw_inventory.product=products.product_name

inner join devices
on sw_inventory.device_name=devices.name

where sw_inventory.ignore=0

group by devices.name, products.family_name

Upvotes: 0

Views: 92

Answers (1)

Matt
Matt

Reputation: 14341

There are plenty of answers out there on this topic but I definitely understand not always knowing terminology. you are looking for how to find duplicates values.

Basically this is a two step process. 1 find the duplicates 2 relate that back to the original records if you want those. Note the second part is optional.

So to literally find all of the duplicates of the query you provided

ADD HAVING COUNT(*) > 1 after group by statements. If you want to know how many duplicates add a calculated column to count them.

select
devices.name,
sw_inventory.product, 
products.family_name,
sw_inventory.ignore_usage,
NumberOfDuplicates = COUNT(*)
from sw_inventory

inner join products
on sw_inventory.product=products.product_name

inner join devices
on sw_inventory.device_name=devices.name

where sw_inventory.ignore=0

group by devices.name, products.family_name

HAVING COUNT(*) > 1

Upvotes: 1

Related Questions