CODEWITHSUNDEEP
mysqlsql
Omid
Omid

Reputation: 4705

Selecting rows from multiple tables. How?

Let's say that we have tree tables.

Products                  Fields                Fields Value
----------------          -------------         --------------
  pid    catid             fid   catid            fid   pid   value
-------|-------|          -----|-------         ------|-----|--------
   1       1                1      1               1     1     25%
   2       1                2      1               1     2     32.5%
                            3      2               2     1     45%
                                                   2     2     42%
                                                   3     1     17.3%
                                                   3     2     21%

The normal way is selecting Products in a one query and loop through result set(RS1).
Then we select Fields for catid per each row (RS2). Then doing the same action with RS2 for selecting `Fields Value'.

Only problem is performance issue that will be reduced due to executing a lot of queries` when there are a lot of rows in each table.

Would you suggest me better solution to execute less queries ?

edit
I want to show each product in a box and show fields for each product with it's proper value. joining tree tables together will returns duplicated values for each FieldValue in Products and not usable in loop.

Upvotes: 0

Views: 127

Answers (4)

Andomar
Andomar

Reputation: 238078

You can join the tables together using left join:

select *
from Products p
left join Fields f on f.catid = p.catid
left join `fields value` fv on fv.fid = f.fid on fv.pid = p.pid
where p.pid = 1

Upvotes: 0

onedaywhen
onedaywhen

Reputation: 57023

SELECT * 
  FROM Products
       NATURAL JOIN Fields
       NATURAL JOIN FieldsValue;

Upvotes: 1

Marco
Marco

Reputation: 57573

Guessing what you need, try this:

SELECT f.catid, fv.* FROM Fields f
INNER JOIN Products p 
  ON f.catid = p.catid
INNER JOIN FieldsValue fv
  ON fv.fid = f.fid AND fv.pid = p.pid

Upvotes: 2

amd
amd

Reputation: 21442

use Join Syntax : 

SELECT * FROM Products as P
LEFT JOIN FieldsValue as FV ON FV.PID = P.PID
LEFT JOIN Fields as F on F.fid = FV.fid

Upvotes: 0

Related Questions