SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Addict
    Join Date
    Sep 2000
    Location
    Ontario, Canada
    Posts
    320
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    simple join question

    I'm running into a little problem with a JOIN and I was wondering if someone here could give me some suggestions on how I can get it to work.

    Basically, what I'd like to do is when a house is shown, grab agentname from the agents table.

    Here's my simplified db structure:

    CREATE TABLE agents (
    id int(11) DEFAULT '0' NOT NULL auto_increment,
    agentname varchar(40) NOT NULL,
    );

    CREATE TABLE homes (
    id int(11) DEFAULT '0' NOT NULL auto_increment,
    owner int(11) DEFAULT '0' NOT NULL,
    PRIMARY KEY (id),
    KEY owner (owner),
    );

    And here's what I currently have for my select statement but it produces a blank result:

    SELECT h.* FROM homes h LEFT JOIN agents a ON (h.owner = a.id) WHERE h.price>=10000000 etc. etc.

    I tried to select a.*,h.* where (h.owner=a.id) and it shows the agentname but screws up the rest of the stuff.

    Any suggestions are greatly appreciated!

    Thanks.
    Jason Dulberg
    Extreme MTB
    http://extreme.nas.net

  2. #2
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    on the first one if you're not seeing the agent it's cuz you're not selecting anything from the agent table. you have SELECT h.*. try changing it to SELECT *.

    AND you have "WHERE h.price>=10000000". do you have a price column in the homes table?
    - Matt ** Ignore old signature for now... **
    Dr.BB - Highly optimized to be 2-3x faster than the "Big 3."
    "Do not enclose numeric values in quotes -- that is very non-standard and will only work on MySQL." - MattR

  3. #3
    SitePoint Addict
    Join Date
    Sep 2000
    Location
    Ontario, Canada
    Posts
    320
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yep, h.price is part of the homes table.

    I changed the select from h.* to * and its now showing the agentname however now 2 other items that I'm taking from the query are outputted incorrectly.

    Here are a couple examples of the data that's getting messed up.

    before change: (correct)
    propview.php?owner=$owner&view=$id
    =
    propview.php?owner=5&view=9

    after change (incorrect)
    propview.php?owner=$owner&view=$id
    =
    propview.php?owner=5&view=5

    In the examples (&view=$id) is the h.id

    Thanks for your time
    Jason Dulberg
    Extreme MTB
    http://extreme.nas.net


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
  •