SELECT Id, Name, Lastname
FROM customers AS c, Places AS p,
WHERE c.customer_ID = p.customer_ID
My problem is that, i want to prevent the result of the query of showing a row that exist in another table(stages)
Asked
Active
Viewed 1.7k times
2
Makis Theotokis
- 27
- 1
- 1
- 8
-
sorry for the title, i wanted to say IF EXIST – Makis Theotokis Apr 23 '13 at 21:50
-
1Use either an `IF NOT EXISTS()` construct, or `LEFT JOIN that other table ON your on clause WHERE some non-nullable column in other table IS NULL`. – Wrikken Apr 23 '13 at 21:53
4 Answers
7
You can do a LEFT JOIN and check for null.
SELECT Id, Name, Lastname
FROM customers AS c LEFT JOIN Places AS p ON c.customer_ID = p.customer_ID
WHERE p.customer_ID IS NULL
Brent Baisley
- 12,641
- 2
- 26
- 39
1
add
and not exists
(subquery to select your exclusions)
to your query
Dan Bracuk
- 20,699
- 4
- 26
- 43
-
hey again!! sorry i'm a newbe . the result was the opposite, i want the prevent the result of the query of showing that row, if exists to the other table. – Makis Theotokis Apr 23 '13 at 22:07
0
You could use:
SELECT Id, Name, Lastname
FROM customers AS c JOIN Places AS p USING(customer_ID)
This is faster way that you could make with in/exists.
Jack
- 1,892
- 1
- 19
- 23
0
I suppose you want something like this:
SELECT Id, Name, Lastname
FROM customers,
WHERE customer_ID NOT IN (SELECT customer_ID FROM Places)
Lucia Pasarin
- 2,268
- 1
- 21
- 37
-
1
-
You are right. Thinking about it again, it is clear NOT EXISTS must be faster. It can stop when one record is found, whereas mine should check all the records in the NOT IN (...) part. – Lucia Pasarin Apr 23 '13 at 22:00