Jump to content


Photo

Problem selecting multiple records from multiple tables


  • Please log in to reply
5 replies to this topic

#1 nezbie

nezbie
  • Members
  • PipPipPip
  • Advanced Member
  • 31 posts
  • LocationFINLAND

Posted 27 September 2006 - 03:52 PM

Hi

I have two database fields looking like this:

distribution_lists (only relevant fields regarding this question
dlist_id
user_id
email_sent

users (only relevant fields regarding this question
user_id

$toNewListQ = 'SELECT user_id, (SELECT d.email_sent FROM distribution_lists AS d LEFT JOIN users AS u ON u.user_id = d.user_id WHERE d.dlist_id = "' . $_POST['dlist_id'] . '" LIMIT 0,1) AS email_sent FROM users WHERE user_id IN (' . $givenIDs. ') ORDER BY user_id ASC';

Now, the problem is with this query, that the user who exists on users -table might not exists in distribution_list -table. My goal is to join the users table to the distr.list table and get info about if a certain user has sent email or not, if he/she has the row should return 1. If the user is on the distribution list and he/she has not sent email, it would return 0. If the user does not exist on the distribution list, it should return 0 (for email_sent -field value)

Any ideas ?

Thanx
:: Be patient with me, I\'m new to PHP and MySQL :: :)

#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 27 September 2006 - 06:47 PM

You don't need a subquery for this... just JOIN users and lists directly, and use email_sent in the ON clause.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 nezbie

nezbie
  • Members
  • PipPipPip
  • Advanced Member
  • 31 posts
  • LocationFINLAND

Posted 27 September 2006 - 08:52 PM

SELECT u.user_id, d.email_sent FROM users AS u LEFT JOIN distribution_list AS d ON u.user_id = d.user_id WHERE d.dlist_id = "' . $_POST['dlist_id'] . '" AND u.user_id IN (' . $givenIDs . ') ORDER BY u.user_id ASC

Now that's the query. It works half. It now selects the right value for email sent IF THE USER IS FOUND ON distribution_lists TABLE. There will be a situation that all the IDs given in a comma separated list variable, $givenIDs won't be found on this table.

Considering the query this way might help: 1) select the users info from USERS table. Then join DISTRIBUTION_LISTS to check if users info exists on that table and get only the EMAIL_SENT value true or false.

Half way of winning this :)
:: Be patient with me, I\'m new to PHP and MySQL :: :)

#4 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 27 September 2006 - 09:19 PM

I'm not sure I understand... you can simply use IFNULL() to check the value of d.email_sent, and return whatever value you want.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#5 nezbie

nezbie
  • Members
  • PipPipPip
  • Advanced Member
  • 31 posts
  • LocationFINLAND

Posted 27 September 2006 - 09:32 PM

*thinking*

So I have these two tables. I have a form to select users. Those users IDs are saved in this $givenIDs variable. (comma separated, in php). My tables are USERS and DISTRIBUTION_LISTS

All users available for my form are already found on USERS table. Anyhow, on this certain case I have to know if user has already received email. This info is found on DISTRIBUTION_LISTS table. Now, the DISTRIBUTION_LIST table might not hold the userID in progress. If so, email_sent should be set to 0.

So the problem with my current query is that if the user_id exists in USERS table but NOT in DISTRIBUTION_LISTS table, mysql for some reason skips this row and returns NO user_id OR NO value for email_sent - regardless if the user is found on one of the tables.. I have to get some value (null or 0) for email_sent field for my php script to handle on..
:: Be patient with me, I\'m new to PHP and MySQL :: :)

#6 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 27 September 2006 - 10:32 PM

I'm not sure I understand -- this LEFT JOIN will return a record for every single user found in the USERS table, regardless of any matching rows in the DISTRIBUTION_LISTS table.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users