Problem selecting multiple records from multiple tables
Posted 27 September 2006 - 03:52 PM
I have two database fields looking like this:
distribution_lists (only relevant fields regarding this question
users (only relevant fields regarding this question
$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 ?
Posted 27 September 2006 - 08:52 PM
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
Posted 27 September 2006 - 09:32 PM
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..
Posted 27 September 2006 - 10:32 PM
0 user(s) are reading this topic
0 members, 0 guests, 0 anonymous users