Jump to content
  • Advertisement
Sign in to follow this  
DareDeveloper

MySQL - Group By and Where Clauses

This topic is 3779 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

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
Advertisement
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
Sign in to follow this  

  • Advertisement
×

Important Information

By using GameDev.net, you agree to our community Guidelines, Terms of Use, and Privacy Policy.

Participate in the game development conversation and more when you create an account on GameDev.net!

Sign me up!