Join two rows of the mysql table

Databases
1

Hi! Is there any way by which I can join two rows (records) into one Row (record) on the basis of common field between them ??? for example

Id  Father Name   Son Name
1     John                Roy
2     John           Gill

I want to join then as follow (Output)

Id Father Name   Son Name 1      Son Name2
1   John                   Roy                    Gill
2

Well my first question would be… why?
Second question would be what you expect the output to look like.

3

forgot to add output first, I am looking for following output

Id Father Name   First Son   2nd Son
1  John                 Roy               Gill
4

Okay…
what if there are 3 entries?
or 4?
or 100?

You’d do better handling this in the receiving program, rather than trying to turn multiple rows into 1 in the database result.

5

It will be maximum two rows, wont be more then two at any point

6

Going by your example, if you want the name “John” to be referenced the same in multiple records, you may have a separate ‘name’ table. You would then reference the name “John” from that table by its ID as a foreign key.
Though we probably need to see more of your database and its intenden purpose to give a solid answer.

7

Gonna still say this is a bad idea.

Here’s how my brain managed to do it.

SELECT t1.id, t1.fathername, t1.sonname, t2.sonname AS s2name
FROM tablename AS t1
LEFT JOIN tablename AS t2 ON (
         t1.fathername = t2.fathername 
     AND t2.id > t1.id
)
WHERE t2.sonname IS NOT NULL
UNION
SELECT id, fathername, sonname, "" AS s2name 
FROM tablename
WHERE fathername IN(
    SELECT fathername FROM(
        SELECT fathername, COUNT(fathername) as c1 
        FROM tablename 
        GROUP BY fathername HAVING c1 = 1
    ) as t3
)

Could it be done better? possibly. Is my brain at 8 AM on a Sunday put effort into making a bad idea work better?..

2 Likes
8

Why not

Select father, group_concat(sonname) from table group by father
9

it’s a simple self-join under normal circumstances, but there are two problems here

  1. why does the result row have an ID, and how do you determine which one it is

  2. where does the order “first son… second son” come from?

10

so we’re not talking about fathers and sons, are we

what is it, really?

11

it was payment system some thing like
three ways of payment 1) cash 2)card 3)online

12

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.