Sign in to follow this  
DareDeveloper

MySQL - Group By and Where Clauses

Recommended Posts

Hi, hope somebody is looking for a challenge (or knows a bit about this stuff) I have a SELECT statement with a GROUP BY and not all the conditions in the WHERE clause are true for all the results. Is there some kind of hierarchy for conflicting commands?
SELECT DISTINCT
	e.event_id, e.caption
FROM
	EVT_EVENT e,
	EVT_DATE d,
	EVT_ADDRESS st
WHERE
	e.event_id = d.event_id AND
	e.eventtyp_id = 1 AND
	e.address_id NOT IN (SELECT adr.address_id FROM EVT_ADDRESS adr WHERE adr.address_id = e.address_id AND adr.addressgroup_id not in (1)) AND
	e.address_id NOT IN (SELECT rel1.address_id FROM EVT_ADDRESS asp, EVT_RELATION rel1 WHERE e.address_id = rel1.address_id AND rel1.rel_address_id = asp.address_id AND asp.addressgroup_id not in (1))	AND
	d.address_id = st.address_id AND
	st.addressgroup_id in (1) AND
	st.address_id NOT IN (SELECT rel2.address_id FROM EVT_ADDRESS staette, EVT_RELATION rel2 WHERE rel2.rel_address_id = staette.address_id AND staette.addressgroup_id not in (1))
GROUP BY e.event_id, e.caption, st.caption
ORDER BY e.caption


There might be some typos because I translated some german captions. Does anybody notice anything weird/wrong? MySQL seems to ignore some where clauses. But I am probably wrong ... maybe the tables are not joined properly or something like that?

Share this post


Link to post
Share on other sites
On this WHERE clause:

d.address_id = st.address_id AND
st.addressgroup_id in (1) AND
st.address_id NOT IN (SELECT rel2.address_id FROM EVT_ADDRESS staette, EVT_RELATION rel2 WHERE rel2.rel_address_id = staette.address_id AND staette.addressgroup_id not in (1))

do you only want the statement
d.address_id = st.address_id
to select those st.address_id that are not in

st.address_id NOT IN (SELECT rel2.address_id FROM EVT_ADDRESS staette, EVT_RELATION rel2 WHERE rel2.rel_address_id = staette.address_id AND staette.addressgroup_id not in (1))

?
if so, that could be a problem for why it is not restricting your query.
you would need to do something like:

d.address_id =
(SELECT st.address_id WHERE st.address_id NOT IN (SELECT rel2.address_id FROM EVT_ADDRESS staette, EVT_RELATION rel2 WHERE rel2.rel_address_id = staette.address_id AND staette.addressgroup_id not in (1))

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this