CODEWITHSUNDEEP
phpmysql
Mahantesh
Mahantesh

Reputation: 357

how to select one row from one table and multiple rows from other table using joins in mysql,

I searched google and everything all are explained using joins but i dont need duplicate data of the first table, I want single row from first table.

consider table a has two column id and name

id | name

1  | jhon

2  | rock

and table b has two column id which is foreign key referencing table a's id and place

id | nickname

1  | steve

1  | smith

2  | kiran

And i want out put as

id | name | nickname

1  | jhon | steve

          | smith

Upvotes: 1

Views: 2880

Answers (1)

Darshan Mehta
Darshan Mehta

Reputation: 30849

You can use simple inner JOIN to do this, e.g.:

SELECT ta.id, ta.name, tb.nicname
FROM tablea ta JOIN tableb tb ON ta.id = tb.id;

This would result in multiple records for one user, to get only one record, you can use GROUP_CONCAT, e.g.:

SELECT ta.id, ta.name, GROUP_CONCAT(tb.nicname)
FROM tablea ta JOIN tableb tb ON ta.id = tb.id
GROUP BY ta.id, ta.name;

Update

To get the expected output, you can define variables in query and use IF to return the values, e.g.:

SELECT IF(@previous = ta.id, '', ta.id) as id, IF(@previous = ta.id, '', ta.name) as name, 
tb.nickname, @previous := ta.id 
FROM table1 ta JOIN table2 tb ON ta.id = tb.id,
(SELECT @previous := '') a ;

This will return an extra column but you can ignore that while parsing the result.

Upvotes: 1

Related Questions