Insert results of query into another query

Oh man, I just can’t figure out how to write this query.
Any guidance will be highly appreciated.

I have the following tables:

Specification_Title
id
title (e.g. position, design)

Specification_Name
id
name (e.g. left, right)
spec_title_id

Products
id
name (e.g. headlights)
dataEntryUserId
dateOfEntry

Product_Spec_Link
id
specnameid
productid

I want to fetch all the products where

dataEntryUserId = X
dateOfEntry = Y

Which is simple to do with

SELECT * FROM products WHERE dataEntryUserId = "X" AND dateOfEntry = "Y"

But I just can’t figure out how to get the following output

Product Name: Headlight
dataEntryUserId: X
dateOfEntry: “Y”
Specifications: Position (Left), Position (Right)

I am just wondering if this can be done in SQL or do I have to somehow use PHP to store the results of multiple INNER JOIN queries and output it in the final table (which I also can’t figure out).

Can you kindly assist?

SELECT p.name AS "Product Name"
     , GROUP_CONCAT( CONCAT(st.title, ' (', sn.name, ')' ) 
                   SEPARATOR ', ' ) AS "Specifications" 
  FROM products AS p
INNER
  JOIN product_spec_link AS ps
    ON ps.productid = p.id  
INNER
  JOIN specification_name AS sn
    ON sn.id = ps.specnameid
INNER
  JOIN specification_title AS st
    ON st.id = sn.spec_title_id      
 WHERE p.dataEntryUserId = 'X' 
   AND p.dateOfEntry = 'Y'
GROUP
    BY p.name   
1 Like

Many thanks buddy!
Learnt something new today.
I have to explore SQL functions a bit more.
GROUP_CONCAT worked like a charm :slight_smile:
Can I get you a beer (paypal)?

thank you for the offer but the best thing you could do is pay it forward and help other people who want to learn SQL

:beer::beer::beer::beer::beer::beer::beer:

2 Likes