Is this SQL query/join possible?

Question

I am learning SQL and I am looking at SQL joins at the moment. Playing around with different joins in MySQL and I was wondering if this would be possible, to have it all in 1 column.

DB has 3 tables:

shops
prices
products

Products looks like:

product_id
product_name

Shops looks like:

shop_id
shop_name

Prices look like:

price_id
price_amount
product_id
shop_id

So the big question is: is it possible to combine it to have it output like: product_id,product_name,price_shop1,price_shop2,price_shop3

I got so far that my output is like product_id,price_amount,shop_id,shop_name, but on 3 different rows.

Can it be done? What kind of join am I looking for if it is possible?

I do not see where I could be using this, just trying to learn.

Thanks.

Regards, Mike

Sample data and expected result would be help.. – dwir182 Oct 31 '18 at 22:19 — dwir182, Oct 31 '18 at 22:19
Do you want it all in 1 column or 1 row? – Barmar Oct 31 '18 at 22:21 — Barmar, Oct 31 '18 at 22:21

score 0 · Accepted Answer · answered Oct 31 '18 at 22:21

Not exactly, we are talking about making a request with a dynamic number of columns, because you don't know beforehand how many shops with that product are there. This is not supported.

But you can have a single column with all the prices concatenated and comma-separated (or any other symbol you choose).

For example:

SELECT
    a.product_id,
    a.product_name,
    GROUP_CONCAT(b.price_amount SEPARATOR ';') prices
FROM
    products a INNER JOIN prices b ON a.product_id = b.product_id
GROUP BY a.product_id

This should yield you a list of the products and a list of all their available prices, though in the form of a string that you will have to parse.

If you also wish to include the shops those products are in, I advise you not to do that. Since some parsing is needed either way, just get the full list, which will include product duplicates but you can sort by product_id to deal with that easily.

Is this SQL query/join possible?

1 Answers1