Results 1 to 3 of 3

Thread: help writing SQL query including Join and Subquery
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular baitmaster's Avatar
    Join Date
    Mar 2009
    Location
    bristol, england
    Posts
    2,027
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    1 Thread(s)

    Default help writing SQL query including Join and Subquery

    Iím working with SQL in Excel, and itís working well. Now I want to add a new query thatís slightly more complex than the others and is outside my limited SQL comfort range.

    I have 3 tables (worksheets):
    • Table1 contains JourneyRef, RouteNum, DepTime, ArrTime
    • Table 2 contains RouteNum, Origin & Destination
    • Table 3 contains Location1, Location2 and timeTaken


    RouteNum is the same in several tables. Location1 equates to Destination and Location2 equates to Origin

    I want to find the whole record in table 1 where (DepTime less timeTaken) is the earliest possible but greater than a given time X
    I can see I need to do some joins and probably a subquery but Iím unsure the best approach. Can anyone please write a single SQL query that will do this?

    Thanks
    Baitmaster G



    Always save your work before running code from me, I probably haven't tested it and I'm not responsible when you lose all your work :p
    I aim to improve understanding so you can develop your own solutions, not do all your work for you. Write a clear requirement and you're more likely to get help

  2. #2
    Board Regular baitmaster's Avatar
    Join Date
    Mar 2009
    Location
    bristol, england
    Posts
    2,027
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    1 Thread(s)

    Default Re: help writing SQL query including Join and Subquery

    I've made some progress and am nearly there but have encountered a couple of SQL error messages. I'm now at:
    Code:
    SELECT [J!JourneyRef], [J!RouteNum], [J!DepTime], [J!ArrTime], [J!DepTime] - [A!TimeTaken] AS Soonest
    FROM (([journey_data$] J
    LEFT JOIN [routes$] R
        ON ([J!RouteNum] = [R!RouteNum]))
    LEFT JOIN [alternatives$] A
        ON ([A!Location1] = [R!Destination]))
    WHERE [J!JourneyRef] is not null
    ORDER BY Soonest;
    The brackets were added around the FROM /JOIN clauses due to the error "syntax error (missing operator) in query expression..." and then I added some more brackets around the ON clauses due to the error "sql join expression not supported" as suggested by article found here

    I'm still getting the error message "sql join expression not supported". Does anybody know why?
    Baitmaster G



    Always save your work before running code from me, I probably haven't tested it and I'm not responsible when you lose all your work :p
    I aim to improve understanding so you can develop your own solutions, not do all your work for you. Write a clear requirement and you're more likely to get help

  3. #3
    Board Regular baitmaster's Avatar
    Join Date
    Mar 2009
    Location
    bristol, england
    Posts
    2,027
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    1 Thread(s)

    Default Re: help writing SQL query including Join and Subquery

    I get the same error with the much-simplified
    Code:
    SELECT [J!*] FROM ([journey_data$] J LEFT JOIN [routes$] R ON ([J!RouteNum] = [R!RouteNum]))
    Baitmaster G



    Always save your work before running code from me, I probably haven't tested it and I'm not responsible when you lose all your work :p
    I aim to improve understanding so you can develop your own solutions, not do all your work for you. Write a clear requirement and you're more likely to get help

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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