MySQL select all rows but exclude same value

user_4578250 0
Viewed 236

All I have table in MySQL as bellow.

+---------+-------------+---------------+
| item_no | item_name   | item_location |
+---------+-------------+---------------+
| ITM145  |  laptop     | USA           |
| ITM146  |  camera     | USA           |
| ITM147  |  cd         | USA           |
| ITM148  |  phone      | USA           |
| ITM149  |  cd         | France        |
| ITM150  |  phone      | France        |
+---------+-------------+---------------+

The last tow items location is France, and the same items I have in USA, I want the query to return me as bellow exclude same value for location France.

+---------+-------------+---------------+
| item_no | item_name   | item_location |
+---------+-------------+---------------+
| ITM145  |  laptop     | USA           |
| ITM146  |  camera     | USA           |
+---------+-------------+---------------+

First try

SELECT * FROM table
WHERE item_location !='France'
GROUP BY item_name

FAIL second try

SELECT * FROM table
WHERE item_location NOT IN('France')
GROUP BY item_name
mysql sql
4 Answers

Try this instead using the <> operator (This is to omit 'France')

SELECT * FROM table
WHERE item_location <> 'France'
GROUP BY item_name

But if you want to get your results

SELECT * FROM table
WHERE item_location <> 'France' AND item_name IN ('laptop','camera')
GROUP BY item_name

To get the results that exclude item_name in France, you can try this

SELECT * FROM table
WHERE item_location <> 'France' AND item_name NOT IN (Select item_name FROM 
table where item_location = 'France')
GROUP BY item_name
user_74559820

Try this:

SELECT * FROM table
GROUP BY item_name
Having count(*) = 1
user_102529070

you want to get " laptop " and camera only there use this SELECT * FROM country WHERE item_location ="usa" AND (item_name = "laptop" OR item_name = "camera") GROUP BY item_no if you want to selec

user_59909480

Use LEFT JOIN with self table like this:

SELECT t.* FROM Table1 t
LEFT JOIN
(
  SELECT item_name FROM Table1 WHERE item_location = 'France'
) t2
ON t.item_name = t2.item_name
WHERE t2.item_name IS NULL

Output:

| item_no | item_name | item_location |
|---------|-----------|---------------|
|  ITM145 |    laptop |           USA |
|  ITM146 |    camera |           USA |

See this SQLFiddle.

To understand joins see A Visual Explanation of SQL Joins.

user_13692350
Related