join mysql tables and get multiple rows

Question

I'm having an issue with MySQL .

I have 2 Tables :

 Table A             Table B

 userId               userId
 name                 email
 email

It's a relation 1->N (1 user can have multiple emails in Table B)

Now i want to import my users into a mail program , i want to make it in 1 query instead of multiple query .

What i would like to have is the following :

user id , name , email

ex:

1 , John Doe , [email protected]    (This email comes from Table A)
1 , John Doe , [email protected]  (This email comes from Table B)

Currently i have the following query That returns multiple row but the problem is that the fields are not the same

i get something like :

Table A.name , Table A.email , Table B. email  etc ..


SELECT
jos_users.userId,
jos_members_webAddress.email2,
jos_users.uniqueId,
jos_users.firstName,
jos_users.lastName,
jos_users.username,
jos_users.email


FROM
jos_users
INNER JOIN jos_members_webAddress ON jos_users.userId = jos_members_webAddress.memberId

Thanks for the help

The system was made in a way that a user could have N emails . — Tarek
– Tarek, Commented Mar 3, 2012 at 2:15
You now have 1 email in tableA, N-1 emails in tableB. Without the email in tableA, you would have N emails in tableB, and no pesky exceptional cases in tableA. — Amadan
– Amadan, Commented Mar 3, 2012 at 2:18
You are right but the request to Add multiple email came later and email from Table A is being used for the login system . — Tarek
– Tarek, Commented Mar 3, 2012 at 2:25

Amadan · Accepted Answer · 2012-03-03 02:17:41Z

4

Literal answer to your question:

SELECT userId, name, email
FROM tableA
UNION
SELECT tableB.userId, tableA.name, tableB.email
FROM tableA
JOIN tableB ON tableA.userId = tableB.userId

Better answer to your question, unless you can give my first response in the comments with a solid answer:

Delete email from tableA, have all emails in tableB, then it's just the part of the answer below the UNION.

answered Mar 3, 2012 at 2:17

Amadan

200k23 gold badges253 silver badges321 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

join mysql tables and get multiple rows

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related