mysql query multi table with union and limit

Question

I need to fix a query and dont know how. actually the query is this

(SELECT qrliststgo.ordenser,qrliststgo.razon,qrliststgo.emision,qrliststgo.despacho,hitos.fecha FROM `qrliststgo`,`hitos` WHERE `qrliststgo`.`tipotransporte`='I' AND `hitos`.`idplace`=`qrliststgo`.`sigla` AND `hitos`.`guia`=`qrliststgo`.`guia`) UNION (SELECT qrlistvalpo.ordenser,qrlistvalpo.razon,qrlistvalpo.emision,qrlistvalpo.despacho,hitos.fecha FROM `qrlistvalpo`,`hitos` WHERE `tipotransporte`='I' AND `hitos`.`idplace`=`qrlistvalpo`.`sigla` AND `hitos`.`guia`=`qrlistvalpo`.`guia`) UNION (SELECT qrlistsananto.ordenser,qrlistsananto.razon,qrlistsananto.emision,qrlistsananto.despacho,hitos.fecha FROM `qrlistsananto`,`hitos` WHERE `qrlistsananto`.`tipotransporte`='I' AND `hitos`.`idplace`=`qrlistsananto`.`sigla` AND `hitos`.`guia`=`qrlistsananto`.`guia`) UNION (SELECT qrlistlocalstgo.ordenser,qrlistlocalstgo.razon,qrlistlocalstgo.emision,qrlistlocalstgo.despacho,hitos.fecha FROM `qrlistlocalstgo`,`hitos` WHERE `qrlistlocalstgo`.`tipotransporte`='I' AND `hitos`.`idplace`=`qrlistlocalstgo`.`sigla` AND `hitos`.`guia`=`qrlistlocalstgo`.`guia`) UNION (SELECT qrlistlocalvalpo.ordenser,qrlistlocalvalpo.razon,qrlistlocalvalpo.emision,qrlistlocalvalpo.despacho,hitos.fecha FROM `qrlistlocalvalpo`,`hitos` WHERE `qrlistlocalvalpo`.`tipotransporte`='I' AND `hitos`.`idplace`=`qrlistlocalvalpo`.`sigla` AND `hitos`.`guia`=`qrlistlocalvalpo`.`guia`) UNION (SELECT qrlistlocalsananto.ordenser,qrlistlocalsananto.razon,qrlistlocalsananto.emision,qrlistlocalsananto.despacho,hitos.fecha FROM `qrlistlocalsananto`,`hitos` WHERE `qrlistlocalsananto`.`tipotransporte`='I' AND `hitos`.`idplace`=`qrlistlocalsananto`.`sigla` AND `hitos`.`guia`=`qrlistlocalsananto`.`guia`)

Yes, i know its long, basically, i need to find all the rows FROM "qrliststgo" that matches 2 columns of it with 2 columns of "hitos".

(SELECT qrliststgo.ordenser,qrliststgo.razon,qrliststgo.emision,qrliststgo.despacho,hitos.fecha FROM `qrliststgo`,`hitos` WHERE `qrliststgo`.`tipotransporte`='I' AND `hitos`.`idplace`=`qrliststgo`.`sigla` AND `hitos`.`guia`=`qrliststgo`.`guia`)

and then use UNION to do the same with another table named "qrlistvalpo" and "hitos" again.

The problem im having is that in "hitos",there are 3 rows that matches, one row from "qrliststgo", so it being "duplicated"... 3 lines with the same info except for the last one that changed because it find 3 results in "hitos".

how can i do it?

(i need this for a php to excell export)

Update:

What i need is show all the rows from qrlist where there is at least one row in hitos that qrlist.sigla = hitos.idplace and qrlist.guia=hitos.guia.

If there is more that one result in hitos that matches, show the first one and skip the others and then check another result from qrlist.

dont know if i explained correctly

yes i did, i have this `SELECT * FROM `qrlistlocalstgo` LEFT JOIN `hitos` ON `qrlistlocalstgo`.`sigla`=`hitos`.`idplace` AND `qrlistlocalstgo`.`guia`=`hitos`.`guia`` and that results in 16 rows, 13 with null data on the 'hitos' columns and 3 rows with the same data on the qrlist part and different data on the hitos columns cos i have 3 rows that matches guia and sigla on qrlist of guia and idplace on hitos. — Sebiche, Jul 13 '15 at 13:34

score 0 · Accepted Answer · answered Jul 13 '15 at 13:49

0

See if the following gives what you're after:

      (SELECT ordenser, razon, emision, despacho, fecha FROM qrliststgo
       WHERE (sigla, guia) IN (SELECT DISTINCT idplace, guia FROM hitos))
UNION (SELECT ordenser, razon, emision, despacho, fecha FROM qrlistvalpo
       WHERE (sigla, guia) IN (SELECT DISTINCT idplace, guia FROM hitos))
UNION (SELECT ordenser, razon, emision, despacho, fecha FROM qrlistsananto
       WHERE (sigla, guia) IN (SELECT DISTINCT idplace, guia FROM hitos))
UNION (SELECT ordenser, razon, emision, despacho, fecha FROM qrlistlocalstgo
       WHERE (sigla, guia) IN (SELECT DISTINCT idplace, guia FROM hitos))
UNION (SELECT ordenser, razon, emision, despacho, fecha FROM qrlistlocalvalpo
       WHERE (sigla, guia) IN (SELECT DISTINCT idplace, guia FROM hitos))
UNION (SELECT ordenser, razon, emision, despacho, fecha FROM qrlistlocalsananto
       WHERE (sigla, guia) IN (SELECT DISTINCT idplace, guia FROM hitos));

answered Jul 13 '15 at 13:49

Luke

1,724
1
12
17

WOW, almost almost, i had to remove "fecha" from the SELECT cos fecha is from hitos and not from "qrlist...", i just need to get fecha from hitos in the same table. – Sebiche Jul 13 '15 at 14:39
but as i told, there can be more than one row that matches sigla and guia with idplace and guia in hitos, i just need to show the first fecha from hitos (ordered by id) – Sebiche Jul 13 '15 at 14:52
this is what i get with my query [http://i.imgur.com/z102w0T.png] and this is with yours [http://i.imgur.com/1Z7FtHW.png]. With yours i get both rows that have matching info on hitos, but im missing the "fecha" – Sebiche Jul 13 '15 at 14:59
Ah, getting fecha is a bit annoying. I wish SQL had some easy way of selecting the top n rows from a group by some predicate. Easiest is to add another expression to the select clause: `(SELECT TOP 1 h.fecha FROM hitos AS h WHERE sigla = h.idplace AND guia = h.guia)`, but this is becoming a pretty inefficient query... Otherwise we look at CROSS APPLY like here: http://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group/#12190849 and more info here: http://stackoverflow.com/questions/1139160/when-should-i-use-cross-apply-over-inner-join – Luke Jul 14 '15 at 05:34
thanks i will check it. i managed to make the query with 2 querys, the first one is to get all the rows from qrlist and the second one to get the ones from hitos and only print the ones where idplace and guia is the same as sigla and guia from qrlist and if there is a row on hitos. thanks for the answer, i could use that query in another page – Sebiche Jul 14 '15 at 12:33

mysql query multi table with union and limit

1 Answers1