CODEWITHSUNDEEP
phpmysql
Rocco The Taco
Rocco The Taco

Reputation: 3777

MySQL JOIN on different column names

I have two tables with similar data but entirely different column names. I make the query in a PHP page and then use the data in echo statements like this:

<?php echo($row['bedrooms']); ?>

The current query looks like this:

$sql_query = "SELECT mls_number, city, list_price, bedrooms 
                  FROM bonita_property_res 
                  WHERE city = "Bonita" AND list_price >= 1500000 
                  ORDER BY list_price DESC";

How do a join a table called naples_property_res that looks like this and still be able to use the php echo as its configured?

MLSNumber     City       ListPrice      TotalBeds
--------------------------------------------------
898989   | Bonita    | 200000     |  4

Upvotes: 0

Views: 374

Answers (4)

Minoru
Minoru

Reputation: 1730

You can just use ALIAS to the columns.

$sql_query = "SELECT colA AS mls_number, colB AS city, colC AS list_price, 
                  colD AS bedrooms FROM naples_property_res WHERE ...";

And make a UNION.

Upvotes: 1

Digital Chris
Digital Chris

Reputation: 6202

Well you didn't tell us what the "entirely different column names" are but it would look something like this:

SELECT mls_number, city, list_price, bedrooms 
FROM bonita_property_res 
WHERE city = "Bonita" AND list_price >= 1500000 
ORDER BY list_price DESC
UNION
SELECT entirely, different, column, names
FROM naples_property_res
WHERE ......

Upvotes: 1

Dave
Dave

Reputation: 3658

Use UNION:

SELECT mls_number, city, list_price, bedrooms FROM bonita_property_res WHERE ...
UNION 
SELECT MLSNumber AS mls_number, City AS city, ListPrice AS list_price, TotalBeds AS bedrooms FROM naples_property_res WHERE ...

The column aliases - something AS something_else - ensure that you don't break any references in PHP, e.g. $row['bedrooms'].

Upvotes: 1

Explosion Pills
Explosion Pills

Reputation: 191729

It doesn't sound like you want to use a JOIN, but rather a UNION

SELECT fields FROM bonita_property_res WHERE conditions
UNION SELECT fields FROM naples_property_res WHERE conditions

Upvotes: 1

Related Questions