Get single lines in the database, mysql

Navigation
2

It is necessary to get rows that do not have duplicates in a table, I did searches and found the DISTINCT function, but this does not solve the problem, because it somehow groups the data.

One way to solve this would be to use PHP and check one by one, but before returning to this option, I want to make sure I do not have any other ones that are more efficient.

An example: 

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

The return would have to be 4, 8 because they are unique

    
asked by anonymous 22.09.2017 / 17:11

1 answer

3

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

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

If you want the other columns this can be done 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, since the innermost query guarantees that we will only have one row for each pet, we can alternatively delete the subquery and use an aggregate function that returns the result (eg, MIN or MAX ): 

SELECT
  MIN(customer) AS customer,
  pets
FROM customer_pet
GROUP BY pets
HAVING COUNT(*) = 1
    
22.09.2017 / 17:54
Function inserts does not work Doubt with Java code snippet EL [closed]