CODEWITHSUNDEEP
phpsqlitejoinpdo
WhiteLine
WhiteLine

Reputation: 1991

Php/SQLite , use a parameter for the JOIN taken from a query?

I have to do this with PHP on a simple SQLite database , with PDO

i have 3 table, tableA - tableB, TableC

tableA

the column TYPE represents the name of the other tables

___________________________________
|ID_OBJ |  TYPE  | PROP_1 | PROP_2 |
-----------------------------------
| 1000  | tableB |    0   |  10    |
| 1001  | tableB |    1   |  10    |
| 1002  | tableC |    1   |  10    |
-----------------------------------

tableB

____________________________
|   ID  |  PROPA   | PROPB  |
----------------------------
| 1000  |  ...     |  ....  |
| 1001  |  ...     |  ....  |
----------------------------

tableC

____________________________
|   ID  | PROPAA   | PROPBB |
----------------------------
| 1002  |  ...     |  ....  |
----------------------------

what I wanted to know is: Can i , through a JOIN (for example a LEFT JOIN) do a query that return something like this?

_______________________________________________________________________
|ID_OBJ |  TYPE  | PROP_1 | PROP_2 | PROPA   | PROPB | PROPAA   | PROPBB|
-----------------------------------------------------------------------
| 1000  | tableB |    0   |  10    |   ...   |   ... |          |       |
| 1001  | tableB |    1   |  10    |   ...   |   ... |          |       |
| 1002  | tableC |    1   |  10    |         |       |  ...     |  ...  |
-------------------------------------------------------------------------

I don't know how to do this, having to take the name of the table on which to perform a join from a table. So let me know if I can run a JOIN, and not having to do a query for each row present in the table tableA

thanks

Upvotes: 0

Views: 83

Answers (1)

CL.
CL.

Reputation: 180060

An outer join also returns rows for which no match was found:

SELECT tableA.ID_OBJ,
       tableA.TYPE,
       tableA.PROP_1,
       tableA.PROP_2,
       tableB.PROPA,
       tableB.PROPB,
       tableC.PROPAA,
       tableC.PROPBB
FROM tableA
LEFT JOIN tableB ON tableA.TYPE = 'tableB' AND tableA.ID_OBJ = tableB.ID
LEFT JOIN tableC ON tableA.TYPE = 'tableC' AND tableA.ID_OBJ = tableC.ID

If the are no 'wrong' rows in tableB/C, it is not necessary to check the type:

SELECT tableA.ID_OBJ,
       tableA.TYPE,
       tableA.PROP_1,
       tableA.PROP_2,
       tableB.PROPA,
       tableB.PROPB,
       tableC.PROPAA,
       tableC.PROPBB
FROM tableA
LEFT JOIN tableB ON tableA.ID_OBJ = tableB.ID
LEFT JOIN tableC ON tableA.ID_OBJ = tableC.ID

Upvotes: 1

Related Questions