SQL Joins

stevenfilip · January 11th, 2011, 07:23 AM

Hi All,

I'm not sure how to solve this problem.
I have 3 tables: property_type, area, rentorsale and 1 table that contains a list of properties.

I want to get a result that contains the normal words not the id's. I'm no star with joins etc, I do think this is the way to do it but I have no idea.

Anyone that can help?

Thank you very much!

Code:

tabel_property_type:
 id | property_type | 
----+---------------+
 1  | Apartment     |
 2  | Villa         |
 3  | Office        |


tabel_area:
 id | area          |
----+---------------+
 1  | New York      |
 2  | Florida       |


tabel_rentorsale:
 id |rentorsale     |
----+---------------+
 1  | Rent          |
 2  | Sale          |


tabel_properties:
 id | property_type  | area  | rentorsale  |
----+----------------+-------+-------------+
 1  | 2              | 1     | 1           |
 2  | 1              | 1     | 2           |
 3  | 2              | 2     | 2           |
 4  | 2              | 2     | 1           |
 5  | 3              | 1     | 1           |


QUERY:
SELECT *
FROM tabel_properties
WHERE rentorsale = 1

I want these results:
 id | property_type  | area     | rentorsale  |
----+----------------+----------+-------------+
 1  | Villa          | New York | Rent        |
 4  | Villa          | Florida  | Rent        |
 5  | Office         | New York | Rent        |

rstelma · January 11th, 2011, 02:55 PM

Just don't select the ID column in your query.

stevenfilip · January 11th, 2011, 02:59 PM

Hi,

I don't think you understand my question.

I have this table

Code:

tabel_properties:
 id | property_type  | area  | rentorsale  |
----+----------------+-------+-------------+
 1  | 2              | 1     | 1           |
 2  | 1              | 1     | 2           |
 3  | 2              | 2     | 2           |
 4  | 2              | 2     | 1           |
 5  | 3              | 1     | 1           |

And I want this as a result

Code:

I want these results:
 id | property_type  | area     | rentorsale  |
----+----------------+----------+-------------+
 1  | Villa          | New York | Rent        |
 4  | Villa          | Florida  | Rent        |
 5  | Office         | New York | Rent        |

rstelma · January 11th, 2011, 03:33 PM

This is difficult if not impossible without knowing where the real 'names' are stored.

Let's say I have two tables:

Table1 has two columns: T1ID, AreaID
Table2 has two columns: AreaID, AreaName

Here's my query to get the area name from Table2

SELECT AreaName AS [Area] FROM Table1 INNER JOIN Table2 ON Table1.AreaID = Table2.AreaID

Is that what you want?

stevenfilip · January 11th, 2011, 04:54 PM

Ok, I think I have found the solution.

To make it simple I have left out the table tabel_rentorsale, but that is now also easy to if needed.

Code:

select tabel_area.area, tabel_property_type.type, tabel_properties.area, properties.type, tabel_properties.id
from tabel_area, tabel_property_type, tabel_properties
where tabel_area.id = tabel_properties.area and tabel_property_type.id = tabel_properties.type and tabel_properties.type = 1

This will give me back all apartments in all areas together with the 'readable' string of the area.

rstelma · January 11th, 2011, 07:45 PM

I'm glad that you found the solution.

I hope that you can see in my example that the way I build my tables that I'm always joining with the same column name....

AreaID in one table is also called AreaID in the other table. That way I don't have to think too much.