How to search data from multiple tables using Mysql?

Question

I have three tables : bldr_prjct,bldr_prjct_attr,bldr_prjct_attr_ref;

i want to get bldr_prjct data where bldr_prjct attributes are stored in bldr_prjct_attr. bldr_prjct_attr_ref : In this table i have defined values all attributes related to projects :

Screenshots : bldr_prjct

bldr_prjct_attr_ref :

bldr_prjct_attr :

My query :

SELECT 
    `p`.`ID`,`p`.`PRJCT_NM`,`p`.`SLUG`,`p`.`STS_CD`,
    `p`.`PRJCT_GEO_LT`,`p`.`PRJCT_GEO_LG` 
FROM 
    `bldr_prjct` `p`, `bldr_prjct_attr_ref` `pr`, `bldr_prjct_attr` `pa` 
WHERE 
    `pa`.`REF_ID` IN (SELECT `ID` FROM `bldr_prjct_attr_ref` WHERE `PRNT_ID`=3)

have a look at this, this should give you some idea. http://stackoverflow.com/questions/10023371/how-can-i-select-data-from-multiple-mysql-tables-in-this-scenario?rq=1 — piyushj, May 17 '16 at 08:52
Take a look [here](http://stackoverflow.com/questions/905379/what-is-the-difference-between-join-and-union) — ElTête, May 17 '16 at 08:58

score 0 · Answer 1 · answered May 17 '16 at 08:56

I might be missing something, but your tables don't appear to have any common reference. For instance, table bldr_prjct_attr_ref does not include any field pointing to the relevant record in table bldr_prjct. If my understanding is correct, you will have to alter these tables to allow some time of cross-referencing. For instance, add to table bldr_prjct_attr_ref a field (column) bldr_prjct_ID that points to the corresponding record in table bldr_prjct. There is one field in the third table that appears to reference a record in the first, but when you issue a SELECT of three tables and one of them returns an empty result, then the overall select returns empty.

score 0 · Accepted Answer · answered May 17 '16 at 08:59

0

Firstly, don't use implicit JOIN syntax(comma separated) , use the proper syntax of join, that will help you avoid this kind of mistakes.

You query is not working because you are missing the join relations , you may have to adjust it a bit, i guessed bldr_prjct is joined to bldr_prjct_attr_ref by id=prnt_id although you have prnt_id column in this table, so change it if needed.

SELECT `p`.`ID`,`p`.`PRJCT_NM`,`p`.`SLUG`,`p`.`STS_CD`,`p`.`PRJCT_GEO_LT`,`p`.`PRJCT_GEO_LG`
FROM `bldr_prjct` `p`
INNER JOIN `bldr_prjct_attr` `pa`
 ON(`p`.id = `pa`.prnt_id )
INNER JOIN `bldr_prjct_attr_ref` `pr`
 ON(`pa`.ref_id = `pr`.id and `pr`.prnt_id = 3 )

answered May 17 '16 at 08:59

sagi

40,026
6
59
84

This query is working but has some issue i have two row with same ref_id in `bldr_prjct_attr` then it gives first row with two time. – Manish Tiwari May 17 '16 at 09:07
`bldr_prjct ` is joined to `bldr_prjct_attr ` and `bldr_prjct_attr` is joined to `bldr_prjct_attr_ref`. – Manish Tiwari May 17 '16 at 09:10
So? What is the problem with two rows? its a duplicate row, E.G. all columns are the same? @ManishTiwari – sagi May 17 '16 at 09:11
Thanks Mate, it's working. i was doing something wrong so that's why it gives duplicate rows. Can you explain me how it works ? – Manish Tiwari May 17 '16 at 09:34
This basically join each table by the connection specified inside the `ON` clause. – sagi May 17 '16 at 10:00

How to search data from multiple tables using Mysql?

2 Answers2