Link to home
Start Free TrialLog in
Avatar of Shamsul Kamal
Shamsul Kamal

asked on

How to combine MYSQL query from different table ?

Hi,

I would like to request an assistant.

I have the following tables :

Table 1 name : transactions
column name : id,ownerid,date,ta,nta,st,tax,seed,debcred,tt

Table 2 name : client_invoices
column name : id,status


Current query :

$res_select = "SELECT id,ownerid,date,ta,nta,st,tax,seed,debcred,tt FROM transactions WHERE date >= $startStamp AND date <= $EndStamp and st != '' order by id";

I would like to combine a condition to the above query as follows :

WHERE transactions.seed  = client_invoices.id WHERE client_invoices.status = 1


I have tried the following, but seems not working :

$res_select = "SELECT transactions.id, transactions.ownerid, transactions.date, transactions.ta, transactions.nta, transactions.st, transactions.tax, transactions.seed, transactions.debcred, transactions.tt, client_invoices.id
                  FROM transactions
                  RIGHT JOIN client_invoices
                  ON transactions.seed = client_invoices.id
                  WHERE (transactions.date >= $startStamp AND transactions.date <= $EndStamp)
                  AND transactions.seed = client_invoices.id WHERE client_invoices.status = 1
                  AND transactions.st != '' ORDER BY transactions.id";


Appreciates anybody can help me with this query.

                  
Thank you.
ASKER CERTIFIED SOLUTION
Avatar of Ray Paseur
Ray Paseur
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of arnold
While both tables have id, those are not related
You need to provide is ownerid in the transaction I a client Id?

In your first query you are missing a comparison value you have st != <missing comparison value/variable> Followed by  order by
This will generate an error when ran.
Avatar of Shamsul Kamal
Shamsul Kamal

ASKER

Spot On.... Thank you.
Thanks for the points and thanks for using E-E, ~Ray