SitePoint Sponsor

User Tag List

Results 1 to 2 of 2

Hybrid View

  1. #1
    SitePoint Addict scoobasteve1982's Avatar
    Join Date
    Apr 2007
    Posts
    333
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    include column from one table in another result set

    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. #2
    SitePoint Enthusiast MetalHippy's Avatar
    Join Date
    Nov 2008
    Posts
    63
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by scoobasteve1982 View Post
    Hi there,

    Can I rename the column ID that I'm joining? So the sName field from tblCommunities would be sCommName
    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
    One Life - Live It


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •