Select 2 related tables and 1 unrelated query

Question

Select 2 related tables and 1 unrelated query

Navigation

#1 by (1 votes)
#2 by (0 votes)
#3 by (-2 votes)

1

I have this query down which returns me data from two related tables. It takes all vehicle fields and all product fields with "id_transfer" the same

 $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 does not need to be related.

It would look like this:

  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..
   }

php mysql

asked by anonymous 24.02.2015 / 21:05

3 answers

I can not change a record in a .dat file in Pascal Footer Alignment [closed]

score 1 · Answer 1

This query will link all products / vehicles with all exit_tables

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 + agenda_saidas. In this case you need to know exactly which columns you want. There 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, I do not know if you have posted in the correct name of the tables and columns.

score 0 · Answer 2

You can make a JOIN in the agenda_saidas table without specifying the join condition, since the table is unrelated.

However, this will cause a nx m (all rows with all rows) intersection, that is, it will duplicate the vehicle and product result for each instance that exists in the agenda_saidas table.

You can solve this 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 in SQL Fiddle .

I do not know if this is exactly what you want, I relied on the code snippets of the question. If you need anything else, I suggest you edit your question.

score -2 · Answer 3

You can use UNION to make this query. However, both SELECTS must have the same fields.

In any case, your questioning was not very clear. If you make explicit what you intend to return with this third table, since it has no relation to the other two, perhaps there is a better solution.