CODEWITHSUNDEEP
phpmysqlsqljoin
Peter Stuart
Peter Stuart

Reputation: 2434

Advice on MySQL JOIN

I am struggling to get me head round the MySQL Join function.

Here is two tables I have:

User table:

admin_id     username
  *1          peter93
   2       stackoverflowrocks
   3          user3*

Admin details table:

   admin_username     description        image
       pedro93          [text]        [image_url]
 stackoverflowrocks     [text]        [image_url]
        user3           [text]        [image_url]

I know usualy to link two databases together you would use ID numbers, but in this case I want to join the two tables where admin_username = username.

Can anyone help me please? It is for a PHP script if that helps.

Thanks in advance! Peter

Upvotes: 1

Views: 103

Answers (3)

T I
T I

Reputation: 9933

You can join on pretty much any field but you may wish to keep in mind datatype mismatching, indexing etc

All users

SELECT u.*, a.*
FROM users AS u
LEFT JOIN admin AS a 
       ON u.username = a.username

All users that are admin

SELECT u.*, a.*
FROM users AS u
INNER JOIN admin AS a 
       ON u.username = a.username

All admin

SELECT u.*, a.*
FROM users AS u
RIGHT JOIN admin AS a 
       ON u.username = a.username

Upvotes: 1

Vlad Balmos
Vlad Balmos

Reputation: 3412

joining by a string column is the same as joining by Id

select * from admin_table
inner join admin_details on admin_table.username = admin_details.admin_username

But make sure you have and index for the username columns, or else your queries will go slow when you have lots of records

Upvotes: 1

podiluska
podiluska

Reputation: 51494

So that's fine. You can join on anything you like.

select * 
from user
    inner join admin
    on user.username=admin.admin_username

Upvotes: 7

Related Questions