-1

I'm pretty new to SQL/PHP and I'm trying to LEFT JOIN two tables with the second table LEFT JOINed twice. Here the scenario: I have two tables, products and productvariations. In the productvariations table I also have a column 'colorvalue' that tells the user whether it's the standard color or a special color for the products.

products

idproducts | product | checked
-------------------------------
1          | testpr1 | 0
2          | testpr2 | 0
...

productvariations

productid | colorvalue | price
-------------------------------
1         | 0          | 12.50
1         | 1          | 10.25
2         | 0          | 14.50
2         | 1          | 13.00
...

Now what I want to do is to SELECT all columns of products and LEFT JOIN to them the twice the productvariations table to have two different additional columns for the price. But only those products that have the checked value = '0'. From different other Q&A I set up this code, but somehow it doesn't give me any results. Can someone help?

SELECT products.*, productvar1.price_single, productvar1.price_palette, productvar2.price_single, productvar2.price_palette, productvar1.price_specialconditions, productvar1.price_colorchange 
FROM products 
LEFT JOIN productvariations AS productvar1 
ON products.idproducts = productvar1.product AND productvar1.colorvlaue = '0' 
LEFT JOIN productvariations AS productvar2 
ON products.idproducts = productvar2.product AND productvar2.colorvalue = '1'
WHERE products.checked = '0' LIMIT 200;
iMorces
  • 11
  • 3
  • In where exactly that you didn't receive any result? Running the code in MySQL server (using a tool like phpmyadmin etc.) or from the frontend of your php code? – FanoFN Jul 22 '21 at 09:19
  • Your code looks ok. – Serg Jul 22 '21 at 09:20
  • you probably don't need 2 left joins you can simply do `productvar1.colorvlaue in ('0', '1')` – Harsh Gundecha Jul 22 '21 at 09:21
  • @FaNo_FN Sorry, that's the frontend of my php code. After putting the query into a variable $query I check with if ($result = mysqli_query($conn, $query)) whether there are matches. But it always automatically goes to the else statement. – iMorces Jul 22 '21 at 09:26
  • @Harsh Gundecha How would that work exactly? I'm sorry, I'm new to SQL. Since I need the same column twice in my result I figured I have to do two joins. Would I take productvar2 out completely then and just mention twice productvar1.price_single and productvar1.price_palette? – iMorces Jul 22 '21 at 09:28
  • 2
    Maybe it's because the typo in your query? This > `AND productvar1.colorvlaue = '0'`.. it's probably should be `AND productvar1.colorvalue = '0'` because if I try that, your query seems to be working. See here > https://www.db-fiddle.com/f/v3hB6Krrmwz88mhcdPi6i6/1 – FanoFN Jul 22 '21 at 09:28
  • @iMorces `productvar1.colorvlaue in ('0', '1')` basically means checking the `productvar1.colorvlaue` for having either `0` or `1`, which is what you seem to be doing in 2 different join as far as i can understand – Harsh Gundecha Jul 22 '21 at 09:32
  • @FaNo_FN That's it! Thanks man. How stupid of me for not finding this to simple mistake. Now it works perfectly. Thank you so much again. You saved me a lot of nerves. – iMorces Jul 22 '21 at 09:34
  • @Harsh Gundecha Thanks for the info, that's new to me too. I'll have a closer look into it to see whether it's fitting in my case. – iMorces Jul 22 '21 at 09:35
  • @FaNo_FN Now I got another problem though. With the second LEFT JOIN. What I didn't expect to happen like that is that since there are several entries in the productvariations table with the same product ID and the colorvalue = '1' it gives me a list of all the productvariations matching the query. But I only need the MIN() of it. Found posts like this stackoverflow.com/questions/11388443/… But I can't wrap my head around how to apply it. Can you help? – iMorces Jul 22 '21 at 13:59
  • I suggest that you post a new question @iMorces. That would be better and will attract more people to help you. – FanoFN Jul 22 '21 at 21:52

1 Answers1

0

Your query had some typos and of course there was a column in the select clause that I did not see in the table, but now the following query works according to the information in the table.

SELECT products.*, 
       productvar1.colorvalue as colorvalue1,
       productvar1.price as price1,
       productvar2.colorvalue as colorvalue2,
       productvar2.price as price2
FROM PT1 products 
LEFT JOIN productvariations AS productvar1 
ON products.idproducts = productvar1.productid AND productvar1.colorvalue = '0' 
LEFT JOIN productvariations AS productvar2 
ON products.idproducts = productvar2.productid AND productvar2.colorvalue = '1'
WHERE products.checked = '0' LIMIT 200;

demo in db<>fiddle

Meysam Asadi
  • 6,438
  • 3
  • 7
  • 17
  • Thanks for the reply. I got another problem though. With the second LEFT JOIN. What isn't mentioned above is that in the productvariations list there can be several matches with the same product and colorvalue = '1'. Now it shows all of the rows matching, but I only want the MIN() of it. Found posts like this https://stackoverflow.com/questions/11388443/limiting-a-left-join-to-returning-one-result But I can't wrap my head around how to apply it. Can you help? – iMorces Jul 22 '21 at 12:52
  • Yes I can help you with a positive vote :) Just enter the sample data and desired output – Meysam Asadi Jul 22 '21 at 14:37