#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    84
    Rep Power
    0

    join table-name with table on other database on same server


    Hello,
    I have on same server two databases.
    I need to do a query to database1 and I need to do a query to database2,
    I need to join these two queries, what they have in common is the property-name,
    in database1, the property-name is a row, however the problem is with database2.
    In database 2 the property-name is the name of the table...

    Below queries works perfectly separately, but how to join the results?

    Code:
        SELECT internet, text_esp, pool, bedrooms, bathrooms, air, image
        FROM database1.2buscador 
        where id_propiedad = 'propertyname'
    
        SELECT min(`price_client`) AS price From 
        database2.`propertyname` (propertyname = name for the table)
    Below you can see how the two tables are in the 2 differents databases:

    Code:
     --
        -- 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');
    
    
         --
         -- 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',);
    Is this possible?
    Thanks
    Last edited by newtonperri; September 6th, 2017 at 02:18 AM.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    84
    Rep Power
    0
    Keep trying things,
    I woud prefer a join if possible.

    I tried a union, and if I do a query like this I get the result in 2 rows

    Code:
    select min(`price_client`) as price, null, null, null, null, null, null, null from marbella_casas.Alicate_Playa_80
    union
    select null, internet, text_esp, pool, bedrooms, bathrooms, air, image from marbella_bookings.2buscador where id_propiedad = 'Alicate_Playa_80'
  4. #3
  5. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,712
    Rep Power
    4288
    Originally Posted by newtonperri
    Below queries works perfectly separately, but how to join the results?
    you cannot

    your error was in designing a separate table for each property

    you will have to run the queries separately and join the results in your front-end language (php or whatever)

    alternatively, if you can stuff the name of the property into the sql before running the two queries, you can combine them like this --
    Code:
        SELECT this.internet
             , this.text_esp
             , this.pool
             , this.bedrooms
             , this.bathrooms
             , this.air
             , this.image
             , that.price
          FROM database1.2buscador AS this
        CROSS
          JOIN ( SELECT MIN(`price_client`) AS price 
                   From database2.`Alicate_Playa` 
               ) AS that         
         WHERE this.id_propiedad = 'Alicate_Playa'
    Last edited by r937; September 6th, 2017 at 10:05 AM.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    84
    Rep Power
    0
    Originally Posted by r937
    you cannot

    your error was in designing a separate table for each property

    you will have to run the queries separately and join the results in your front-end language (php or whatever)
    Ok, Thanks, I`ll do so.

    I am redesigning, the previous have only one table with date intervals and is not what I need.
    I need a row for each day of many years.
    I can still change, but I still think the best would be a separate table, not having many columns with the properties.
    Donīt know how many columns are to many.

    Thanks again

IMN logo majestic logo threadwatch logo seochat tools logo