Select 2 related and 1 unrelated tables in a query

Question

I have this query below that returns me data from two related tables. It takes all vehicle fields and all product fields with equal "id_transfer"

 $cmd = "SELECT p.*, v.* FROM produtos AS p
       INNER JOIN veiculos AS v
       ON p.id_veiculo= v.id_veiculo where p.id_transfer = '$id_transfer' AND     
 v.id_transfer = '$id_transfer' AND  nome LIKE '%$pesq%' ORDER by nome limit 
 $inicio,$registros

I’m not sure how to insert a 3rd table that doesn’t need to be related.

Would that be:

  Selecione 
  todos tabala veiculo
  todos tabela produtos
  com seu id_tranfer iguais
  e selecione 
  todos da tabela agenda_saidas
  com status iguais ativo

  Produto
  +--------+----------------+---------------+
  |   id   |     Destino    |    Data       |
  +--------+----------------+---------------+
  |  01    | Rio de Janeiro | 01/01/2015    |
  |  02    | São Paulo      | 01/01/2015    |
  +--------+----------------+---------------+
  Veiculo
  +--------+----------------+---------------+
  |   id   |     Veiculo    |    Cor        |
  +--------+----------------+---------------+
  |  01    |     Palio      |   Preto       |
  |  02    |     GOL        |   Branco      |
  +--------+----------------+---------------+

  agenda_saidas
  +--------+----------------+---------------+
  |   x1   |      x2        |    x3         |
  +--------+----------------+---------------+
  |   xxx  |      xxx       |    xxx        |
  |   xxx  |      xxx       |    xxx        |
  +--------+----------------+---------------+

   while{ 
   Resultado = Rio de Janeiro 01/01/2015 Palio Preto
   Resultado = São Paulo 01/01/2015 GOL Branco
   .....
   Resultado = xxx xxx xxx  
   Resultado = xxx xxx xxx    
   .....
   Se é que isso e possível..
   }

Answer 1

This query will list all products/vehicles with all schedules

SELECT p.*, v.*, ags.*
FROM produtos AS p
JOIN veiculos AS v ON p.id_veiculo = v.id_veiculo and v.id_transfer = p.id_transfer
JOIN agenda_saidas ags ON ags.status = 'ativo'
where p.id_transfer = '$id_transfer' AND  nome LIKE '%$pesq%' 
ORDER by nome 
limit $inicio,$registros

what seems to me what you want is all products/vehicles + schedule_exits. In that case you need to know exactly which columns you want. They are two different queries, but both need to have the same number of columns.

select * from (
    SELECT p.Destino as nome, p.Data as data, v.Veiculo, v.Cor
    FROM produtos AS p
    JOIN veiculos AS v ON p.id_veiculo = v.id_veiculo and v.id_transfer = p.id_transfer
    where p.id_transfer = '$id_transfer' AND  nome LIKE '%$pesq%' 
    UNION
    SELECT ags.x1, ags.x2, ags.x3, '' 
    FROM agenda_saidas ags 
    WHERE ags.status = 'ativo'
    ) produtos_veiculos_agenda_saidas
    ORDER by nome 
limit $inicio,$registros    

check the name of the columns, do not know if you posted in the correct name of the tables and columns.

Answer 2

You can join in the table agenda_saidas without specifying junction condition, since the table has no relation.

But this will cause a crossing n x m (all lines with all lines), ie, will duplicate the results of vehicles and products for each instance that exists in the table agenda_saidas.

You can fix it by making a group by per vehicle.

SELECT Veiculo.*, Produto.*, agenda_saidas.* FROM Veiculo
INNER JOIN Produto ON Produto.veiculo_id = Veiculo.id
INNER JOIN agenda_saidas
WHERE Veiculo.transfer_id = 1 AND Produto.transfer_id = 1
GROUP BY Veiculo.id

See working on SQL Fiddle.

I don’t know if this is exactly what you want. If you need anything else, I suggest an issue in your question.