SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Member
    Join Date
    Jul 2010
    Location
    Kenya
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SELECT query that JOINs four tables

    I have created four tables in a MySQL database
    town (town_id, town_name)
    area ( area_id, area_name, town_id)
    station (sta_id, sta_name)
    prices (pid, dollar, sta_name, area_id)

    A: I want to get a list of 10 distinct towns/cities, each with a corresponding station where the price in dollars is the cheapest.
    For example:
    1. Melbourne, Airport, $20
    2. Nairobi, JKIA, $50

    B: Suppose am given a town or city say Melbourne. I want to get a list of 10 distinct areas (in this town) where price is cheapest.
    City: Melbourne
    1. Airport - $ 20
    2. Southbank - $ 23
    3. Windsor -$ 24.32


    Thank you in advance.

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,506
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    What have you got so far?

  3. #3
    SitePoint Member
    Join Date
    Jul 2010
    Location
    Kenya
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    What have you got so far?
    For A above, I am using the query below, the problem is that some towns appear several times.

    PHP Code:
    <?php
    $query 
    "SELECT town.town_name, station.sta_name, prices.dollar FROM town,station,prices,area WHERE prices.sta_name = station.sta_name AND prices.sta_name=station.sta_name AND area.town_id=town.town_id ORDER BY prices.dollar ASC LIMIT 10";
    $result = @mysql_query($query,$db) or die ("Error in query: $query. " mysql_error());
    echo 
    "<ul class=\"list\">";
    while (list(
    $town_name,$sta_name,$dollar) = mysql_fetch_row($result))
    {
    echo 
    "<li><a href=\"./?town_name=$town_name\"><b>$town_name - $sta_name</b><br />Ksh. $dollar</a></li>";  
    }
    echo 
    "</ul>"
    mysql_free_result($result);
    ?>
    For B above I am running the query below, it is not giving the correct dollar for a given station:
    PHP Code:
    <?php  
    if (!isset($_REQUEST['town_name'])){$town 'Nairobi';} else {$town $_REQUEST['town_name'];}
    ?>
    <div class="content"><h3><?php echo $town ?></h3>
    <?php
    $query 
    "SELECT area.area_name, station.sta_name, prices.dollar FROM area, station,prices, town WHERE prices.area_id IN (SELECT DISTINCT area_id FROM prices) AND prices.sta_name = station.sta_name AND area.town_id=town.town_id AND town.town_name = '$town' ORDER BY dollar ASC LIMIT 10";
    $result = @mysql_query($query,$db) or die ("Error in query: $query. " mysql_error());
    echo 
    "<ul class=\"list\">";
    while (list(
    $area_name,$sta_name,$dollar) = mysql_fetch_row($result))
    {
    echo 
    "<li><a href=\"area.php?area_name=$area_name\"><b>$area_name - $sta_name</b><br />Ksh. $dollar</a></li>";  
    }
    echo 
    "</ul>"
    mysql_free_result($result);
    ?>


Tags for this Thread

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
  •