Mysql query to get data from multiple tables

Question

I have 2 tables, t1 and t2, t1 holds aliases and scores, while t2 holds aliases and their real names, I wish to build a query to get the scores,real names and aliases (example - desired results)

t1

alias - scores - tl_alias - tm_alias (column names)

tk - 96 - pp - jj
sp - 94 - pp - jj

t2

name - alias - role  (column names)

tom Koshy - tk - user  
shaun penn - sp - user  
peter pan - pp - tl  
john james - jj - tm

Desired Result

user_alias - user_name - scores - tl_alias - tl_name - tm_alias - tm_name (column labels)


tk - tom koshy - 96 - pp - peter pan - jj - john james
sp - shaun penn - 94 - pp - peter pan - jj - john james

Current Results

The below query gives me tl_name for all instances of t2.name in the query which I think is correct as per the query but what I would like to have is, the first instance of t2.name should show the user_name, then next instance should show the tl_name and then the tm_name

SELECT t1.alias,t2.name,t1.scores,t1.tl_alias,t2.name,t1.tm_alias,t2.name from t1 JOIN t2 on t1.tl_alias = t2.alias

tk - peter pan - 96 - pp - peter pan - jj - peter pan
sp - peter pan - 94 - pp - peter pan - jj - peter pan

The below does not work either

SELECT t1.alias,t2.name,t1.scores,t1.tl_alias,t2.name,t1.tm_alias,t2.name from t1 JOIN t2 on t1.tl_alias = t2.alias, t1.tm_alias = t2.alias

@Uueerdo , can you give a quick example on joining twice, I tried the below but didn't work — Manuraj Sebastian
– Manuraj Sebastian, Commented Sep 13, 2018 at 19:10

Zaynul Abadin Tuhin · Accepted Answer · 2018-09-13 19:19:01Z

1

use inner join and sub-query

select t3.*,t2.name  from
(    
 select t1.alias,t2.name, t1.scores ,t1.tl_alias  from t1 join t2 on t1.alias =t2.alias
) as t3 join t2 on t3.tl_alias  =t2.tl_alias

or use join with t2 for two times by using table alias

select t1.alias,t2.name, t1.scores ,t1.tl_alias,t3.name 
 from t1 join t2 on t1.alias =t2.alias
      join t2 as t3 on t1.tl_alias=t3.alias

edited Sep 13, 2018 at 19:19

answered Sep 13, 2018 at 19:05

Zaynul Abadin Tuhin

32.1k6 gold badges37 silver badges66 bronze badges

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

4 Comments

Manuraj Sebastian Over a year ago

looks complex, let me try this :)

Zaynul Abadin Tuhin Over a year ago

@ManurajSebastian not complex just t2 use two times in join using table alias

Zaynul Abadin Tuhin Over a year ago

@ManurajSebastian no this is not typo its table t2 alias for joining with diff column

Manuraj Sebastian Over a year ago

,sorry to bother you again, is it possible to do this with the AS , I have created a small CMS system, I do not have an option to add AS there

Uueerdo · Accepted Answer · 2018-09-13 19:24:06Z

0

I misspoke in my initial comment, you actually need to join to t2 three times, like so...

SELECT t1.alias AS user_alias, t2_u.name AS user_name
   , t1.scores
   , t1.tl_alias, t2_l.name AS tl_name
   , t1.tm_alias, t2_m.name AS tm_name
FROM t1 
   JOIN t2 AS t2_u on t1.alias = t2_u.alias
   JOIN t2 AS t2_l on t1.tl_alias = t2_l.alias
   JOIN t2 AS t2_m ON t1.tm_alias = t2_m.alias
;

answered Sep 13, 2018 at 19:24

Uueerdo

16k1 gold badge19 silver badges21 bronze badges

5 Comments

Manuraj Sebastian Over a year ago

sorry to bother you again, is it possible to do this with the AS , I have created a small CMS system, I do not have an option to add AS there

Manuraj Sebastian Over a year ago

I think I will have to add the option to add AS there then :)

Manuraj Sebastian Over a year ago

I have added the AS option, and when I run the above code, it just freezes my PC, I have tried it on 2 machines, are you sure there are no typos or anything missing in the code in the code

Uueerdo Over a year ago

Try the query in a query browser like MySQL Workbench or phpMyAdmin. If it has typo's, MySQL should reject it. If it runs quickly, you know it's your homegrown framework. If it is still slow, you probably want to consider indexing t2 on it's alias field.

Manuraj Sebastian Over a year ago

kewl that worked on phpmyadmin MySQL like a charm but does not work in phpmyadmin Raspbian MariaDB, will figure it out why it is not working in MariaDB, thanks champ

Collectives™ on Stack Overflow

Mysql query to get data from multiple tables

2 Answers 2

4 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related