Join of two tables query php + mysql

Question

Join of two tables query php + mysql

Navigation

#1 by (1 votes)

1

I have two tables: accounts payable and accounts receivable. The two tables have: code, description, date, datepagamento, data collection and value.

How do I make a query to get the data of the two tables by sorting by the date?

I was using union :

SELECT 
    codigo,
    descricao,
    dataVencimento,
    dataEntrada,
    valor
FROM
    contasReceber
WHERE
    status= "pago" 

    UNION ALL

SELECT 
    codigo,
    descricao,
    dataVencimento,
    dataSaida,
    valor
FROM
    pagamento
order by dataVencimento

But I could not think of a way to differentiate the inputs from the outputs. Because I need to display this data, for example:

Codigo   Descrição   Pago    Recebido
1        farmacia    20,00   -
2        aluguel     -       30,00

How can I do this using javascript + php + mysql ?

php mysql

asked by anonymous 19.08.2017 / 07:54

1 answer

Add Blank Row Excel Using VBA Query on two different tables in Wordpress

score 1 · Accepted Answer

Continue using union, however you should create additional columns:

SELECT 
r.codigo,
r.descricao,
r.dataVencimento,
r.dataEntrada,
0 as pago,
r.valor as recebido,
'CR' as tipo
FROM
contasReceber r
WHERE
r.status= "pago" 
and r.dataVencimento between '01/08/2017' and '30/08/2017'

UNION

SELECT 
p.codigo,
p.descricao,
p.dataVencimento,
p.dataSaida,
p.valor,
0,
'PG' as tipo
FROM
pagamento p
where p.dataVencimento between '01/08/2017' and '30/08/2017'

order by dataVencimento

remembering that the name of the columns will follow the name of the first query.