Dargrotek Posted March 18, 2009 Share Posted March 18, 2009 New around here so forgive me if I haven't done something I should. Anyway the problem. I've been fiddling around with relational queries for a while now to try and compact everything together and I've found this rather bizarre issue that I can't seem to figure out. The original query is as follows: SELECT user_registration_details.id, COUNT(user_mail_blist.id) FROM user_registration_details, user_mail_blist WHERE user_registration_details.game_id = '2' AND (user_mail_blist.user_id = user_registration_details.id AND user_mail_blist.save_id = '1') This works completely fine, returns the correct values of. user_registration_details.id: 2 COUNT(user_mail_blist.id): 0 When I modified it to look up via a username instead of an id it all goes wrong. SELECT user_registration_details.id, COUNT(user_mail_blist.id) FROM user_registration_details, user_mail_blist WHERE user_registration_details.username = 'testaccount' AND (user_mail_blist.user_id = user_registration_details.id AND user_mail_blist.save_id = '1') This for some reason returns the following user_registration_details.id: NULL COUNT(user_mail_blist.id): 0 All the data in the tables are correct and just in case i created new entries to test them with no success. I realise it's probably just a case of a syntax error somewhere that's causing the problem, but I'll be damned if I can see it. I never though changing one field would cause me so much aggrivation late at night. Any thoughts? Quote Link to comment https://forums.phpfreaks.com/topic/149935-solved-probably-simple-yet-annoying-query-problem/ Share on other sites More sharing options...
Daniel0 Posted March 18, 2009 Share Posted March 18, 2009 Try this: SELECT rd.id, COUNT(mb.id) FROM user_registration_details AS rd INNER JOIN user_mail_blist AS mb ON mb.user_id = rd.id AND mb.save_id = 1 WHERE rd.username = 'testaccount'; Quote Link to comment https://forums.phpfreaks.com/topic/149935-solved-probably-simple-yet-annoying-query-problem/#findComment-787446 Share on other sites More sharing options...
Dargrotek Posted March 18, 2009 Author Share Posted March 18, 2009 Same result, no ID returned :-\ Try this: SELECT rd.id, COUNT(mb.id) FROM user_registration_details AS rd INNER JOIN user_mail_blist AS mb ON mb.user_id = rd.id AND mb.save_id = 1 WHERE rd.username = 'testaccount'; Quote Link to comment https://forums.phpfreaks.com/topic/149935-solved-probably-simple-yet-annoying-query-problem/#findComment-787448 Share on other sites More sharing options...
Daniel0 Posted March 18, 2009 Share Posted March 18, 2009 What happens when you just do SELECT * FROM user_registration_details WHERE username = 'testaccount'; ? Quote Link to comment https://forums.phpfreaks.com/topic/149935-solved-probably-simple-yet-annoying-query-problem/#findComment-787450 Share on other sites More sharing options...
Dargrotek Posted March 18, 2009 Author Share Posted March 18, 2009 Yeah i tried that before as well, it returns all the correct values. What happens when you just do SELECT * FROM user_registration_details WHERE username = 'testaccount'; ? Quote Link to comment https://forums.phpfreaks.com/topic/149935-solved-probably-simple-yet-annoying-query-problem/#findComment-787454 Share on other sites More sharing options...
Daniel0 Posted March 18, 2009 Share Posted March 18, 2009 Then try to do a LEFT JOIN instead. Quote Link to comment https://forums.phpfreaks.com/topic/149935-solved-probably-simple-yet-annoying-query-problem/#findComment-787465 Share on other sites More sharing options...
Dargrotek Posted March 18, 2009 Author Share Posted March 18, 2009 That done the trick, thank you. Then try to do a LEFT JOIN instead. Quote Link to comment https://forums.phpfreaks.com/topic/149935-solved-probably-simple-yet-annoying-query-problem/#findComment-787468 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.