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
Well my first question would be… why?
Second question would be what you expect the output to look like.
forgot to add output first, I am looking for following output
Id Father Name First Son 2nd Son
1 John Roy Gill
what if there are 3 entries?
You’d do better handling this in the receiving program, rather than trying to turn multiple rows into 1 in the database result.
It will be maximum two rows, wont be more then two at any point
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.
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
SELECT id, fathername, sonname, "" AS s2name
WHERE fathername IN(
SELECT fathername FROM(
SELECT fathername, COUNT(fathername) as c1
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?..
Select father, group_concat(sonname) from table group by father
it’s a simple self-join under normal circumstances, but there are two problems here
why does the result row have an ID, and how do you determine which one it is
where does the order “first son… second son” come from?
so we’re not talking about fathers and sons, are we
what is it, really?