如何找到长距离相关的表之间的关系? MySQL的 [英] How do I find relations between tables that are long-distance related? MySQL

查看:231
本文介绍了如何找到长距离相关的表之间的关系? MySQL的的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我有一个从prestashop数据库找到表ps_product和ps_carrier之间的关系的问题。该模式可在 http://doc.prestashop.com/display / PS16 /基础+ + PrestaShop +开发

我需要通过加入这两个表在我的店更新,但我挣扎着找到好钥匙。如何撰写我的查询?

表表示业务关系/关联。你提到的关系是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你想回来。



是否有任何经验法则来构建SQL查询从一个可读的描述?


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屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆