Hi, i don't quite understand joins ..

if i were to search for 'propSuburb' which exists in two tables (suburb, and property) do i have to do a join to show my results properly??
if so.. how do i do an inner join with this query.. ??

<php>
$query = "Select distinct propSuburb from property
where propType='$propType' ORDER BY propSuburb";
</php>

Keira, in the example you are describing, there is no need for a join; only two queries.

A join is more appropriate when you have a relation between two tables. For example, imagine I have three tables.

An Actors table with just two fields, actorId (primary key) and actorName.

A Movies table with just two fields, movieId (primary key) and movieName.

An Acted table with just three fields, id (primary key) actorId and movieId.

Now the Actors table would be filled with actors and actresses but nothing about their movie work. Movies would be filled movies, but no information on who acted in them. The Acted table bridges the two and has a relationship with both.

So let's say we had a movie, Jaws and we wanted to find out all the actors in it.

PHP Code:

SELECT m.movieName, a.actorName, c.* FROM Movies m INNER JOIN Acted c on m.movieId = c.movieId INNER JOIN Actors a on a.actorId = c.actorId WHERE m.movieName = 'Jaws' 


Now this is a bit more complex example than I needed (sorry, thought it up on the fly) because it has two joins rather than one. The final result will be a result set with 5 fields, or columns: movieName (Jaws), actorName (containing all the actors in the movie) and the three fields from the Acted table (which aren't necessary, but I included them as an illustration of the concept).

I hope I helped rather than made it worse.....

But in your example, you have two similar tables who really don't have a relationship with each other, right? They are independent lists of properties? I'm making that assumption. If that is the case, then you can get a complete list of properties of a given property type by running two separate queries.

okay your example did confuse me a bit with the two joins, but.. i'll understand that one day perhaps..
as far as my question goes though, my query should be fine the way it is, or do you recommend i do two queries??

i thought that mysql was just really similar to microsoft access, so i think im confusing myself a lot..

question/ say i have a table called property, and the user enters the information about a property including an address, suburb, etc..
the type of program im using to search the database, and to enter new data requires you to first search via suburb, so i thought maybe i should make a new table soley for 'suburb'.. so the user can choose from an existing suburb or enter a new one into the database when entering data for a new property. ..

er.. is this making sense?
what i want to know is, do i really need another table for suburb?? i cant get my head around it.. i really am really really new at this! lol... sorry!!!

keira,

If you are going to have thousands of records in the property table, then perhaps you would want a separate table for your suburb list. In that case, you would have a two-column table, suburbId (an int primary key) and suburb name. Then in your property table you would put the primary key of the suburb the property is in (not the name), so that column would be an int.

Then, in that case, to pull all the properties in a given suburb, you would pull it like this:


SELECT p.*, s.* FROM properties p INNER JOIN suburbs s on p.suburbId = s.suburbId where s.suburbName = 'ALEXANDRIA'


You will also be wanting to list your available suburbs and this is easily done by

SELECT * FROM suburbs

thanks Kyrnt,
that helps a lot!!! I might even be closer to understanding this after all!!