SELECT with 2 tables and same field in both

Asked

Viewed 1,239 times

1

Good afternoon!

In my database, I have two tables: imoveisvenda and imoveislocacao

The two have the field "dormitory", which tells how many dormitories have the house or apartment, be it for sale or lease.

I want to create a SELECT to make it easier for those browsing the site.

I have tried several combinations on Phpmyadmin, but without success.

How can I make this query, to deploy in my PHP page?

The last one I tried was: SELECT * imoveislocacao INNER JOIN imoveisvenda WHERE dormitorios = 3. Once again, without success!

I’ve looked at a few posts here from Stackoverflow, but I couldn’t deploy.

Can someone help me?

UPDATING:

My friend @Ricardo, I want to thank you for the support. And, as requested, follow the error that was generated below:

Notice in ./libraries/sqlparser.lib.php#2477
Undefined offset: -2

Backtrace

./libraries/sql.lib.php#1456: PMA_SQP_format(
array,
string 'query_only',
integer 0,
integer 2,
)
./libraries/sql.lib.php#1574: PMA_countQueryResults(
integer 3,
boolean false,
boolean false,
string 'adimovel_imoveis',
string '',
array,
array,
)
./libraries/sql.lib.php#2411: PMA_executeTheQuery(
array,
string '(SELECT iv.id, iv.dormitorio FROM imoveisvenda iv WHERE iv.dormitorio = 3) UNION (SELECT il.id, il.dormitorio FROM imoveislocacao il WHERE il.dormitorio = 3) LIMIT 0, 25 ',
boolean false,
string 'adimovel_imoveis',
string '',
NULL,
string '(SELECT iv.id, iv.dormitorio FROM imoveisvenda iv WHERE iv.dormitorio = 3) UNION (SELECT il.id, il.dormitorio FROM imoveislocacao il WHERE il.dormitorio = 3)',
NULL,
)
./import.php#708: PMA_executeQueryAndSendQueryResponse(
array,
boolean false,
string 'adimovel_imoveis',
string '',
NULL,
string '(SELECT iv.id, iv.dormitorio FROM imoveisvenda iv WHERE iv.dormitorio = 3) UNION (SELECT il.id, il.dormitorio FROM imoveislocacao il WHERE il.dormitorio = 3)',
NULL,
boolean false,
NULL,
NULL,
NULL,
array,
string 'db_structure.php',
string './themes/pmahomme/img/',
NULL,
NULL,
NULL,
string '(SELECT iv.id, iv.dormitorio FROM imoveisvenda iv WHERE iv.dormitorio = 3) UNION (SELECT il.id, il.dormitorio FROM imoveislocacao il WHERE il.dormitorio = 3)',
NULL,
NULL,
)
Notice in ./libraries/sqlparser.lib.php#2482
Undefined offset: -1

Backtrace

./libraries/sql.lib.php#1456: PMA_SQP_format(
array,
string 'query_only',
integer 0,
integer 2,
)
./libraries/sql.lib.php#1574: PMA_countQueryResults(
integer 3,
boolean false,
boolean false,
string 'adimovel_imoveis',
string '',
array,
array,
)
./libraries/sql.lib.php#2411: PMA_executeTheQuery(
array,
string '(SELECT iv.id, iv.dormitorio FROM imoveisvenda iv WHERE iv.dormitorio = 3) UNION (SELECT il.id, il.dormitorio FROM imoveislocacao il WHERE il.dormitorio = 3) LIMIT 0, 25 ',
boolean false,
string 'adimovel_imoveis',
string '',
NULL,
string '(SELECT iv.id, iv.dormitorio FROM imoveisvenda iv WHERE iv.dormitorio = 3) UNION (SELECT il.id, il.dormitorio FROM imoveislocacao il WHERE il.dormitorio = 3)',
NULL,
)
./import.php#708: PMA_executeQueryAndSendQueryResponse(
array,
boolean false,
string 'adimovel_imoveis',
string '',
NULL,
string '(SELECT iv.id, iv.dormitorio FROM imoveisvenda iv WHERE iv.dormitorio = 3) UNION (SELECT il.id, il.dormitorio FROM imoveislocacao il WHERE il.dormitorio = 3)',
NULL,
boolean false,
NULL,
NULL,
NULL,
array,
string 'db_structure.php',
string './themes/pmahomme/img/',
NULL,
NULL,
NULL,
string '(SELECT iv.id, iv.dormitorio FROM imoveisvenda iv WHERE iv.dormitorio = 3) UNION (SELECT il.id, il.dormitorio FROM imoveislocacao il WHERE il.dormitorio = 3)',
NULL,
NULL,
)

Finally, my complete code was as follows:

<?php

                            $hostdb = "**********";
                            $userdb = "**********";
                            $passdb = "**********";
                            $tabledb = "**********";

                            $conecta = mysql_connect($hostdb, $userdb, $passdb) or die (mysql_error());
                            @mysql_select_db($tabledb, $conecta) or die ("Erro ao conectar com o banco de dados");

                            $busca_query = mysql_query("SELECT * FROM imoveisvenda WHERE imoveisvenda.dormitorio = 3 UNION ALL SELECT * FROM imoveislocacao WHERE imoveislocacao.dormitorio = 3")or die(mysql_error());

                            if (empty($busca_query)) {
                                echo "Nenhum registro encontrado.";
                            }

                            while ($dados = mysql_fetch_array($busca_query)) { ?>
                                <img src="<?php echo "$dados[bancoimgthumb]";?>" class="img-responsive"> <?php
                                echo "Imóvel: $dados[imovel]<br />"; 
                                echo "Localização: $dados[localizacao]<br />"; ?>
                                Mais detalhes: <a href="<?php echo "$dados[file]";?>?id=<?php echo "$dados[id]";?>">Clique aqui!</a><br />
                                <?php echo "<hr>";
                            }
                            ?>

EVERYTHING WORKING PERFECTLY! Thank you to everyone who has given up their time to help me! Hugs!

php mysql

  • What is the table id imoveisvenda and imoveislocacao?

  • It is the same "id" field, same for both.

  • Try it on there: SELCT * FROM imoveisvenda iv INNER JOIN imoveisalocacao ia ON (iv.id = ia.id) WHERE iv.dormitorios = 2 AND ia.dormitorios = 2

  • I’ll try, I’ll be right back.

  • 1054 - Unknown column 'iv.dormitorios' in 'Where clause'

  • Puts your field dormitorios in the clause WHERE, I don’t know how he’s appointed in his bank.

  • It is dorm, I corrected, I had not noticed... but the query does not return anything, nor error message. Very strange!

  • It is probably because there should be no domitorio with value 2

  • I thought that too. I changed to 3 and 1, nothing...

  • I tried so: SELECT dormitorio FROM imoveislocacao INNER JOIN imoveisvenda ON dormitorio = id WHERE dormitorio = 3 and the result was: Column 'dormitorio' in field list is ambiguous

  • From a look here: http://answall.com/questions/6441/qual%C3%A9-a-difference%C3%A7a-entre-Inner-Join-e-outer-Join/6448#6448

  • 1

    @Denercarvalho, I will take a look. I will return with the solution, no doubt! Hug, friend!

Show 7 more comments

3 answers

2


You just need to use Union All

select * from imoveisvenda where imoveisvenda.dormitorio=3 union all select * from imoveislocacao where imoveislocacao.dormitorio = 3

  • What worked for me was your code, @Ronaldo. Thanks for the support!

1

I’d wear a Union in two selects once I didn’t understand how to do the join of the tables:

(SELECT iv.campo1, iv.campo2 FROM imoveisvenda iv WHERE iv.dormitorios = 3)
UNION
(SELECT il.campo1, il.campo2 FROM imoveislocacao il WHERE il.dormitorios = 3)

Give a read on the UNION here

Here is a Fiddle with the Union running.

I will put the fiddle code also to keep the record:

Create table imovelVendido (
  id varchar(10),
  nome varchar(10));

Create table imovelAlugado (
  id varchar(10),
  nome varchar(10));

  insert into imovelVendido values ("1","venda");
  insert into imovelAlugado values ("1","alugado");

Once this data created executes the SQL below that returns the two records:

(SELECT iv.id, iv.nome FROM imovelVendido iv WHERE iv.id = "1")
UNION
(SELECT il.id, il.nome FROM imovelAlugado il WHERE il.id = "1")

  • did not work. Generates an error, even shows the results, but when I play in PHP page, shows nothing.

  • What mistake are you making?

  • "Some errors have been detected on the server! Please look at the bottom of this window."

  • The report is too big to send...

  • But not knowing how we’re going to do it? Edit your question and put the bug there, there it will fit. It’s formatted for everyone to read. I’ll make a Fiddler to test the Union in the meantime.

  • Edited response, keeps seeing the Fiddle link to better understand how it works. http://sqlfiddle.com/#! 9/e36195

Show 1 more comment

0

In the future, you can improve your application by leaving this data in only one table, because as I understand the structure of the tables is the same, you can create a field to indicate the type of contract (sale/lease). So your tables will become better normalized.

  • I agree. I will finish the project, because time is very scarce, and then I make this improvement. Thanks for the tip!

Browser other questions tagged php mysql

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