2

I have a schema that looks like this:

                  +------------+
+------------+    | acc_email  |
| account    |    +============+
+============+    | id         |
| id         |<-+-+ account_id |
+------------+  | | email      |
                | +------------+
                |  +------------+
                |  | password   |
                |  |============|
                |  | id         |
                +--+ account_id |
                   | password   |
                   | iterations |
                   | salt       |
                   +------------+

The users login via email, need to find the account, than find the password. I got this far:

       SELECT * 
         FROM acc_email
    LEFT JOIN account
           ON acc_email.account_id = account.id

How do I join and retrieve the account email, account and latest password in a single query?

Improve this question

1 Answer 1

Reset to default
1

I can't see any email_id or account.id in your Schema, but following your design it must be

   SELECT  a.*,ae.email , p.password    
   FROM acc_email ae
        LEFT JOIN account a
             ON ae.account_id = a.id 
        LEFT JOIN password p
             ON p.account_id  = a.id 
   WHERE ae.email = '[email protected]'
Improve this answer
3
  • Ahhh, yes, I made a bunch of mistakes, but using your answer, I was able to complete it with some design changes! dbfiddle.uk/… Commented Aug 21, 2021 at 22:44
  • no you have a small bug see dbfiddle.uk/… and please don't forget to accept the answer Commented Aug 21, 2021 at 22:56
  • I think I fixed where "I cant see email_id or account.id in your Schema". Do you mind editing it out in case someone read it so they are not confused. Commented Aug 22, 2021 at 0:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.