Jump to content

Recommended Posts

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?

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

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.