Get single lines in the database, mysql

Asked

Viewed 75 times

2

It is necessary to obtain lines that are not have duplicates in a table, I did some research and found the function DISTINCT but this does not solve the problem, because it somehow groups the data.

One way to solve it would be by using PHP and checking one by one, but before falling back to this option I want to make sure I don’t have more efficient ones.

An example:

+----------+------+
| Customer | Pets |
+----------+------+
|       20 |    2 |
|       21 |    3 |
|       22 |    3 |
|       23 |    2 |
|       24 |    4 |
|       25 |    8 |
+----------+------+

the return would have to be 4, 8 for they are unique

mysql

1 answer

3


You want all pets with a single entry in your table. A literal translation of this into SQL assuming that your table name is CUSTOMER_PET would be:

SELECT pets FROM customer_pet GROUP BY pets HAVING COUNT(*) = 1

If you want the other columns you can do it with a subquery:

SELECT
  customer,
  pets
FROM customer_pet
WHERE pets IN (
  SELECT pets
  FROM customer_pet
  GROUP BY pets
  HAVING COUNT(*) = 1
)

In fact, as the innermost query ensures that we will only have one line per pet alternatively we can eliminate the subquery and use an aggregator function that returns the result (e. g., MIN or MAX):

SELECT
  MIN(customer) AS customer,
  pets
FROM customer_pet
GROUP BY pets
HAVING COUNT(*) = 1

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.