Joining Tables

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
leewad
Forum Commoner
Posts: 91
Joined: Tue May 11, 2004 8:32 am

Joining Tables

Post by leewad »

Hi

I`m suck on joining 2 seperate tables together, i need to display all records which country is 'ES' from (Houses) and all of the other table ( rentals ).

Here are the fields of the tables:

Code: Select all

==== 
Houses 
==== 
Code
Region
FromNumberPersons
MinPrice1W
DescriptionEN
country

In an other table most of them are named different

Code: Select all

============= 
rentals 
============= 
id 
location
sleeps
price
description
Is this possible?


Thanks for your help.
User avatar
Mordred
DevNet Resident
Posts: 1579
Joined: Sun Sep 03, 2006 5:19 am
Location: Sofia, Bulgaria

Post by Mordred »

What is the connection between rentals and houses?
leewad
Forum Commoner
Posts: 91
Joined: Tue May 11, 2004 8:32 am

Post by leewad »

One is a new table from another company and one is an existing table so i need to display details from both tables, this is why i need to join the tables for the query.
User avatar
Mordred
DevNet Resident
Posts: 1579
Joined: Sun Sep 03, 2006 5:19 am
Location: Sofia, Bulgaria

Post by Mordred »

You're not paying attention.

In order to combine information from two tables, you need something to connect the two - a house must know its rental id, or a rental must know its house id. To ask my question in another manner, how would you, as a human, make the connection between the two tables.
leewad
Forum Commoner
Posts: 91
Joined: Tue May 11, 2004 8:32 am

Post by leewad »

As a Human? I am paying attention moron..... i mean Mordred, I`m only asking how i can display details from 2 tables, i dont know to to connect to 2 tables hence my post,

I need a query something like

Code: Select all

SELECT * FROM rentals, Houses WHERE ((rentals.id <>'') AND (Houses.Country ='ES')))

If its not possible then i`ll think of something else
Grim...
DevNet Resident
Posts: 1445
Joined: Tue May 18, 2004 5:32 am
Location: London, UK

Post by Grim... »

It is entirely possible, but I think your attitude just waved goodbye to any chance of getting help.
Impressively, you still didn't answer the question he asked.

To make it simpler, if I had a table with user details, and a table about books they might have written, I could have:

Code: Select all

user
------
id
name
email

books
-------
id
author
title
In the books table, 'author' would be the same as the 'id' field in user. Get it? You need some way of telling a database about the relationship between two tables in the data itself before you can join them together.
leewad
Forum Commoner
Posts: 91
Joined: Tue May 11, 2004 8:32 am

Post by leewad »

Thanks for a normal reply Grim,

There is no connection between the 2 tables, the only thing i need is to display data from 2 different tables,

Lets say i have 2 different tables

Code: Select all

user 
------ 
table1
name 
email
and then

Code: Select all

table
------- 
reference
name
email
Is it possible to display details from both tables together?
Grim...
DevNet Resident
Posts: 1445
Joined: Tue May 18, 2004 5:32 am
Location: London, UK

Post by Grim... »

Are they in exactly the same order or something, then?
As in the 51st house in the Houses table is the same as the 51st house in the rentals table?

If they're not, I don't see how you expect MySQL to know which house relates to which rental :?
leewad
Forum Commoner
Posts: 91
Joined: Tue May 11, 2004 8:32 am

Post by leewad »

I think we have crossed wires here and i may have used the incorrect term join tables, none of the tables refer to each other they both contain totally different properties.

I need to query which will find and display all properties in rentals and all properties in houses which has a value of 'ES'

for example something like this but this doesn`t work

Code: Select all

SELECT *
FROM Houses, rentals
WHERE ( Houses.Country = 'ES' and rentals.area = 'ES' );
so if there were 30 properties in TABLE HOUSES with the country field set at 'ES' and 24 properties in RENTALS TABLE with the area field set at 'ES' then the query would say 54 found.
Grim...
DevNet Resident
Posts: 1445
Joined: Tue May 18, 2004 5:32 am
Location: London, UK

Post by Grim... »

Use a UNION.

Code: Select all

(
SELECT * FROM houses WHERE this = 'that'
)
UNION
(
SELECT * FROM rental WHERE this = 'that'
)
ORDER BY whatever
Post Reply