It's totally logical...
What you ask the DB, is to return you the fields from the tables transactions, transaction_details, product_items and branches, but you don't tell the db how to match the records from all those tables.
This imply an cross join, as know as a cartesian product.
In short, each row of each table is matched with every rows of all the other tables.
Result: probably a couple of millions of rows.
2 things to do:
1) Drop the "select x from tbl1, tbl2, tbl3" syntax. It's hard to read and to understand.
Use the sql92 syntax:
Code:
select
t.*,
td.*,
pi.name as product_name,
b.name as branc_name
from transactions as t
inner join transaction_details as td on td.transactionId=t.transaction
inner join product_items as pi on td.productId=pi.productId
inner join branches as b on pi.branchId=b.branchId
where t.retailer_id=1
and t.transaction_id=7
Of course, I have extrapolated the link between your tables here, but you get the idea.
This is the currently admited best practice when it come to write an sql query.
2) use joins:
You have basically 3 types of joins:
* cross join => each rows of each tables are matched to every rows of the other. Basically, you never want this, except in rare cases, where you specifically know what you are doing.
* inner join => the rows of the first table are matched with the rows of the second table on the condition expressed by the "on t1.id=t2.id"
Only records having such a relation are shown. If a record cannot be matched between the 2 tables, it's excluded from the resultset
* outer join => like the inner join, except that you can specify 1 of the 2 tables (the left, ie the first or the right, ie the second) as a reference table.
The matching is done on the "on t1.id=t2.id" relation too, but if a record from the reference table doesn't have a match with the second one, it's included in the result nontheless.
For example, in the query up here.
If each articles have a branch, then the inner join is ok, because every articles will be returned.
But if some articles don't have a branch, then those would not be displayed if we use an inner join.
We would have to use an outer join to do so.
The reference table would be the article table, so the qery would be rewrote:
Code:
select
t.*,
td.*,
pi.name as product_name,
b.name as branc_name
from transactions as t
inner join transaction_details as td on td.transactionId=t.transaction
inner join product_items as pi on td.productId=pi.productId
left outer join branches as b on pi.branchId=b.branchId
where t.retailer_id=1
and t.transaction_id=7
left outer join, because the table articles is defined before the table branches.
With that query, every articles would be returned, even when no branches are defined for them.
Just be vigilant that you don't mix "inner join" and "outer join" when you write the queries.
The "outer join" should always be the last. Do not ever define an "inner join" after an "outer join". The db engine should be smart enough to rewrite the query internally to avoid this, but you never know...
!
