SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Zealot
    Join Date
    May 2004
    Location
    London
    Posts
    155
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    an sql table join conumdrum

    Hi there

    I've got a php sign up process on my site. The php writes to a mysql table with the following columns..........this table's called members

    ref_id
    firstname
    lastname
    email
    username
    passwd
    source (how they found out about my site)

    Once they've signed up they can set up an ad for their Spanish property using form fields ..........they write to a table with these columns .................this table's called properties...............

    ref_id
    username (the same as that in the members table)
    title_text
    opening_text
    main_text
    type
    location
    sleeps
    double_bedrooms
    single_bedrooms
    features
    amenities
    other_info
    pricing_item1
    pricing_item2
    pricing_item3
    pricing_item4
    contact_details1
    contact_details2
    contact_details3
    contact_details4
    thumb
    ready
    agent


    you get the idea...............one member has one property in the properties table.

    My question ...........supposing I have the ref_id of a property in the properties table...how do I get the email for the member whose property it is? Is there an sql syntax table join solution that can get it for me?

    all the best!

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    yes, you could do a join, but if all you want is the email, you don't need to

    after all, you said you already have the ref_id value
    Code:
    select email
      from members
     where ref_id = value
    of course, this begs the question of how you got the ref_id value in the first place, and whether you actually need to retrieve the row from the properties table too, because you should get both rows at the same time
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot
    Join Date
    May 2004
    Location
    London
    Posts
    155
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi thanks alot for this but what I have is the ref_id for the property in the properties table.........as opposed to the ref_id in the members table......sorry if I never mentioned but the two are different.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    okay, my bad, you did say the username, i shoulda seen that
    Code:
    select members.email
      from properties
    inner
      join members
        on properties.username
         = members.username
     where properties.ref_id = value
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    Non-Member bronze trophy geniusgoalie's Avatar
    Join Date
    Sep 2004
    Location
    Buffalo, USA
    Posts
    979
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    rudy in the php forum! What a surprise!

  6. #6
    SitePoint Zealot
    Join Date
    May 2004
    Location
    London
    Posts
    155
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi a massive thanks for that! I just tried it in an ssh environment and it worked!
    thanks alot............I'd been trying to figure it out myself all night!!

    All the best to ya!

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by geniusgoalie
    rudy in the php forum! What a surprise!
    yeah, when i peruse "today's topics" (see link, top right corner), if there's a subject line with "sql" in it, i'll usually take a look

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •