如何找到长距离相关的表之间的关系? MySQL的 [英] How do I find relations between tables that are long-distance related? MySQL
问题描述
我需要通过加入这两个表在我的店更新,但我挣扎着找到好钥匙。如何撰写我的查询?
表表示业务关系/关联。你提到的关系是FK(外键),而不需要查询。他们声明某些列的子值也必须是某些关键列的子值。所需要的是要知道当它在一张桌子上时,现在的商业情况是怎么说的。 (其中,考虑到出现什么情况,确定FK和其他约束条件。)
From 需要在第三个表中将两个表与他们的FK连接起来:
lockquote
每个基表都带有一个谓词 - 由列名称参数化的语句模板。表的值是使谓词变为true语句的行。
查询也有一个谓词。它的值也是使谓词成立的行。它的谓词是根据它的FROM,WHERE和其他子句构建的。
(CROSS或INNER)JOIN在谓词之间放置AND; UNION把OR放在它们之间;除了插入AND NOT和ON&在哪里和在一个条件;当SELECT从T中删除一个列C时,它将T的谓词前面的某个(值为)C放入。 (其他运营商等)。
所以给出
- 其中product [id_product]由[id_supplier]提供的行...
ps_product(id_product,id_supplier,...)
- carrier [id_carrier]参考[id_reference] ...
ps_carrier(id_carrier,id_reference,...)
ps_product s
JOIN ...
ON s.id_product = ...
...
JOIN ps_carrier c
ON ... = id_carrier
WHERE ...
获取行
product [p.id_product]由[p。 id_supplier] ...
AND ...
AND s.id_product = ...
...
AND carrier [c.id_carrier]有参考[c.id_reference] ...
AND ... = id_carrier
AND ...
您需要知道您的表的谓词,然后将表ON或WHERE条件连接在一起,以便生成的谓词用于ro你想回来。
I have a problem with finding relations between tables ps_product and ps_carrier from a prestashop database. The schema is available at http://doc.prestashop.com/display/PS16/Fundamentals+of+PrestaShop+Development.
I need to make an update by joining these two tables in my shop but I'm struggling with finding good keys. How do I compose my query?
Tables represent business relationships/associations. The "relation[ship]s" you mention are FKs (foreign keys), and which are not needed for querying. They state that subrow values for some columns must also be subrow values for some key columns. What is needed is to know what a row says about the current business situation when it is in a table. (Which, given what situations arise, determine the FKs and other constraints.)
From Required to join 2 tables with their FKs in a 3rd table:
Every base table comes with a predicate--a statement template parameterized by column names. The table value is the rows that make its predicate into a true statement.
A query also has a predicate. Its value also is the rows that make its predicate true. Its predicate is built up according to its FROM, WHERE and other clauses.
(CROSS or INNER) JOIN puts AND between predicates; UNION puts OR between them; EXCEPT inserts AND NOT and ON & WHERE AND in a condition; when SELECT drops a column C from T, it puts FOR SOME (value for) C in front of T's predicate. (Etc for other operators.)
So given
-- rows where product [id_product] is supplied by [id_supplier] ...
ps_product(id_product, id_supplier, ...)
-- rows where carrier [id_carrier] has reference [id_reference] ...
ps_carrier(id_carrier, id_reference, ....)
we write
ps_product s
JOIN ...
ON s.id_product = ...
...
JOIN ps_carrier c
ON ... = id_carrier
WHERE ...
to get rows where
product [p.id_product] is supplied by [p.id_supplier] ...
AND ...
AND s.id_product = ...
...
AND carrier [c.id_carrier] has reference [c.id_reference] ...
AND ... = id_carrier
AND ...
You need know your tables' predicates then JOIN together tables ON or WHERE conditions so that the resulting predicate is for the rows you want back.
Is there any rule of thumb to construct SQL query from a human-readable description?
这篇关于如何找到长距离相关的表之间的关系? MySQL的的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!