Shaping dataset in MySQL with a multiple inner join

Question

I am learning MySQL and working with some dummy tables that I set up from an example online.

The code to create the tables wasn't given, just the end result of what they should look like, so I did my best to re-create them. I know that their design isn't ideal - I'd rather use something like ENUM for the customer_info.state column, and some type of DATETIME with getdate() for the dates, for example, but haven't had success with those (more appropriate setups) yet so I'm working on JOINS first, as they're relevant to my job. I'll get to the fine-tuning on design later (my job will involve a lot of querying and joining, not a lot of DB design, although I'd eventually like to learn better habits/skills in that arena).

I am currently practicing my INNER JOIN, and have successfully joined three tables, but the result set isn't shaped the way I'd like.

Currently, my three tables are:

customer_info (customerid INT NOT NULL, firstname VARCHAR(20) NOT NULL, lastname VARCHAR(20) NOT NULL, city VARCHAR(25), state VARCHAR(2))

purchases (customerid INT NOT NULL, order_month_day VARCHAR(10), order_year INT NOT NULL, item VARCHAR(50) NOT NULL, quantity INT NOT NULL, price FLOAT NOT NULL)

customer_favorite_colors (customerid INT NOT NULL, favorite_color VARCHAR(20) NOT NULL)

I put this query together to join the three tables and display some of the particular columns:

SELECT customer_info.customerid, customer_info.firstname, customer_info.lastname, customer_info.city, customer_info.state, purchases.item, customer_favorite_colors.favorite_color

FROM customer_info

INNER JOIN purchases ON customer_info.customerid = purchases.customerid

INNER JOIN customer_favorite_colors ON purchases.customerid = customer_favorite_colors.customerid

ORDER BY customer_info.customerid;

And my result set lists everything I was looking for, except that it's formatted like:

So, how can I ask SQL to show me each of these fields, but more like:

I think it has something to do with using DISTINCT, but I can't for the life of me figure out how to make it work.

Also, if there are any differences in the solution for this in MySQL vs SQL Server 2012, that would be very helpful as well (we use SQL Server 2012 at work).

Answer 1

What if you add a GROUP BY to your existing query like

    SELECT customer_info.customerid, customer_info.firstname,
    customer_info.lastname, customer_info.city, customer_info.state,
   GROUP_CONCAT( purchases.item), customer_favorite_colors.favorite_color
    FROM customer_info
    INNER JOIN purchases ON customer_info.customerid = purchases.customerid
    INNER JOIN customer_favorite_colors 
    ON purchases.customerid = customer_favorite_colors.customerid
    GROUP BY customer_info.customerid, customer_info.firstname,
    customer_info.lastname, customer_info.city, 
    customer_info.state, customer_favorite_colors.favorite_color
    ORDER BY customer_info.customerid;

Answer 2

In the example, why does Lantern have associated customer data but not the others? If this is what you want exactly then we need more info on how to choose then I can update this with the answer.