How do I find relations between tables that are lo

Question

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?

Answer 1

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?