CODEWITHSUNDEEP
mysqlsql
Syl
Syl

Reputation: 3829

The reverse of a query in MYSQL

I have the following two table : A and B

B contain a foreign key to A:a_id.

I have the following query :

SELECT a.* FROM a INNER JOIN b ON b.a_id = a.id WHERE b.stuff=1 AND (QueryCheckingDateWithBFields)

I want to reverse the query so i did that :

SELECT a.* FROM a INNER JOIN b ON b.a_id = a.id WHERE b.stuff!=1 OR (!QueryCheckingDateWithBFields)

It works fine except for one problem. I'd like that my first query + my second query = all A table entry. But if there is no B with a a_ID of an A, this A will not be selected. How can i say "i also want the A that are not joined?"

Upvotes: 1

Views: 149

Answers (2)

Jeremy
Jeremy

Reputation: 1521

Use LEFT JOIN instead of INNER JOIN

Upvotes: 2

Daniel Hilgarth
Daniel Hilgarth

Reputation: 174309

Use a left outer join instead of the inner join and adjust your where clause to support the case when there is no B:

SELECT a.* FROM a LEFT OUTER JOIN b ON b.a_id = a.id
WHERE b.id == null OR (b.stuff = 1 AND (QueryCheckingDateWithBFields))

SELECT a.* FROM a LEFT OUTER JOIN b ON b.a_id = a.id
WHERE b.id == null OR b.stuff != 1 OR (!QueryCheckingDateWithBFields)

Upvotes: 4

Related Questions