Select from multiple tables with the same ID in Mysql

Asked

Viewed 432 times

2

The intention of the query is to select all records of various tables when the ID is equal to the one passed in the parameter, echo of the SQL line displays the query below, the records exist, but does not return any.

The intention is to verify which of the tables is the record that has the ID of the sent parameter, but the tables have no relationship, only records that MAY BE that there is the ID, not that there can be the same ID in all, and the tables have the same structure, only names of fields are changed.

SELECT 
* 
FROM 
`ax_det` a, 
`ax_det1` ar, 
`ax_det2` ca, 
`ax_det3` im, 
`ax_dete4` mo, 
`ax_det5` na, 
`ax_det6` te, 
`ax_det7` tr, 
`ax_det8` ve 
WHERE 
a.`aw_token` = '834545' 
OR 
ar.`ar_token` = '834545' 
OR 
ca.`ca_token` = '834545' 
OR 
im.`im_token` = '834545' 
OR 
mo.`mo_token` = '834545' 
OR 
na.`na_token` = '834545' 
OR 
te.`te_token` = '834545' 
OR 
tr.`tr_token` = '834545' 
OR 
ve.`ve_token` = '834545' 
;
mysql sql database

  • But what relates the lines of these 9 different tables?

  • Do these tables have the same columns? The same structure?

  • the tables have different suffixes, and the intention is only to select the records, both have the same id in different tables, the relation is the id, but each of the tables does not depend on the others to work.

  • @Ronaldoaraújoalves do not, only have equal id’s, but have different structure as placed in the WHERE condition.

  • Right, and you need all the data from each table?

  • @Ronaldoaraújoalves no, only id’s own field

  • I get it. Edit the question and report it as it is relevant.

  • @Ronaldoaraújoalves I’ve done it.

Show 3 more comments

2 answers

4


It seems to me the case of using the UNION:

SELECT a.aw_token AS token, 'ax_det' AS tabela FROM ax_det a WHERE a.aw_token = '834545'
UNION
SELECT ar.ar_token AS token, 'ax_det1' AS tabela FROM ax_det1 ar WHERE ar.ar_token = '834545'
UNION
-- ... UNION com as outras tabelas
UNION
SELECT ve.ve_token AS token, 'ax_det8' AS tabela FROM ax_det8 ve WHERE ve.ve_token = '834545'

UNION

UNION is used to combine the result from Multiple SELECT statements into a single result set.

In free translation:

UNION is used to combine the result of various instructions SELECT in a single set of results.

  • none of them have relationship with each other, the similarity is only the id that exist in each of them, the UNION in this scenario would solve also?

  • 1

    To use the UNION, the sequence of the data returned by select must be the same type and in the same order

  • the structure is the same, the names of the fields are changed, I edited the question, sorry for missing this detail. check please.

  • @Sorack It worked, I did as suggested and returned all the records. Thank you!

  • @Eliseub. with the change from now will match what you described

1

select ( SELECT campo_desejado FROM `ax_det`    a where  a.`aw_token` = '834545')  a, 
       ( SELECT campo_desejado FROM `ax_det1`  ar where ar.`ar_token` = '834545') ar, 
       ( SELECT campo_desejado FROM `ax_det2`  ca where ca.`ca_token` = '834545') ca, 
       ( SELECT campo_desejado FROM `ax_det3`  im where im.`im_token` = '834545') im, 
       ( SELECT campo_desejado FROM `ax_dete4` mo where mo.`mo_token` = '834545') mo, 
       ( SELECT campo_desejado FROM `ax_det5`  na where na.`na_token` = '834545') na, 
       ( SELECT campo_desejado FROM `ax_det6`  te where te.`te_token` = '834545') te, 
       ( SELECT campo_desejado FROM `ax_det7`  tr where tr.`tr_token` = '834545') tr, 
       ( SELECT campo_desejado FROM `ax_det8`  ve where ve.`ve_token` = '834545') ve

  • I had tried AND too, but did not return any record even existing in the tables, in the case of query, it is inside a looping, which passes a different ID but does not change anything, just need to check which one is the id searched.

  • I updated the question, I missed an important detail, sorry.

  • I saw that it succeeded, but it follows another solution through subquery.

  • Does the subquery do the same as the right UNION? I will experiment with the two examples, so learn a little more, thank you for your wisdom.

  • The result will return in columns, if there is no id, comes as null.

  • I created a second instruction and tested it. Thank you.

Show 1 more comment

Browser other questions tagged mysql sql database

You are not signed in. Login or sign up in order to post.