SQL Joins for repeating records

Question

I have two tables. One table with static information (userid, username, dob, village). The second table with variable information (userid, favourite_food). One user can have several favourite foods.

I want to join the two tables to output without repeating the record. The favourite foods should be in colums but the numbers can be varied for each user.

Example Table 1

userid  usernames   dob     village
2001    wallace 1963    olu
2002    John    1975    remba
2003    Maurice 1954    goro
2004    Patrick 1932    goro
2005    Emma    2000    chula
2006    Mary    1989    remba
2007    Anne    2001    sakwa
2008    Moses   1994    remba
2009    Agatha  1956    sori

Table 2

userid  favourite_food
2001    fries
2002    rice
2003    potatoes
2004    chicken
2004    beef
2005    mutton
2001    pork
2003    chapati
2004    dhal
2006    fries
2007    carrot
2008    lamb
2009    bread
2009    corn

Expected output

userid  usernames   dob     village favouritef_food1    favourite_fod2  favourite_food3
2001    wallace 1963    olu freis   pork    
2002    John    1975    remba   rice        
2003    Maurice 1954    goro    potatoes    chapati 
2004    Patrick 1932    goro    chicken beef    dhal
2005    Emma    2000    chula   mutton      
2006    Mary    1989    remba   fries       
2007    Anne    2001    sakwa   carrot      
2008    Moses   1994    remba   lamb        
2009    Agatha  1956    sori    bread   corn

Note: There is no prior way to know the number of favourite foods one can have and there is no max. For each additional, there should be a column in the output.

How do i do this?

Could you please provide a sample output, how you'd like to have the result? — BurninLeo, Jul 26 '18 at 19:59
userid, username, dob, village, favourite_food1, favourite_food2, favorite_food3, ....favourite_foodn). The columns for favourite_food should be the maximum any user have in the table but this is not known before hand. ( can be 3,4, ....10). — Joseph Mugah, Jul 26 '18 at 20:07
Please improve your question by adding to it, your schema and an example of the output you want. — petey, Jul 26 '18 at 21:35
The number of columns of the result of a query has to be defined **before** the query is executed. There might be some workarounds using dynamic SQL, but it depends on the actual DBMS product you are using ("SQL" is only a query language not the name of a database product). — , Jul 27 '18 at 07:32
It will probably be (much) easier to do that processing in the script that does the MySQL request (PHP or whatever) than in MySQL. Restructuring data is not a specific strength of SQL... — BurninLeo, Aug 03 '18 at 09:43

score 1 · Answer 1 · answered Jul 27 '18 at 02:15

You don't say which database you are using. I see two options here. Either you:

Produce a Pivot Table like in SQL Server, or
Group by person and produce a single column "favorite foods" using the LISTAGG() function to aggregate the favorite foods column.

Carsten Massmann · Answer 2 · 2018-07-27T10:01:42.223

What you are looking for us "pivoting" the data. You can find answers right here in Stackoverflow:

SQL-Server: How to pivot text columns in SQL Server?

MySql:

Select AA.uname,
 max(case when nr=1 then ffood end) ff1,
 max(case when nr=2 then ffood end) ff2,
 max(case when nr=3 then ffood end) ff3,
 max(case when nr=4 then ffood end) ff4
FROM  (select BB.*,@rownum := case when buid=@lu then @rownum else 0 end + 1 as nr,
   @lu:=buid
   FROM BB,(SELECT @lu := 0, @rownum :=0) r order by buid) P
INNER JOIN AA ON auid=buid
GROUP BY auid

Doing a pivot with Mysql is not really complicated, however, in this case we also need to generate a "row number" for the various favourite foods. Whereas in SQL-Server you will find a function for that, we need to work with user defined variables and a case clause in MySQL to achieve the same (column nr).

Here is a little demo: http://rextester.com/JDA89035

SQL Joins for repeating records

2 Answers2