My problem is as follows:
I have 3 tables with content:
tableA:|ID_A|field1|field2|field3| => base table
tableB:|ID_B | ID_C |ID_A| => Mapping Table
tableC:|ID_C |FieldToGet| => Additional info field table
* Important assumption: It is possible that there is multiple values of ID_C associated with the same ID_A such that tableB: has the following structure:`
| ID_B | ID_C | ID_A|
| 24 | 81 | 23 |
| 25 | 11 | 23 |
| 26 | 82 | 23 |
| 27 | 42 | 24 |
| 28 | 113 | 33 |
but if there is to be multiple ID_B for a single ID_A, they will follow each other.
Now, I am writing a php script to fetch the required fied for a certain ID_A, but it fails because my subquery returns multiple rows.:
$q = $this->dao->prepare('SELECT FieldToGet FROM table3 WHERE ID_C = ( SELECT ID_C FROM table2 WHERE ID_A =$SOME_ID_A )');
I am looking for either a solution that only fetches results from columm FieldToGet ( My current attempt) or a solution that will join the whole basetable, with the column FieldToGet ( unsure about how to use the JOIN opearation).
the join will look like this,
SELECT a.*, c.* -- select the columns you want to get
FROM tableA a
INNER JOIN tableB b
ON a.ID_A = b.ID_A
INNER JOIN tableC c
ON b.ID_C = c.ID_C
To fully gain knowledge about joins, kindly visit the link below:
Not completely sure I understand your needs. @JW correctly shows you how to use the INNER JOIN to connect your 3 tables together.
Your query is very close. Instead of using WHERE ID_C =, you should use WHERE ID_C IN ().
SELECT FieldToGet
FROM table3
WHERE ID_C IN (SELECT ID_C FROM table2 WHERE ID_A =$SOME_ID_A)
I personally prefer using JOINs over INs, but depending on your DB structure, using IN could actually perform better as using JOINs could return multiple results and require the use of DISTINCT or GROUP BY. Here was a good post I read about it:
http://explainextended.com/2009/06/16/in-vs-join-vs-exists/
And one on SO:
https://stackoverflow.com/a/4755644/1073631
Good luck.