CODEWITHSUNDEEP
mysqlsql
somejkuser
somejkuser

Reputation: 9040

SQL Multiple AND Conditions

Here is my query:

SELECT * FROM addresses 
WHERE (addresses.name LIKE '%Jonathan%' OR addresses.domain LIKE '%Jonathan%') 
AND (addresses.name LIKE '%gmail' OR addresses.domain LIKE '%gmail%')

With the AND statement being used against the WHERE clause, lets say if the portion after the AND clause doesn't hit anything, will the results be null because its being added as an AND statement? I'm trying to grab, in this example, everyone with the addresses.name as Jonathan and everyone who is Jonathan has the addresses.domain as gmail. However, if lets say in this example gmail does not exist for anyone named Jonathan, I want all of the Jonathan results (which is why I think using the AND statement wont work) I might not be wording this correctly.

Thanks

Upvotes: 1

Views: 109

Answers (4)

sgeddes
sgeddes

Reputation: 62861

If either the first set or second set of conditions do not meet your WHERE criteria, then using AND, the query won't return any results.

Basically, 

WHERE (true) and (true) -- returns True
WHERE (true) and (false) -- returns False
WHERE (false) and (false) -- returns False

Given your edits, if I'm understanding correctly, one option would be to join the table back on itself counting the number of emails like gmail. Something like this:

select *
from addresses a
  inner join (
    select name, count(case when domain like '%gmail%' then 1 end) cnt
    from addresses 
    where name like '%Jonathan%'
) a2 on a.name = a2.name
where a.domain like '%gmail%' or a2.cnt = 0
;

This way you check if anyone is named Jonathan and the count with domain like gmail. If the count of the domain is 0 (meaning nobody like gmail), then all will return, else, only those with the domain like gmail.

Upvotes: 1

isJustMe
isJustMe

Reputation: 5470

You are using the same comparisons for different fields which can lead to errors in logic, take a look at this example 

CREATE TABLE addresses  
    (
     id int auto_increment primary key, 
     name  varchar(20), 
     domain  varchar(30)
    );

INSERT INTO addresses
(name, domain)
VALUES
('Jonathan', '[email protected]'),
('Jonathan', '[email protected]'),
('Jonathan', '[email protected]'),
('Jonathan', '[email protected]'),
('Xavier', '[email protected]')
;

Using your query it would return:

1   Jonathan    [email protected]
2   Jonathan    [email protected]

The AND operator specifies that both conditions have to be met.

Look at this demo, It will help you to understand how the AND operator works

Upvotes: 1

Brandon
Brandon

Reputation: 10058

I think what you want is OR.

You are confusing UNION with AND. UNION means combine the results of one query with the results of another query. In English, "find me A and find me B".

But the term AND in SQL means row-by-row condition matching. Find me rows that match this condition and match this other condition.

If you want a query to find rows for Jonathan and rows for gmail.com, just use an OR:

SELECT * FROM addresses 
WHERE addresses.name LIKE '%Jonathan%' OR addresses.domain LIKE '%Jonathan%'
   OR addresses.name LIKE '%gmail' OR addresses.domain LIKE '%gmail%'

Upvotes: 0

Gimmy
Gimmy

Reputation: 3911

I think this is the layout used in MySQL:

SELECT * FROM addresses 
WHERE (addresses.name LIKE '%Jonathan%' || addresses.domain LIKE '%Jonathan%') && 
(addresses.name LIKE '%gmail%' || addresses.domain LIKE '%gmail%')

Upvotes: 0

Related Questions