CODEWITHSUNDEEP
mysqlsqloracle11g
Maddy
Maddy

Reputation: 2060

SQL Condition to get "a" or "b" but not both

I am new to sql queries. I am writing a query in which I am suppose to return title

I have the following query so far and it works but it does not return me the correct result. If someone can guide me where I am going wrong.

SELECT movie_title, release_year 
FROM (
SELECT m.movie_title, m.release_year,COUNT(n.won), 
COUNT(c.actor_name) 
FROM MOVIE m JOIN ON (n.movie_title=m.movie_title AND n.release_year=m.release_year) 
JOIN on (c.movie_title=m.movie_title AND c.release_year=m.release_year) 
GROUP BY m.movie_title, m.release_year 
HAVING COUNT(n.won)>2 OR COUNT(c.actor_name)>2
);

I have looked at examples online but have not been able to get the exact result.

Thank you for all the help.

Upvotes: 1

Views: 5556

Answers (3)

Maksim Satsikau
Maksim Satsikau

Reputation: 1494

Another way of doing this is through union and another group by:

select * from (select movie_title, release_year from movie m
JOIN CAST_MEMBER c on (c.movie_title=m.movie_title AND c.release_year=m.release_year) 
group by m.movie_title, m.release_year
having count(*)>1) cm
union all
select * from (select movie_title, release_year from movie m
JOIN NOMINATION n on (n.movie_title=m.movie_title AND n.release_year=m.release_year) 
group by m.movie_title, m.release_year
having count(*)>1) nm
group by movie_title, release_year
having count(*) = 1

Upvotes: 1

Jason Baker
Jason Baker

Reputation: 2483

Most database systems have an XOR operator, which is exactly the behaviour you want.

From the MySQL documentation:

[XOR] returns NULL if either operand is NULL. For non-NULL operands, evaluates to 1 if an odd number of operands is nonzero, otherwise 0 is returned.

And from the Oracle 11g documentation:

XOR: Returns TRUE if either component condition is TRUE. Returns FALSE if both are FALSE. Otherwise returns UNKNOWN.

From a mathematical perspective, XOR stands for eXclusive OR; it literally means "One or the other, but not both". Interestingly, it's logically equivalent to NOT AND, so you could use that just as effectively.

Upvotes: 1

harvey
harvey

Reputation: 2953

Change your condition to;

HAVING ( COUNT(n.won) > 1 AND COUNT(c.actor_name) < 2 )
         OR ( COUNT(n.won) < 2 AND COUNT(c.actor_name) > 1 )

Or using the more terse XOR as mentioned by a previous poster;

HAVING COUNT(n.won) > 1 XOR COUNT(c.actor_name) > 1

Upvotes: 3

Related Questions