Thesql join many to one table and display each ‘many’ record as an additional column

MySQLrelations

I have a many to one table.

For example:

user_id, name
1, chris
2, john

and

user_id, item
1, apple, 
1, banana,
2, orange

How could I run a query that returns something like this:

user_id, item_1, item_2, item_3
1, apple, banana, null
2, orange, null, null

Would I need to do a sub query for each item_n column?

Best Answer

As Kondybas suggests in his comment, you can use GROUP_CONCAT to concatenate the columns

SELECT user_id, GROUP_CONCAT(item,',') as items 
FROM table_name 
GROUP BY user_id

Note that all original columns is fit into one column in the answer.

Related Solutions

Mysql – remove duplicate rows in thesql table that does not contain primary key

In the spirit of @yercube's answer, I have an answer that has an added twist.

CREATE TABLE stage
(
    id int not null auto_increment,
    name varchar(20),
    primary key (id)
);
CREATE TABLE stage2 LIKE stage;
INSERT INTO stage (name) SELECT name FROM item;
INSERT INTO stage2 (id) SELECT min_id FROM
(SELECT MIN(id) min_id,name FROM stage GROUP BY name) A;
UPDATE stage2 A INNER JOIN stage B USING (id) SET A.name=B.name;
TRUNCATE TABLE item;
INSERT INTO item (name) SELECT name FROM stage2;
DROP TABLE stage;
DROP TABLE stage2;

This will load stage2 with the first occurrence of each name from item, zap the item table, and load the unique occurrences back.

If you look back in @yercube's answer and compare it to my answer, his is much more simplistic because

@yercube uses one temp table, while I use two
I had to create a column for iteration control, @yercube did not need to
@yercube has fewer steps
both answers achieve the same thing

I do not expect my answer to be accepted. The sole purpose of my answer was demonstrate that other answers lose the concise clarity needed to solve your problem. Again, hats off to @yercube.

Mysql – Select only rows that equal x but not x and y

The question is not very clear. If what you want is to have as result **every object that is related to name 'orange' but not to name 'banana', here is one way:

SELECT o.*                  -- Change this to only the columns needed.
FROM objects AS o           -- If there are data needed from other tables,
                            -- join here.
WHERE EXISTS 
      ( SELECT 1 
        FROM name_relationships AS nr
          JOIN name_data AS nd USING (data_id)
          JOIN name AS n  USING (name_id)
        WHERE nc.name = 'orange' 
          AND n.object_id = o.object_id
      )
  AND NOT EXISTS 
      ( SELECT 1 
        FROM name_relationships AS nr
          JOIN name_data AS nd USING (data_id)
          JOIN name AS n  USING (name_id)
        WHERE n.name = 'banana' 
          AND nr.object_id = o.object_id
      ) ;

Best Answer

Related Solutions

Mysql – remove duplicate rows in thesql table that does not contain primary key

Mysql – Select only rows that equal x but not x and y

Related Question