Include column from one table in another result set

Databases
1

Hi there,

I have two tables:

tblRentalUnits - this table consists of numerous fields with one in particular called iRentID. This contains an integer value that maps to another table that contains a column called sName that contains the human readable name of the community in which this rental resides.

tblCommunities - contains numerous fields, with it’s ID (unique) that is referenced to from tblRentalUnits.

Basically what I’m looking to do is select ALL the rental units (from tblRentalUntis), but also include/add the community name (from the ID linkage - iRentID as stored in tblCommunities). This will then allow me to sort by community alphabetically.

I’ve looked at doing join’s etc, but I’m not so savy in mySQL to achieve this with one select statement.

Any help would be greatly appreciated.

Thanks!

NOTE:

After some tinkering I found this kinda works:

SELECT * FROM tblRentalUnits INNER JOIN tblCommunities ON tblRentalUnits.iRentID = tblCommunities.ID;

However, now I have all columns from both tables (which makes sense based on the JOIN) in one result set, however I JUST want the sName column from tblCommunities and all the others from tblRentalUnits

FINAL NOTE:

Okay so after even more tinkering I found that this looks like it should work. Does anyone see why it wouldn’t?

SELECT ru.*, c.sName AS sCommName FROM tblRentalUnits AS ru, tblCommunities AS c WHERE ru.iRentID = c.id;

2

yes you have to use the name of wach column you want to select seperated by commas instead of the * then you can use as to rename the column you want to. Like this…

SELECT units.colname1 ,units.colname2 ,units.sName, coms.sName as sCommName
FROM tblRentalUnits as units
INNER JOIN tblCommunities as coms ON units.iRentID = coms.ID

I think that should work.

Hope this helps.

Mike