#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2011
    Posts
    6
    Rep Power
    0

    My sql query help needed


    i am using xampp with phpmyadmin and i am following a tutorial to make a train time table,but the query is not working,

    the idea is to write a query that will show train timetable depending upon the user selection of source and destination station,

    my tables are
    StationTbl
    ------------
    StnName
    StnCity
    StnCode - {Primary Key}

    TrainTbl
    ---------
    TrnName
    TrnNumber - {Primary Key}
    DaysofWeek

    TrainHopTbl
    --------------
    TrnNumber - {Primary Key}
    StationCode - {Primary Key}
    ArrTime
    DepTime
    HopIndex


    and i am getting this error
    Error
    SQL query: Documentation

    SELECT src.TrnNumber, srcSt.StnName AS SourceStation, srcSt.StnCity AS SourceCity, src.DepTime, destSt.StnName AS DestinationStation, destSt.StnCity AS DestinationCity, dest.ArrTime, (
    abs( dest.HopIndex - src.HopIndex )
    ) AS Stops
    FROM TrainHopTbl src
    INNER JOIN TrainHopTbl dest ON src.TrnNumber = dest.TrnNumber
    INNER JOIN StationTbl srcSt ON src.StnCode = srcSt.StationCode
    INNER JOIN StationTbl destSt ON dest.StnCode = destSt.StationCode
    WHERE src.StnCode = 'WDC'
    AND dest.StnCode = 'NYC'
    AND src.HopIndex < dest.HopIndex
    ORDER BY Stops ASC , DepTime ASC
    LIMIT 0 , 30

    MySQL said: Documentation
    #1054 - Unknown column 'dest.HopIndex' in 'field list'

    i am new to this community and i really appreciate the help you guys are offering,thanks a lot in advance.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    do you need help understanding what the error message is telling you?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2011
    Posts
    6
    Rep Power
    0
    Originally Posted by r937
    do you need help understanding what the error message is telling you?
    any help regarding the query would be great,i cant post link otherwise i would post the link of the tutorial.

    a general direction about how can i write a query to achieve train timetable would be wonderful.

    and thank you.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,928
    Rep Power
    378
    I can't find the tutorial to which you refer, but this thread looks like it has answered your specific problem already...

    http://stackoverflow.com/questions/913661/database-design-for-transport-timetable-system
  8. #5
  9. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    Originally Posted by mualanj
    i cant post link otherwise i would post the link of the tutorial.
    is this it here?

    http://stackoverflow.com/questions/9...metable-system

    note exactly same query, including source and destination stations

    this apparently was some guy's personal project a couple years ago, not a tutorial
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2011
    Posts
    6
    Rep Power
    0
    yes it is,i am actually trying to test the given solution in stack overflow,and with my limited knowledge i cant understand why the query isnot working,

    or what is it need to be done to make it work,

    thank you guys.
  12. #7
  13. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    please do a SHOW CREATE TABLE for the trainhopstbl table table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2011
    Posts
    6
    Rep Power
    0
    well,there it is


    TrnNumber -int (20) -PK
    StationCode -varchar(200)-PK
    ArrTime-varchar(200)
    DepTime-varchar(200)
    HopIndex-int(20) indexed
  16. #9
  17. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    sorry, no, i meant for you to run this query and show the results --
    Code:
    SHOW CREATE TABLE TrainHopTbl
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  18. #10
  19. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2011
    Posts
    6
    Rep Power
    0
    Originally Posted by r937
    sorry, no, i meant for you to run this query and show the results --
    Code:
    SHOW CREATE TABLE TrainHopTbl
    i cant exactly run this query as my xampp is crashed,i am guessing you are wanting to see my trainhoptable structure ,
    which i already posted,

    thank you.
  20. #11
  21. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    Originally Posted by mualanj
    i am guessing you are wanting to see my trainhoptable structure ,
    which i already posted,
    yeah, that looks okay

    without reproducing the tables and testing the query myself, i'm sorry, i have no idea why you're getting the unknown column error message
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  22. #12
  23. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2011
    Posts
    6
    Rep Power
    0
    well, i guess i am stuck with it then,

    anyway thank you for the effort, really appreciate it.

    and if you have any other suggestion or tutorial on the subject matter,don't hesitate to tell.

IMN logo majestic logo threadwatch logo seochat tools logo