2. PHPRunner General questions
  3. Dynamic modifying LEFT JOIN
This topic is locked

Dynamic modifying LEFT JOIN

 		12/13/2016 6:43:49 AM
PHPRunner General questions
C
 christou author

I have a query like this:

select * from master_tbl

left join child_tbl on child_tbl.parent=master_tbl.id and child_tbl.username='john'

where master_tbl.data=1


simply I want to replace the ON condition _childtbl.username='john' by the logged in user's user name _child_tbl.username=$SESSION['UserID'] (syntax omitted here)
I tried doing it by preg_replace at List Page: Before SQL Query, then the page will generate php error telling SQL syntax error while querying something like

SELECT count(*) from child_tbl) as t1 on ...



(yes there is an unpaired bracket)
In the error page detail, the call stack show that error occurs when PHPR tries to call GetRowCount.
Any substitute method to this?
Sergey Kornilov admin 12/13/2016

This is the type of query you should be looking for:

select * from master_tbl

left join child_tbl on child_tbl.parent=master_tbl.id

where master_tbl.data=1 and child_tbl.username='john'


And the best approach to add a WHERE clause like this is described in this article:

http://xlinesoft.com/phprunner/docs/modify_sql_query_on_the_fly.htm
C
 		christou author 12/13/2016



This is the type of query you should be looking for:

select * from master_tbl

left join child_tbl on child_tbl.parent=master_tbl.id

where master_tbl.data=1 and child_tbl.username='john'


And the best approach to add a WHERE clause like this is described in this article:

http://xlinesoft.com/phprunner/docs/modify_sql_query_on_the_fly.htm


since we have tables like this:
master_tbl



id data data2

-----------------------------

1 1 A

2 1 B

3 1 C

4 2 A

5 2 A


child_tbl



id parent data3 username

-----------------------------

10 1 0 john

11 1 1 admin

12 3 0 joe


with _childtbl.username='john' moved to the where clause the query result will have only 1 row:



id data data2 data3 username

----------------------------------------------------------

1 1 A 0 john


with _childtbl.username='john' keep in the on clause the result will have 3 rows:



id data data2 data3 username

----------------------------------------------------------

1 1 A 0 john

2 1 B (null) (null)

3 1 C (null) (null)


which I need to keep the record in master_tbl even there is no child_tbl record to join (return null).

It will be great if it can be solved in SQL.