CODEWITHSUNDEEP
phpmysqlsql
John Siniger
John Siniger

Reputation: 885

Select Different values from different tables based on id's with one query

Hello I would like to ask how I can select different values from different tables based on the id's from my main table properties table:

Here is how my DB is organized: 

propery table:

----------------------------------------------------
id | title | property_type | city | sector | owner | 
----------------------------------------------------
1  | title | 1             | 2    | 6      | 12    |
----------------------------------------------------



property_type table: 

--------------
id | english |
--------------
1  | name    |
--------------
2  | name 2  |
--------------



owner table:

-----------------------------------
id | ownername | phone            |
-----------------------------------
12 | Mike      | 27836            |
-----------------------------------

So my main select command is: 'SELECT id, title, property_type, owner FROM properties ORDER BY id'

Where for example in owner I have to be able to select both parameters the ownername and phone

Which is dumping the different ID's but I wold like to modify this query so it selects the values from the different tables based on the id in the main table.

Any help achieving this will be appreciated. And the Select should be realized with a single query.

the Result of the query should be: id(property table),english(property_type table), ownername (owner table:), phone (owner table).

Upvotes: 0

Views: 89

Answers (2)

SQL Tactics
SQL Tactics

Reputation: 296

It looks like this is the query you want:

SELECT
   P.id
  ,PT.english
  ,O.ownername
  ,O.phone
FROM property P
JOIN property_type PT
  ON PT.id = P.property_type
JOIN owner O
  ON O.id = P.owner

SQLFiddle Here

Upvotes: 2

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

select p.id, p.title, p.property_type, o.ownername, o.phone, pt.english
from property p join property_type pt 
on p.property_type = pt.id
join owner o
on o.id = p.owner

You would just join the tables. Add more columns if needed.

Upvotes: 0

Related Questions