CODEWITHSUNDEEP
mysql
vavutijul
vavutijul

Reputation: 3

Set Alias in SELECT with Inner Join

I have a 2 tables:

People table:

id  |  name |  date
1   |  Mika |  2013
2   |  Rose |  2015

Work table:

id  |  user_id  |  work_name  |  registers
1   |     1     |    rugby    |     10
2   |     1     |    golf     |      3

I use this query to join tables:

SELECT * FROM work INNER JOIN people ON work.user_id = people.id WHERE work_name= :work_name

This work it, but I get duplicate column ID and in php when I try to print the camp id, this show the last column id.. I try with this but dont work it:

SELECT *, id AS 'work_id' 
FROM work 
INNER JOIN people ON work.user_id = people.id 
WHERE work_name= :work_name

Upvotes: 0

Views: 44

Answers (2)

Alex Filipovsky
Alex Filipovsky

Reputation: 1

Try to manually list all of columns you need to display, for instance

SELECT w.user_id, w.work_name, w.registers, p.name, p.date FROM work as w INNER JOIN people as p ON work.user_id = people.id WHERE work_name= :work_name

Upvotes: 0

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

That is because you are using * in the SELECT that mean will bring all the fields from boths tables instead you can use 

SELECT   work.id  as work_id, 
         work.name as work_name, 
         work.date as work_date,
         people.id as people_id, 
         people.name as people_name, 
         people.date as people_date

Upvotes: 1

Related Questions