help with mysql join

[ericleeclark]

Hi all,

 

I've got two tables, one with product information (xcart_products) and the other that stores related products (xcart_product_links). They are related by the PK of productid and productid2. I have successfully created a query to join the two tables and get pretty close to what I need. However, the values in the xcart_product_links table are not exactly what I need, because they relate back to the SKUs in the xcart_products table. My end goal is to generate a simple list of product SKUs in one field, and a concatenated list of related products in another field.

 

Here's my query, and examples of the two tables I'm working with:

 

SELECT `productcode`, `product`, GROUP_CONCAT(xcart_product_links.`productid2` SEPARATOR '|')
FROM `xcart_products`
INNER JOIN `xcart_product_links`
ON `xcart_products`.`productid`=`xcart_product_links`.`productid1`
GROUP BY xcart_products.`productcode`

 

The xcart_product table contains several fields, but I'm only interested in the productid and productcode (the SKU #). The xcart_product_links table contains two fields, productid1 and productid2, bot relate to productid in xcart_products. The problem I'm running into is that my export needs to contain a list of SKU #'s and then in a second field a list of related SKU #'s separated by |. Like this: SKU1234;SKU3904|SKU2342|SKU9283|SKU83942

 

I've attached screenshots of these two tables for clarity, and what my current query is generating as a result. Any help is appreciated!

 

[attachment deleted by admin]

[ericleeclark]

Maybe it would help if I simplified the issue. I want to get the linked SKUs but I'm only getting the linked IDs?

 

Table 1 has ID (primary key) and SKU. Table 2 has ID1 and ID2. ID1 is relates to ID in Table 1. Table 2 is simple, showing the relationship between ID1 and ID2 row by row. ID1 repeats several times while ID2 changes, which builds the related products.

 

My end goal here is to create a query that returns a list of SKU numbers for Table 1 along with a concatenated list of related SKU's. The concatenated list of related SKU's is where I'm hung up, because I've only been able to pull back the IDs in the concatenation.

 

Again, any help here is greatly appreciated. Thanks!

[fenway]

Then you simply need to join back to the SKU->ID table twice.

[ericleeclark]

Thanks, fenway. I was able to complete it with a second join as you suggested. Here was my final query:

SELECT p1.productcode, p1.product, GROUP_CONCAT(p2.productcode SEPARATOR '|')
FROM xcart_products AS p1
INNER JOIN xcart_product_links AS l1 ON p1.productid=l1.productid1
INNER JOIN xcart_products AS p2 ON p2.productid=l1.productid2
GROUP BY p1.productcode;