CODEWITHSUNDEEP
mysqlsqljoin
Tarek
Tarek

Reputation: 3798

join mysql tables and get multiple rows

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

Upvotes: 0

Views: 178

Answers (1)

Amadan
Amadan

Reputation: 198526

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.

Upvotes: 4

Related Questions