Mysql内部连接根据值的多个表

Question

I have to create a database of real state for sale. Problem is the kind of properties: if I have a house, I have a kind of description, but if it is just a land, the descriptions does not need to include number of bathrooms, just the area, front range, etc.

So I did a table with generic data about the element (imoveis) with address, price etc.. Than I created categories of elements (imoveis_categs). I did 5 categories, for each category, there will be a table (e.g.: imoveis_descr2) with the specific features of the kind.

To enter data it's easy, but to list my data, I will need to perform query select to find those elements according with some filters. In PHP will be easy to solve, but I am wondering about the performance for a large amount of data and users requests. Better solve it by SQL commands, thought. But mySQL is not my area, I imagine something like this to start...

 SELECT * FROM imoveis INNER JOIN imoveis_descr(imoveis.categ) ...

The "categ" field of imoveis point to the right description table. It is possible to do something like this? Is there another way more appropriate or efficient to do it?

EDIT: I tryed to clarify with an example... EDIT2: I corrected the example, the column "rooms" will be the same. The fields are not exclusives, both apartment and house categories have number of rooms.

Table imoveis
id  categ   title            price       address ...
1   2       The House        $ 1000000   Somestreet 77
2   1       An Appartment    $ 500000    Somewhere 11
3   4       A Land           $ 250000    Nowhere 33

Table imoveis_descr1
idImovel    rooms   area    floor   ...
2           2       70      5

Table imoveis_descr2
idImovel    rooms fieldArea   constrArea ...
1           3      120        80

Table imoveis_descr4
idImovel    area    width   height ...
3           2640    22      120

Result
id   categ    title       price     address   rooms fieldArea  constrArea    area    floor        area   width  height
1      2     The House    $ 1000000 Somestreet 77   3   120 80  null    null    null    null    null
2   1   An Appartment   $ 500000    Somewhere 11    2   null    null    70  5   null    null    null
3   4   A Land  $ 250000    Nowhere 33  null    null    null    null    null    2640    22  120

Answer 1

Some of your field-names are duplicated in your results (e.g. "rooms"), wouldn't it be better to merge those fields with COALESCE (as they appear mutually exclusive)?

As the tables your refer to are mutually exclusive, you will never get the required results with an INNER JOIN, you'll need an OUTER JOIN:

SELECT
      i.id,
      i.categ,
      i.title,
      i.price,
      i.address,
      COALESCE(i1.rooms, i2.rooms) AS rooms,
      i2.fieldArea,
      i2.constrArea
      COALESCE(i1.area, i3.area, ...) AS area,
      ...
FROM  imoveis AS i
      LEFT OUTER JOIN imoveis_descr1 AS i1 ON i1.idImovel = i.id
      LEFT OUTER JOIN imoveis_descr2 AS i2 ON i2.idImovel = i.id
      LEFT OUTER JOIN imoveis_descr3 AS i3 ON i3.idImovel = i.id
      ...