Join two tables where one table name is inside the other table

Question

I am registering various properties in the property table. Depending on the type of property, three new tables namely apartment, villa, land is used to store specific data. So what I have is the a master table which holds property URL, Property type, Ref table name (any of the three sub table name - apartment, villa, land) and the ref id which is the primary autoincrement key of the sub tables.

I tried the three seperated select query and Union of the result. The drawback is that the results will be mostly from the first table name uses among the three.

 function getMyProperty() {
        $tables = $this->getPropertyTables();
        $result = array();
        foreach ($tables as $key => $table) {
            $this->db->select('a.main_heading, a.sub_heading, a.location, a.about_property, a.property_price, a.available_from, p.property_url');
            $this->db->from('property p');
            $this->db->join($table . ' a', 'p.ref_id = a.id');
            $this->db->where('p.posted_by', $this->session->user_id);
            $this->db->where('p.ref_table', $this->db->dbprefix($table));
            $query = $this->db->get();
            $res = $query->result();
            $result = array_merge($result, $res);
        }
        return $result;
    }

DB SCHEMA

CREATE TABLE `apartment` (
  `id` int(11) NOT NULL,
  `main_heading` varchar(256) NOT NULL,
  `sub_heading` text NOT NULL,
  `build_up_area` int(11) NOT NULL,
  `carpet_area` int(11) NOT NULL,
  `no_of_bedrooms` int(11) NOT NULL,
  `bathrooms` int(11) NOT NULL,
  `available_from` date NOT NULL,
  `furnishing` varchar(256) NOT NULL,
  `facing` varchar(100) NOT NULL,
  `flooring` varchar(256) NOT NULL,
  `parking` varchar(256) NOT NULL,
  `width_of_facing_road` varchar(100) NOT NULL,
  `property_age` int(11) NOT NULL,
  `property_price` decimal(16,2) NOT NULL DEFAULT '0.00',
  `address` text NOT NULL,
  `about_property` text NOT NULL,
  `location` varchar(256) NOT NULL,
  `amenities` text NOT NULL,
  `owner_name` varchar(256) NOT NULL,
  `owner_email` varchar(256) NOT NULL,
  `owner_phone` varchar(100) NOT NULL,
  `active` enum('Y','N') NOT NULL DEFAULT 'Y'
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT;


INSERT INTO `apartment` (`id`, `main_heading`, `sub_heading`, `build_up_area`, `carpet_area`, `no_of_bedrooms`, `bathrooms`, `available_from`, `furnishing`, `facing`, `flooring`, `parking`, `width_of_facing_road`, `property_age`, `property_price`, `address`, `about_property`, `location`, `amenities`, `owner_name`, `owner_email`, `owner_phone`, `active`) VALUES
(2, 'My Appartment', 'Place', 255, 400, 4, 4, '2019-08-08', 'semi', 'north', 'vitrified', '2', '15', 15, '15.00', 'ghkgkgk', 'jkhjkhjk', 'Kochi', '', 'Agent', 'abc@edg.com', '9876543210', 'Y'),
(3, 'My Appartment 2', 'Test', 255, 400, 4, 4, '2019-08-08', 'semi', 'north', 'vitrified', '2', '15', 15, '15.00', 'ghkgkgk', 'jkhjkhjk', 'Kochi', '', 'Agent', 'abc@edg.com', '9876543210', 'Y');


CREATE TABLE `land` (
  `id` int(11) NOT NULL,
  `main_heading` varchar(256) NOT NULL,
  `sub_heading` text NOT NULL,
  `plot_area` int(11) NOT NULL,
  `gated_colony` int(11) NOT NULL,
  `open_sides` int(11) NOT NULL,
  `available_from` date NOT NULL,
  `dimensions` varchar(256) NOT NULL,
  `facing` varchar(100) NOT NULL,
  `boundary_wall` varchar(256) NOT NULL,
  `parking` varchar(256) NOT NULL,
  `width_of_facing_road` varchar(100) NOT NULL,
  `property_age` int(11) NOT NULL,
  `property_price` decimal(16,2) NOT NULL DEFAULT '0.00',
  `address` text NOT NULL,
  `about_property` text NOT NULL,
  `location` varchar(256) NOT NULL,
  `owner_name` varchar(256) NOT NULL,
  `owner_email` varchar(256) NOT NULL,
  `owner_phone` varchar(100) NOT NULL,
  `active` tinyint(4) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT;

CREATE TABLE `property` (
  `property_id` int(11) NOT NULL,
  `posted_by` int(11) NOT NULL,
  `post_type` enum('RENT','SELL','LEASE') NOT NULL,
  `property_type` enum('VILLA','APARTMENT','LAND') NOT NULL,
  `property_url` varchar(50) NOT NULL,
  `ref_table` varchar(50) NOT NULL DEFAULT '',
  `ref_id` int(11) NOT NULL DEFAULT '0',
  `posted_on` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT;



INSERT INTO `property` (`property_id`, `posted_by`, `post_type`, `property_type`, `property_url`, `ref_table`, `ref_id`, `posted_on`) VALUES
(4, 1, 'SELL', 'VILLA', 'lyohlp', 'villa', 2, '2019-08-05'),
(5, 1, 'SELL', 'APARTMENT', 'cvbdit', 'apartment', 2, '2019-08-05'),
(6, 2, 'SELL', 'APARTMENT', 'qwerty', 'apartment', 3, '2019-08-05'),
(7, 3, 'RENT', 'VILLA', 'asdfgh', 'villa', 3, '2019-08-05');

CREATE TABLE `villa` (
  `id` int(11) NOT NULL,
  `main_heading` varchar(256) NOT NULL,
  `sub_heading` text NOT NULL,
  `build_up_area` int(11) NOT NULL,
  `carpet_area` int(11) NOT NULL,
  `no_of_bedrooms` int(11) NOT NULL,
  `bathrooms` int(11) NOT NULL,
  `available_from` date NOT NULL,
  `furnishing` varchar(256) NOT NULL,
  `facing` varchar(100) NOT NULL,
  `flooring` varchar(256) NOT NULL,
  `total_area` varchar(256) NOT NULL,
  `width_of_facing_road` varchar(100) NOT NULL,
  `property_age` int(11) NOT NULL,
  `property_price` decimal(16,2) NOT NULL DEFAULT '0.00',
  `address` text NOT NULL,
  `about_property` text NOT NULL,
  `location` varchar(256) NOT NULL,
  `amenities` text NOT NULL,
  `owner_name` varchar(256) NOT NULL,
  `owner_email` varchar(256) NOT NULL,
  `owner_phone` varchar(100) NOT NULL,
  `active` enum('Y','N') NOT NULL DEFAULT 'Y'
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT;



INSERT INTO `villa` (`id`, `main_heading`, `sub_heading`, `build_up_area`, `carpet_area`, `no_of_bedrooms`, `bathrooms`, `available_from`, `furnishing`, `facing`, `flooring`, `total_area`, `width_of_facing_road`, `property_age`, `property_price`, `address`, `about_property`, `location`, `amenities`, `owner_name`, `owner_email`, `owner_phone`, `active`) VALUES
(2, 'My Villa', 'Kakkanad', 54, 5, 4, 4, '2019-08-06', 'semi', 'west', 'not-vitrified', '111', '10', 0, '12.00', 'fhgfgh', 'ghfghf', 'Kochi', 'car_parking,water_supply,garden,fitness_center,shower,fridge', 'dfdfdf', 'abc@edg.com', '9876543210', 'Y'),
(3, 'My Villa 2', 'Place', 54, 5, 4, 4, '2019-08-06', 'semi', 'west', 'not-vitrified', '111', '10', 0, '12.00', 'fhgfgh', 'ghfghf', 'Kochi', 'car_parking,water_supply,garden,fitness_center,shower,fridge', 'dfdfdf', 'abc@edg.com', '9876543210', 'Y');

ALTER TABLE `apartment`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `land`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `property`
  ADD PRIMARY KEY (`property_id`),
  ADD KEY `ref_id` (`ref_id`);

ALTER TABLE `villa`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `apartment`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

ALTER TABLE `land`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `property`
  MODIFY `property_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8;

ALTER TABLE `villa`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

I expect result based on the decreasing of post date irrespective of the order of the table I provided.

Provide table schema(create code), insert queries for sample data and expected output for a given input. — Dark Knight, Aug 27 '19 at 19:06
@MohammedShafeek it will return $tables = array('apartment', 'land', 'villa'); — arun c.joy, Aug 27 '19 at 19:24
@JitendraYadav As its too long, I updated it my question section — arun c.joy, Aug 27 '19 at 19:26
[How can you represent inheritance in a database?](https://stackoverflow.com/q/3579079/3404097) — philipxy, Aug 29 '19 at 20:13

score 0 · Answer 1 · answered Aug 27 '19 at 20:05

0

You can integrate the following query in CI instead of looping in php. It provides single set array.

SELECT 
  a.*,
  b.id,
  c.id,
  d.id 
FROM
  `property` AS a 
  LEFT JOIN `apartment` AS b 
    ON a.`ref_id` = b.id 
    AND a.`ref_table` = 'apartment' 
  LEFT JOIN `land` AS c 
    ON a.`ref_id` = c.id 
    AND a.`ref_table` = 'land' 
  LEFT JOIN `villa` AS d 
    ON a.`ref_id` = d.id 
    AND a.`ref_table` = 'villa' 
ORDER BY a.`posted_by` DESC ;

Here, joining tables respective of constant table name and other fields will return null.Those conditions can map in PHP code.

Note : add necessary columns in select what you needed.

add this condition too : $this->db->where('p.posted_by', $this->session->user_id); in query

answered Aug 27 '19 at 20:05

Mohammedshafeek C S

1,916
2
16
26

The result is coming up, but multiple id column is coming. When I add main_heding column to select, it also coming multiple times – arun c.joy Aug 28 '19 at 03:15
That's what i added to customize select part using alias as per your wish in note – Mohammedshafeek C S Aug 28 '19 at 05:26
Yes, I saw your note. I just mentioned that because someone else who checks for a solution can differentiate the two answers received for my question. Appreciating your effort. – arun c.joy Aug 29 '19 at 15:33

Dark Knight · Accepted Answer · 2019-08-29T16:48:34.153

0

As we know only one of the type can be mapped with the property.

So data will be generated from one table(which store details of that property type) only, other tables will produce null.

A great way to pick data from multiple columns if only one of them is not null, is using CONCAT_WS or COALESCE.

SELECT 
    P.property_id, P.property_url, 
    CONCAT_WS('', V.main_heading, A.main_heading, L.main_heading) AS main_heading,
    CONCAT_WS('', V.sub_heading, A.sub_heading, L.sub_heading) AS sub_heading,
    CONCAT_WS('', V.location, A.location, L.location) AS location,
    CONCAT_WS('', V.about_property, A.about_property, L.about_property) AS about_property,
    CONCAT_WS('', V.property_price, A.property_price, L.property_price) AS property_price,
    CONCAT_WS('', V.available_from, A.available_from, L.available_from) AS available_from,
    P.posted_on
FROM property P
LEFT JOIN villa V ON P.ref_table = 'villa' AND P.ref_id = V.id 
LEFT JOIN apartment A ON P.ref_table = 'apartment' AND P.ref_id = A.id
LEFT JOIN land L ON P.ref_table = 'land' AND P.ref_id = L.id
ORDER BY P.posted_on DESC;

Using COALESCE at place of CONCAT_WS would be like:

COALESCE(V.main_heading, A.main_heading, L.main_heading) AS main_heading

Reference: MySQL Function: COALESCE, MySQL Function: CONCAT_WS

edited Aug 29 '19 at 16:48

answered Aug 27 '19 at 20:15

Dark Knight

6,116
1
15
37

This is working. I am going to use this for finding My Visits and My Favorites listing. – arun c.joy Aug 28 '19 at 03:12
@arun c.joy, if it works don't forget to accept and vote up the answer. It motivates others to provide a working solution – Dark Knight Aug 28 '19 at 05:34
I already voted up. Please let me know is there any option to accept the answer. – arun c.joy Aug 29 '19 at 15:36
Here is the way to accept an answer. [Accept An answer](https://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work) – Dark Knight Aug 29 '19 at 16:29
@arun c.joy, if answer solves your problem, do accept it. :) – Dark Knight Sep 03 '19 at 03:20

Join two tables where one table name is inside the other table

2 Answers2