combine two rows from one table together based upon join to a second table in MySQL

Question

Hello I have two tables in MySQL. The first table is called users it has a column ID that relates to table 2 which is called family. The family table has two columns, familyID and userID. Basicly what I want to do is concatenate a first name column in the users table where familyIDs are the same.

So if John(userID 1) and Jane(userID 2) have a familyID of 123 it returns a result of 'John and Jane'.

Is this posible with SQL or do I need another programming language like C# and have a logical check?

Updated info: Users Table:

/Column Information/

Field Type Key
----------------- ----------- ------
id int(11) PRI
username varchar(30)
fName varchar(50)
lName varchar(50)

Family Table:

/Column Information/

Field Type Collation Null Key

ID int(11) (NULL) NO MUL
userID int(11) (NULL) YES MUL

/Index Information/

Table Non_unique Key_name Seq_in_index Column_name

family 1 familyUser 1 userID
family 1 familyID 1 ID

Ive tried several joins and even a self join but most of them return data just not what Im looking for as it repeats all the first names join together.

Please let me know if you need any additional info.

You could use JOIN condition and in the link Clause check it's read this https://www.tutorialspoint.com/mysql/mysql-using-joins.htm and this to concatenate names https://stackoverflow.com/questions/5975958/mysql-concatenation — Javier Jimenez Matilla, Aug 01 '17 at 22:53
See https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query — Strawberry, Aug 01 '17 at 23:06

Code Spirit · Answer 1 · 2017-08-03T09:09:41.277

0

You can use an INNER JOIN to use the family columns in your conditions:

SELECT users.first_name 
    FROM users 
INNER JOIN family 
    ON family.userID=users.ID 
WHERE family.ID=:familyId;

Edit: Soory I am an PHP guy, so the :familyId is supposed to be replaced for the actual familyID you are searching for. When joining tables you can also use columns from joined tables for conditions so with the above query only results that has a corrosponding row in the family table with the supplied familyID will be selected.

edited Aug 03 '17 at 09:09

answered Aug 01 '17 at 23:08

Code Spirit

3,992
4
23
34

So I tried your statement and am getting Error Code: 1064 near ':familyId. as was asked earlier I will add additional info – Christopher Radar San Souci Aug 02 '17 at 00:35
Im not sure what the where clause is doing, I tried looking up but get mostly php stuff thinking Im adding a variable to the statement. – Christopher Radar San Souci Aug 02 '17 at 00:46

score 0 · Answer 2 · answered Aug 02 '17 at 13:41

I was able to figure it out with the help of a few other posts here that i was able to find and some tweaking.

This is what I came up with:

SELECT
  REPLACE(GROUP_CONCAT(IF(f.id = f.id,u.fname, NULL)), ',', ' & ') AS 'First Name'
FROM users AS u
JOIN family AS f 
  ON u.id = f.userid 
GROUP BY f.id;

Thank you for the in site to my problem!

combine two rows from one table together based upon join to a second table in MySQL

/Column Information/

/Column Information/

/Index Information/

2 Answers2