Geek asks and answers

MySQL complex query SELECT?

Here is my table structure:

users:

id | email

emails:

id | subject | body

user_emails:

user_id | email_id

This is a very simple design. However, I need to select the first (lowest) emails.id for all users.id that are not connected in user_emails.

To illustrate:

users:

id | email
1 | email@domain.com
2 | test@lol.com
3 | user@test.com

emails:

id | subject | body
1 | sub1 | body1
2 | sub2 | body2

user_emails:

user_id | email_id
1 | 1
1 | 2
2 | 1

As the data show:

  • User 1 received email 1 and 2, so the choice should exclude this user.
  • User 2 received only email 1, so he should select user 2 and email 2.
  • User 3 did not receive any emails, so he should select user 3 and email 1.

I will do this in PHP, so any logic should be written in PHP, if it is impossible to do only with mySQL.

Thanks in advance

EDIT: , , , . script, , . , .

+5
3

. lastSent users.

// Load all emails into an array. Use the id as the key.
$sql = "SELECT * FROM emails";
$query = mysql_query( $sql );
while( $row = mysql_fetch_assoc( $query ) ) {
    $emails[ $row['id'] ] = $row;
}

// Load all users into an array.
$sql = "SELECT id, email, lastSent FROM users";
$query = mysql_query( $sql );
while( $row = mysql_fetch_assoc( $query ) ) {
    $users = $row;
}

// Loop through each user and send next email from the list based on lastSent value.
foreach ( $users as $user ) {
    $id = $user['lastSent'] + 1;
    $email = $user['email'];
    $subject = $emails[$id]['subject'];
    $body = $emails[$id]['body'];
    $send = mail($email, $subject, $body);
    if ( $send ) {
            // Update lastSent value.
        $sql = "UPDATE users SET lastSent='$id' WHERE id='" . $user['id'] . "'";
        mysql_query( $sql );
    }
}
0

:

SELECT * from users u 
LEFT JOIN users_emails e ON u.id=e.user_id 
LEFT OUTER JOIN emails m on m.id=1 
WHERE e.user_id is NULL

, 1, .

SELECT * from users u 
LEFT JOIN users_emails e ON u.id=e.user_id 
LEFT OUTER join emails m on m.id=(SELECT id from emails order by id limit 1)   
WHERE e.user_id is NULL
+2

t.id, min (t.q_id) (SELECT u.id, e.id q_id u CROSS JOIN e) t (t.id, t.q_id) ( * from user_emails) t.id

0

More articles:


All Articles