Situation. We have 3 tables. User, add. user emails and some data associated with each email.

  1. table_users

UserID | username | email

  1. table_emails

UserID | additional_email

  1. table_data

email | date_added | email_data

TASK: username | email_data

For each user, you need to display some data (email_data), the first in time to add (date_added). Moreover, the data can be tied to any of the user's emails, both to the main one from the table table_users, and to the additional email from table_emails, which can be infinitely many (really, of course, 2-3 pieces). Data email_data for each email can be several, and maybe not at all, so something like this: LEFT JOIN ..ORDER BY date_added LIMIT 1. All this is in conjunction with php, so I think somehow dynamically build a query, in the first step, collect all emails into an array, and then type:

<?php foreach( $emails as $email ) { $i++; $sql .= " LEFT JOIN table_data as td$i ON td$i.email = $email"; } ?>

But somehow, the final request is still not invented ... Help plz.

Request final need a single. I can not, by various requests, first pull out all the data for one email, then if there is an additional one, etc., and glue them together on php. Because there everything is more complicated really, all tables have more fields and by them it is necessary to filter, sort and paginate.

  • 2
    If the user has 3 emails, the latest data for each of them or to the user is the only one line with the most recent data on the email for which they are actually the latest. would give better examples of input and example of output - Mike
  • We need fresh data on one of the user's emails by the date of addition. One user-one data. Real data - there’s a hell of a leg in general, there are actually several data fields, they also need to be filtered, sorted, so I simplify the task. :) - Movs

3 answers 3

I think something like this:

 select * from table_users U left join table_data D on (D.email, D.date_added)= (select email, date_added from table_data D1 where D1.email in(select additional_email from table_emails E where E.UserID=U.UserID union all select U.email ) order by date_added desc limit 1 )

If, of course, you cannot have two records for a single email with repeating time (according to the structure of your tables, this goes like this, for the other unique key in table_data cannot be traced).

In general, I would suggest removing the email field from the user table and storing all email in the emails table. If you need a "main email" then mark it with a special flag in this table.

    for a specific usera something like this

    select u.userid, d.email, d.date_added, d.email_data from table_users u left join table_emails e on u.userid= e.userid left join table_data d on d.email = e.additional_email or u.email = d.email where u.userid = 1 order by d.date_added desc limit 1 http://sqlfiddle.com/#!9/0b53b40/1

    • It is difficult to say which of the requests is more faithful, but my first thought was to write something like the author's answer. Does he work out? - ultimatum

    Problems with sorting if information about the main email is not inserted into the table_emails table:

     select u.username, COALESCE(td1.email_data, td2.email_data) as email_data from users u left join table_emails te on te.user_id = u.user_id left join table_data td1 on td1.email = u.email left join table_data td2 on td2.email = te.email order by ???

    Problems, because time needs to be chosen from the "one" table, then from the "other" (td1 and td2). BUT!!! If you insert data about the main email into the table_emails table, then everything is fine:

     select u.username, td.email_data from users u left join table_emails te on te.user_id = u.user_id left join table_data td on td.email = te.email order by td.date_add