Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2004
    Posts
    2

    Unanswered: query help from two tables..... anyone...

    hello -

    I have two tables called "sites" and "hosts."

    This is my hosts table:

    id | hostname
    ===========
    1 | athens
    2 | soccer
    3 | football
    4 | baseball

    THis is my sites table:

    id | host_id_1 | host_id_2 | expires
    ==============================
    1 | 4 | 1 | 2008-12-31
    2 | 3 | 2 | 2003-12-12
    3 | 2 | 1 | 2005-10-10
    4 | 4 | 3 | 2007-10-10



    Now, I want to see all records for "athens" which is id=1 from host table. Now, I want to see and matching all instances of id "1" with any other id's from sites tables. I trying to get this.....

    baseball - athen - 2008-12-31
    soccer - athens - 2005-10-10

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Good for you.
    What have you tried?
    What were the results?
    Here is a free clue that might be part of a correct solution

    FROM HOSTS H1, HOSTS H2, SITES
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Aug 2004
    Posts
    2
    this is what I have tried... but does not seem to work. Also, need to show the lowest date between the two which I have not figured out yet between the two ID's in the site table.

    -----

    mysql> select distinct h.hostname, h1.hostname, e.expires from hosts e, hosts h, hosts h1, sites s where (s.host_id_1='1' and h.id='1') and (h.id != h1.id);

    +----------+----------+------------+
    | hostname | hostname | expires |
    +----------+----------+------------+
    | master19 | deed | 2007-12-31 |
    | master19 | deed | 2005-12-31 |
    | master19 | deed | 2006-12-31 |
    | master19 | slop | 2007-12-31 |
    | master19 | slop | 2005-12-31 |
    | master19 | slop | 2006-12-31 |
    +----------+----------+------------+

Posting Permissions

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