0

I have problem with SQL query as it returns multiple rows, I am looking to get values only from

ps_tax t

any one can help me?

SELECT bgc.id_product,bgc.id_order, tx.id_tax_rules_group,t.id_tax,pl.name,t.rate as rate, brl.price as gift_price
FROM ps_bestkit_gift_cart bgc
LEFT JOIN ps_bestkit_gift_rule brl ON brl.id_bestkit_gift_rule = bgc.id_bestkit_gift_rule
LEFT JOIN ps_product_lang pl ON bgc.id_product = pl.id_product
LEFT JOIN ps_product p ON pl.id_product = p.id_product
LEFT JOIN ps_tax_rule tx ON p.id_tax_rules_group = tx.id_tax_rules_group
LEFT JOIN ps_tax t ON tx.id_tax = t.id_tax
WHERE id_order =8452
AND pl.id_lang=3
AND tx.id_country=6

There is a screenshot of my result

The DDL

CREATE TABLE ps_bestkit_gift_rule ( id_bestkit_gift_rule int(11) unsigned NOT NULL AUTO_INCREMENT, is_product_depends tinyint(1) unsigned DEFAULT NULL, is_supplier_depends tinyint(1) unsigned DEFAULT NULL, is_manufacturer_depends tinyint(1) unsigned DEFAULT NULL, is_category_depends tinyint(1) unsigned DEFAULT NULL, is_attribute_depends tinyint(1) unsigned DEFAULT NULL, is_feature_depends tinyint(1) unsigned DEFAULT NULL, is_allow_other_gift tinyint(1) unsigned DEFAULT NULL, cart_amount decimal(17,2) unsigned NOT NULL, max_cart_amount decimal(17,2) unsigned NOT NULL, price decimal(17,2) unsigned NOT NULL, min_qty_inside_category int(10) unsigned NOT NULL DEFAULT 0, min_price_inside_category decimal(17,2) unsigned NOT NULL, from_date date NOT NULL, to_date date NOT NULL, criteria_order_type varchar(32) NOT NULL, criteria_max_per_customer int(11) unsigned NOT NULL, criteria_nb_products int(11) unsigned NOT NULL, gift_preselector_product_page tinyint(1) unsigned DEFAULT NULL, available_gifts int(10) unsigned NOT NULL DEFAULT 1, criteria_coupon text, criteria_customer_group text, message tinyint(1) unsigned DEFAULT NULL, active tinyint(1) unsigned DEFAULT NULL, position int(10) unsigned NOT NULL DEFAULT 0, PRIMARY KEY (id_bestkit_gift_rule) ) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8

Jakub
  • 1
  • 1
  • 1
    If we assume you want non-null values from ps_tax; then make your left joins inner joins and only select ps_tax.* The left joins ensure you have all records from bgc and that match brl and all from brl that match pl and so on. which means you'll likely have several null values from ps_tax. However, the question is unclear as its written. Include sample of what this returns, and what you need it to return (desired results) to clarify your question. the 1:M relationshhips between these tables could be causing the issue. Or perhaps you need to make the joins right instead of left... – xQbert Sep 20 '17 at 12:10
  • SO is a great site to ask questions and get answers: Just be sure to provide a complete [MCVE](https://stackoverflow.com/help/mcve). Before you ask a question consider [talking to the duck first](https://blog.codinghorror.com/rubber-duck-problem-solving/). Yes, I'm serious! To help us help you, generate sample data and expected results. [ascii table](https://ozh.github.io/ascii-tables/) makes the output easy to read! You could also mock up data and the SQL tried using http://rextester.com/ or a similar site. Pretty much anything is possible; it's a matter of should we do it; not can we. – xQbert Sep 20 '17 at 12:14
  • it still returns duplicate values – Jakub Sep 20 '17 at 12:17
  • Include sample of what your current query returns, and what you need it to return (desired results) to clarify your question. As it stands the question is too ambiguous to be able to answer. Understand that the data is likely duplicated because of the 1:M relationships between the other tables. It is the data in the other tables which may be causing the "duplicates" like if ps_tax_rule lists a id_tax multiple times, then ID_Tax will be repeated at least once for each tax_rule. – xQbert Sep 20 '17 at 12:18
  • The screenshot doesn't match the query. You're missing PL_name. That aside it seems duplication may be caused by missing join criteria. Without knowing the PK FK of all tables involved I can't say what criteria is missing. I'd guess it's in the BRL tables relations to other tables based on the fact gift_price has two values for each order. So ***does ps_bestkit_gift_rule have a FK to tables other than ps_bestkit_gift_cart*** ? – xQbert Sep 20 '17 at 12:33
  • yes, I just removed the pl.name it is unnecessary, yes ps_bestkit_gift_rule have FK to ps_bestkit_gift_cart table which is id_bestkit_gift_rule – Jakub Sep 20 '17 at 12:38
  • Can we see the DDL for ps_bestkit_gift_rule? My assumption is you're missing a join criteria which is causing the duplication. Additional criteria to ps_bestkit_gift_cart or perhaps one of the other tables. It could also be you're missing a structural component which would prevent this duplication as well. I'm basically after the PK's and FKs on ps_bestkit_gift_rule to make sure all the joins are being done correctly (on the keys) – xQbert Sep 20 '17 at 12:39
  • sorry, but how can I see the DDL ? – Jakub Sep 20 '17 at 12:43
  • https://dev.mysql.com/doc/refman/5.7/en/show-create-table.html OR https://stackoverflow.com/questions/4004205/show-constraints-on-tables-command ... Giving us: `SHOW CREATE TABLE ps_bestkit_gift_rule` DML is abbreviation of Data Manipulation Language. It is used to retrieve, store, modify, delete, insert and update data in database. DDL is abbreviation of Data Definition Language. It is used to create and modify the structure of database objects in database. – xQbert Sep 20 '17 at 12:45
  • `SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME FROM KEY_COLUMN_USAGE WHERE AND TABLE_NAME = 'ps_bestkit_gift_rule' AND REFERENCED_COLUMN_NAME IS NOT NULL;` may be needed as it appears the show tables may not include the constraints I'm after. https://stackoverflow.com/questions/201621/how-do-i-see-all-foreign-keys-to-a-table-or-column – xQbert Sep 20 '17 at 12:52
  • based on the DDL I don't see anything more that is likely a fk to another table. However I do see two things which may cause the duplicate. from_date, to_date... Does the ps_bestkit_gift_cart have a date on it which must be between the from and to dates of ps_bestkit_gift_rule? the 2nd element is does active play a role in if that rule should be used? (I think if you include brl.* in your results and display those we can get it from there.) I'd guess the from/to dates or the active needs to be an additional limit on the join somehow. – xQbert Sep 20 '17 at 12:58
  • No , the ps_bestkit_gift_cart doesnt not have a date on it – Jakub Sep 20 '17 at 13:04
  • Do you understand why I'm looking at ps_bestkit_gift_rule? the fact you have two different values in gift_price which is sourced from brl.price is why. This means that the join `brl.id_bestkit_gift_rule = bgc.id_bestkit_gift_rule` is causing 2 records to be picked up from that table when you only expect 1. – xQbert Sep 20 '17 at 14:24
  • i think I understand the problem now, thank you ! – Jakub Sep 20 '17 at 15:21

2 Answers2

0

Based on the screenshot and your comment that you only want two values out of query which are id193 with rate 21 and 1539 with rate 0 I'd surmise you are either:

  1. Missing join criteria between ps_bestkit_gift_rule and one or more of the other tables.
  2. Missing limiting criteria on ps_bestkit_gift_rule itself.
  3. Missing join criteria on ps_tax Most likely after re-reviewing issue. I didn't account for the left join on ps_beskit_gift_rule originally.
  4. Missing limiting criteria on ps_tax

.

+------------+----------+--------------------+--------+--------+------------+
| ID_Product | ID_order | ID_TAX_RULES_GROUP | ID_TAX |  Rate  | Gift_price |
+------------+----------+--------------------+--------+--------+------------+
|        193 |     8452 |                  4 |      4 | 21.000 |  $5.00     |
|       1539 |     8452 |                  4 |      4 | 21.000 |  $-        |
|        193 |     8452 |                  4 |      7 |  0.000 |  $5.00     |
|       1539 |     8452 |                  4 |      7 |  0.000 |  $-        |
+------------+----------+--------------------+--------+--------+------------+

So teh ID_TaX and Gift_price and rate all have different values I may be looking at the wrong table after all... the NULLs on gift_price may be a result of the left join which means the Two values for ID_TAX and RATE from ps_tax may be the table having the improper join! I'd take a look at the PK/FKs on that table!

xQbert
  • 34,733
  • 2
  • 41
  • 62
0

I think GROUP BY will do the job.

SELECT bgc.id_product,bgc.id_order,
tx.id_tax_rules_group,t.id_tax,pl.name,t.rate as rate, brl.price as gift_price 
FROM ps_bestkit_gift_cart bgc
LEFT JOIN ps_bestkit_gift_rule brl ON brl.id_bestkit_gift_rule = bgc.id_bestkit_gift_rule 
LEFT JOIN ps_product_lang pl ON bgc.id_product = pl.id_product 
LEFT JOIN ps_product p ON pl.id_product = p.id_product 
LEFT JOIN ps_tax_rule tx ON p.id_tax_rules_group = tx.id_tax_rules_group 
LEFT JOIN ps_tax t ON tx.id_tax = t.id_tax 
WHERE id_order =8452 
AND pl.id_lang=3 
AND tx.id_country=6
GROUP BY t.id_tax
Teodor Stoynov
  • 1
  • Group by w/o aggregation really doesn't make sense. Maybe a distinct;but given the other fields tax duplicate tax information could still be presented. but hey maybe I don't understand the question yet. – xQbert Sep 20 '17 at 12:28
  • i just added the image of my result, I want to get only two values out of query which are id193 with rate 21 and 1539 with rate 0 – Jakub Sep 20 '17 at 12:31