CODEWITHSUNDEEP
mysqlsqljoinouter-join
pierreaurelemartin
pierreaurelemartin

Reputation: 1682

SQL join quite complicate but with fiddle

sorry for the poor title but i don't even know how to explain...

Here is the fiddle : http://sqlfiddle.com/#!2/f21e3/5

So i got a really basic table of products AND my data table (tyds in this case).

To make it simple. A product can be related to ZERO, ONE OR MORE tyd.

What a call a TYD is four line in tyds table. One tyd is identified by it's ref_tyd.

For the example, the product_2 is related to tyds N° 98 && 99.

The other products are not (yet) related to the tyds table.

What i'd like to get is all of the products information AND, if they are tyds related, the tyds information that came with it.

So, in my fiddle, here is the result i'm expecting:

ID_PRODUCT  NAME            REF_TYD         CNT_USER    ACTION_TYD
1           product_1       NONE            0           NONE
2           product_2       98              1           5
2           product_2       99              1           1
3           product_3       NONE            0           NONE

What changes:

Now i get the two TYDS (98 & 99) related to id_product = 2.

So now the CNT_USER line return 1 and 1 in both lines instead of suming two different things.

For the tyd N° 98, the action_tyd expected is 5

For the tyd N° 99, the action_tyd expected is 1

So what is happening is that SQL takes only the first tyd relation he found and then go to the next product_id. Logical but not what i want :)

Of course i could make a first query to get all the products and then, for each products, check the tyds table. But wouldn't be nicier in one shot ?

Thanks a lot four your help !

Upvotes: 2

Views: 66

Answers (2)

Shai
Shai

Reputation: 7307

OK.

To fix the first issue (where the two product_2 records are being merged), just change

GROUP BY id_product

to

GROUP BY id_product, ref_tyd

. The second bug is also simple to fix - you have said CASE WHEN action_tyd > 0 AND action_tyd < 5 THEN action_tyd END, so of course the action_tyd of 5 is not included! Just change this to <= 5.

Upvotes: 2

Fabian
Fabian

Reputation: 2982

This gives you the expected result (notice the added GROUP BY column):

SELECT 
    products.id_product AS id_product, 
    products.name_product AS name, 
    COALESCE(tyds.ref_tyd, "NONE") AS ref_tyd, 
    SUM(CASE WHEN action_tyd = 11 THEN 1 ELSE 0 END) AS cnt_user, 
    COALESCE(MAX(CASE WHEN action_tyd > 0 AND action_tyd <= 5 THEN action_tyd END), "NONE") AS action_tyd 
    FROM products
    LEFT JOIN tyds ON tyds.product_tyd = products.id_product
    GROUP BY id_product, ref_tyd

Edit Furthermore, I have changed < 5 to <= 5, which seems to be what you want.

Upvotes: 1

Related Questions