CODEWITHSUNDEEP
mysqljoin
Helenp
Helenp

Reputation: 143

mysql join table separate database to table row

I have a query that uses two database. The name of the tables in one of them are a row in the other table. I need to join these two results to one query:

    SELECT min(`price_client`) AS price 
        From database2.`$propertyname` ($propertyname = name for the table)

    SELECT internet, text_esp, pool, bedrooms, bathrooms, air, image
        FROM database1.2buscador 
        where id_propiedad = '$propertyname'

Is it possible to join them? In this case $propertyname is the name of the table Alicate_Playa

This is how the databases and tables are like, both databases are on the same server:

    /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
    /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS 
    */;
    /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
    /*!40101 SET NAMES utf8mb4 */;

    --
    -- Database: `database2`
    --

    CREATE TABLE `Alicate_Playa` (
    `ID` int(5) NOT NULL,
    `cal_date` date DEFAULT NULL,
    `price_owner` decimal(12,2) DEFAULT NULL,
    `price_client` decimal(12,2) DEFAULT NULL,
    `description` varchar(10) DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

    INSERT INTO `Alicate_Playa` (`ID`, `cal_date`, `price_owner`, 
    `price_client`, `description`) VALUES
    (1, '2017-09-01', '1.00', '158.00', 'baja'),
    (2, '2017-09-02', '1.00', '158.00', 'baja'),
    (3, '2017-09-03', '1.00', '175.00', 'baja'),
    (4, '2017-09-04', '1.00', '175.00', 'baja');



    /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
    /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS 
    */;
    /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
    /*!40101 SET NAMES utf8mb4 */;

     --
     -- Database: `database1`
     --

    CREATE TABLE `2buscador` (

    `id` int(11) NOT NULL,
    `id_propiedad` varchar(30) NOT NULL DEFAULT '',
    `text_esp` mediumtext NOT NULL,
    `internet` varchar(40) NOT NULL,
    `pool` varchar(7) NOT NULL DEFAULT '',
    `bedrooms` tinyint(2) NOT NULL DEFAULT '0',
    `bathrooms` tinyint(1) NOT NULL DEFAULT '0',
    `air` char(2) NOT NULL DEFAULT '',
   `image` varchar(100) NOT NULL DEFAULT
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

    INSERT INTO `2buscador` (`id`, `id_propiedad`, `
    `pool`, `bedrooms`, `bathrooms`, `air`, `text_esp`, 
   `image`, `internet `) 
    VALUES (88, 'Alicate_Playa', '2', 'si', 2, 2, 'si', 
    , Lorem ipsum etc.', '/includes/ Alicate_Playa/1232..jpg', 
    'si',);

Upvotes: 0

Views: 61

Answers (1)

Jarek.D
Jarek.D

Reputation: 1294

It is possible but the two DBs have to be on the same server:

SELECT t2.min(`price_client`), t1.internet, t1.text_esp, t1.pool, t1.bedrooms, t1.bathrooms, t1.air, t1.image AS price 
    FROM database2.`$propertyname` t1 
    JOIN database1.table t2 ON t1.property = t2.`$propertyname`;

Upvotes: 1

Related Questions