I'm trying to compare two tables and work on a result of this operation.
SELECT * FROM table1
WHERE table1.czlowiek_id NOT IN (
SELECT table2.czlowiek_id
FROM table2
WHERE table1.czlowiek_id=table2.czlowiek_id
)
OR table1.czlowiek_id IN (
SELECT table2.czlowiek_id
FROM table2
WHERE table1.czlowiek_id=table2.czlowiek_id
AND table2.szkolenie_id IN ('$selected_one')
AND table1.instruktor IN ('$selected_two')
)
Is there some option to work on a result from this query adding another WHERE statement?
You may be able to solve the problem with a LEFT JOIN. This will allow you to pull all of the results from table1 that don't exist in table2, AND pull the results from table1 that are in table2 and match the requested filters.
SELECT
*
FROM
table1
LEFT JOIN table2 ON table1.czlowiek_id=table2.czlowiek_id
WHERE
table2.czlowiek_id IS NULL
OR (
table2.szkolenie_id IN ('$selected_one')
AND table1.instruktor IN ('$selected_two')
)
There is an alternative way to phrase this. The idea is to aggregate the information from Table2 at the czlowiek_id level, keeping information about the szkolenie_id since it might be needed.
This changes the query to a group by followed by a traditional join and a fairly normal where clause:
select table1.*
from table1 left outer join
(select czlowiek_id,
(case when table2.szkolenie_id IN ('$selected_one') then 1
else 0
end) as flag
from table2
group by czlowiek_id
) t2
ON table1.czlowiek_id = t2.czlowiek_id
WHERE t2.czlowiek_id IS NULL OR
(t2.flag = 1 AND table1.instruktor IN ('$selected_two') )
You can wrap your statement in a set of parenthesis, and assign it an alias (following the closing parent), and use that like a table. (We call this an inline view.)
SELECT q.*
FROM (
SELECT * FROM table1 ... /*your whole query here*/
) q
WHERE ...
You can reference any columns returned by your inline view in the WHERE clause. (This approach can work relatively well with an inline view that returns small-ish (reasonably sized) result sets. But with larger result sets, there's a lot of heavy lifting to do to "materialize" that view, before the outer query can run. It's a useful pattern, but it's also possible to abuse this pattern, and write statements that perform much more horribly than an equivalent statement that doesn't use an inline view.
But I'm not sure that's the question you are asking.
Can you add another predicate to the WHERE clause?
Sure you can... just add another AND or OR keyword, and add your conditional test. Just be careful of precedence between AND and OR. It's good practice to use parenthesis to specify which order you are expecting, there's a difference...
( a OR b ) AND c
a OR ( b AND c)
But I'm not sure that answers the question you are asking either.
Your correlated subqueries could use EXISTS and NOT EXISTS, rather than IN and NOT IN
I believe this will return an equivalent result:
SELECT *
FROM table1
WHERE NOT EXISTS
( SELECT 1 FROM table2
WHERE table2.czlowiek_id=table1.czlowiek_id
)
OR EXISTS
( SELECT 1 FROM table2
WHERE table2.czlowiek_id=table1.czlowiek_id
AND table2.szkolenie_id IN ('$selected_one')
AND table1.instruktor IN ('$selected_two')
)