O
O
Oleg Belyay2017-12-08 23:05:40
SQLite
Oleg Belyay, 2017-12-08 23:05:40

Sqlite selection from multiple tables, truncated by smaller table. What request to make?

Sqlite selection from several tables, truncated by the number of documents, goods. What request to make?
There are tables of goods and documents.
When joining the table, the number of products returned by the query is equal to the number of documents, although there are more products in the documents.

SELECT docs.id as docId, docs.removed as docsRemoved, docTypes.removed as docTypesRemoved, docs.date, docTypes.name as nameDoc, rows.quantity, products.name
FROM products, docs 
LEFT OUTER JOIN  rows ON products.id = rows.productId  
LEFT OUTER JOIN  docTypes   ON docTypes.id = docs.typeId and docTypes.removed = 0 
WHERE rows.docId = docs.id  and docs.removed = 0 
GROUP BY docs.date

Tables:
● docs - Documents
id - Document identifier (its number)
date - Document creation date
typeId - Document type (docTypes.id)
removed - Indicates that the document has been removed
● rows - Document rows
id - Document
row identifier docId - Document identifier , which owns the line (docs.id)
productId - Product identifier (products.id)
quantity - Quantity of goods
removed - Indicates that the line has been removed
● docTypes - Document types
id - Document type identifier
name - Document name
removed - Indicates that this type of document is not used
● products - Products
id - Product identifier
name - Product name
image - Product image URL
price - Product cost in rubles
removed - A sign that the product is on sale

Answer the question

In order to leave comments, you need to log in

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question