mysql select in two tables

Question

hi to all

I have two tables and one reference table for the query. Any suggestion or help would greatly appreciated.

table1

user_id   username     firstname     lastname        address
  1       john867      John          Smith          caloocan
  2       bill96       Bill          Jones          manila

table2

user_name_id   username       firstname      lastname        address       designation
  1             jakelucas     Jake           Lucas           caloocan        employee
  2             jadejones     Jade           Jones           Quezon          student
  3             bong098       Bong           Johnson         pasig           employee

reference table

ref_id   username      friend_username
  1      tirso         bill96
  2      tirso         jadejones
  2      tirso         bong098

the output should like this

user_id    user_name_id      username       firstname      lastname        address       designation
  2                          bill96          Bill           Jones           manila
             2               jadejones       Jade           Jones           Quezon          student
             3               bong098         Bong           Johnson         pasig           employee

Answer 1

Can you try something like this

SELECT  [table1].[USER_ID],
     NULL user_name_id,
     [table1].username,
     [table1].firstname,
     [table1].lastname,
     [table1].address,
     NULL designation
FROM    reference_table INNER JOIN
     table1 ON [reference_table].friend_username = [table1].username
UNION 
SELECT  NULL USER_ID,
     [table2].user_name_id,
     [table2].username,
     [table2].firstname,
     [table2].lastname,
     [table2].address,
     [table2].designation
FROM    reference_table INNER JOIN
     table2 ON [reference_table].friend_username = [table2].username

Answer 2

It's not quite clear what you are trying to achieve, but here's a guess:

SELECT user_id, NULL as user_name_id, username, ...
FROM ref_tab r join table1 t1 on r.friend_username = t1.username
WHERE r.ref_id = 1
UNION 
SELECT NULL as user_id, user_name_id, username, ...
FROM ref_tab r join table1 t2 on r.friend_username = t2.username
WHERE r.ref_id = 2

But I'd have a hard look at the DB design and think about some improvements ...

Answer 3

Since some decent union queries have already been posted, I'll talk about your db design a little bit.

I would definitely take what IronGoofy said into serious consideration before you take too much time looking into joining these tables together. It seems that you have a lot of duplicate data to manage with your tables, and that could get out of hand rather quickly should this scale up.

I think you should probably try and separate your data out so that the important information can be linked on the user_id.

So, for instance, you could have a few tables here...

User Information Table:
---------
User_id
Username
First Name
Last Name
Address
Designation_id

Friend Link Table:
---------
Friend_link_id
User_id     
Friend_user_id   

Designation Table:
---------
Designation_id
Designation_name

So, rather than link on your user names all over the place, you would simply join on the various ID's. A bit cleaner and missing the duplicate data issue that you had before IMO. Hope this helps...