[SOLVED] Probably simple yet annoying query problem

[Dargrotek]

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?

[Daniel0]

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';

[Dargrotek]

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';

[Daniel0]

What happens when you just do

SELECT * FROM user_registration_details WHERE username = 'testaccount';

?

[Dargrotek]

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';

?

[Daniel0]

Then try to do a LEFT JOIN instead.

[Dargrotek]

That done the trick, thank you.

 

Then try to do a LEFT JOIN instead.