Problem selecting multiple records from multiple tables

[nezbie]

Hi

I have two database fields looking like this:

[b]distribution_lists[/b] (only relevant fields regarding this question
dlist_id
user_id
email_sent

[b]users[/b] (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

[fenway]

You don't need a subquery for this... just JOIN users and lists directly, and use email_sent in the ON clause.

[nezbie]

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 :)

[fenway]

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.

[nezbie]

*thinking*

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

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

So the problem with my current query is that if the [b]user_id[/b] exists in [b]USERS table[/b] but NOT in [b]DISTRIBUTION_LISTS[/b] table, mysql for some reason skips this row and [b]returns NO user_id OR NO value for email_sent[/b] - regardless if the user is found on one of the tables.. I have to get some value (null or 0) for [b]email_sent[/b] field for my php script to handle on..

[fenway]

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.