[Solved] Need a little help with this join Code SQL

Creating tables and queries

[Solved] Need a little help with this join Code SQL

Postby sdkautoa » Wed Mar 02, 2016 10:53 am

Is there a way to get the date to show up in my SELECT statement with this code? I try to include it and it's table but keep on getting each player with a lot of duplicate outputs. I'v been fighting with this for a while with no luck. The only way I was able to do it, was to run it in SQL commend and have it make a table with the data and from that I was able to query it to include the date in my report.
Code: Select all   Expand viewCollapse view
Select   
   "ID",
    "LastName",
   "FirstName",                        
    "#",                              
    "Last_three_Average",
     108 - "Last_three_Average" AS "Points",
      CASEWHEN("Last_three_Average" > 96, 5,
         CASEWHEN("Last_three_Average" > 92, 4,
            CASEWHEN("Last_three_Average" > 87, 3,
               CASEWHEN("Last_three_Average" > 83, 2,1)))) As "Handicap"                     
FROM
    "GolferContactTBL"

RIGHT JOIN                                     --I switched the Left Join to a Right Join to Get players without rounds played off the list.
    (
    SELECT
        "GolferID",                              --here we're going to count all * the GolferScore per GolferID and put it
        COUNT( * ) as "#",                        --in the Last_three_Average colume after we average them
        AVG( "GolferScore") "Last_three_Average"
    FROM
        (
        SELECT                                 --and those are coming from another select of columes that have GolferID, GolfDate, and GolferScore
            "GolferID",                           --That are from the RoundTBL A  and a left Join on a copy of RoundTBL B
            "GolfDate",                           --So then we check with ON if the GolfDate on A is less then or equal to GolfDate on B AND(Both have
            "GolferScore"                        --have to be true) if GolferID A is equal to GolferID B.
        FROM
            "RoundTBL" "A"
        LEFT JOIN
            "RoundTBL" "B"
        ON
            "A"."GolfDate" <= "B"."GolfDate"
        AND
            "A"."GolferID" = "B"."GolferID"
   
        GROUP BY
            "GolferID",
            "GolfDate",
            "GolferScore"
      
            
        HAVING COUNT( * )  <= 3

      
        )
    GROUP BY         
        "GolferID"
    ) AS Q3
ON
    "GolferContactTBL"."ID" = "Q3"."GolferID"
   
ORDER BY
     "LastName"


So close but can't seem to get over the hump.
Thanks in advance
Steve Kotsiris
Last edited by sdkautoa on Fri Mar 04, 2016 9:49 am, edited 1 time in total.
OpenOffice 4.1.2 on Windows Vista
sdkautoa
 
Posts: 14
Joined: Sat Jan 23, 2016 11:52 am

Re: Need a little help with this join Code SQL

Postby MTP » Wed Mar 02, 2016 6:14 pm

What date do you want to see? It looks to me like you are displaying summary results that span a range of dates, and I'm not sure if you want the earliest date, latest date, or some other date with significance.
OpenOffice 4.1.1 on Windows 10, HSQLDB 1.8 split database
User avatar
MTP
Volunteer
 
Posts: 1620
Joined: Mon Sep 10, 2012 7:31 pm
Location: Midwest USA

Re: Need a little help with this join Code SQL

Postby sdkautoa » Wed Mar 02, 2016 9:13 pm

I need to capture the most recent date each player played.
Thanks Steve Kotsiris
OpenOffice 4.1.2 on Windows Vista
sdkautoa
 
Posts: 14
Joined: Sat Jan 23, 2016 11:52 am

Re: Need a little help with this join Code SQL

Postby Villeroy » Wed Mar 02, 2016 10:22 pm

Code: Select all   Expand viewCollapse view
SELECT "GolferID", MAX("Date") FROM "Something" WHERE "Date" <= CURRENT_DATE GROUP BY "GolferID"

returns the most recent dates up to date (not in future) for each GolferID.
Join that with whatever.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 27760
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Need a little help with this join Code SQL

Postby sdkautoa » Thu Mar 03, 2016 2:47 pm

So there is no way to do this in the posted query while getting all the other info out of the query that I need? I was hoping to get it done in one query, is that even possible? I'm kind of new to SQL and it's been a very long time that I done any type of programming. Right now I'm making a table of this queries results and then using Max(Date) to get what I need.
Thanks again for your help Villeroy.
OpenOffice 4.1.2 on Windows Vista
sdkautoa
 
Posts: 14
Joined: Sat Jan 23, 2016 11:52 am

Re: Need a little help with this join Code SQL

Postby Villeroy » Thu Mar 03, 2016 9:51 pm

This is similar to the nested function calls of a complex spreadsheet formula or any other problem in functional programming. Where a spreadsheet function returns a 2D-array or single value, a SELECT statement returns a record set. JOINing multiple queries makes everything simpler to understand like splitting up a complex spreadsheet formula into multiple cells with intermediary results.
Code: Select all   Expand viewCollapse view
SELECT X.A. Y.B FROM "qry1" AS X JOIN "qry2" AS Y on X.A=Y.A

can be written as a single query definition like this:
Code: Select all   Expand viewCollapse view
SELECT X.A. Y.B FROM (SELECT ... FROM ...) AS X JOIN (SELECT ... FROM ...) AS Y on X.A=Y.A

The query names are replaced by their respective statements in braces.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 27760
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Need a little help with this join Code SQL

Postby sdkautoa » Thu Mar 03, 2016 11:14 pm

[Solved] Thanks again Villeroy, I didn't know that you can join queries, that makes sense now. Thanks again for all your help.
Happy again Steve Kotsiris.
OpenOffice 4.1.2 on Windows Vista
sdkautoa
 
Posts: 14
Joined: Sat Jan 23, 2016 11:52 am

Re: Need a little help with this join Code SQL

Postby Villeroy » Thu Mar 03, 2016 11:39 pm

Both variants are equivalent within the Base context. But Base is not the only program involved. The underlying database is the second application you work with. It answers SQL statements like a server application answers server requests. However, it does not know that this statement is named "Query1" in the calling program and that there are "Query2" and "Query3". This is not a problem when you run the query in "parsed mode". Base will pass all necessary information to the database application.
When you run the first variant (with query names) in direct SQL mode, the database application receives the bare SQL string and will not understand what "Query1" means. The called application has no access to the calling application. In this situation you either run the nested query as one direct SQL statement or save the split queries as views. Views are SELECT statements that are stored in the realm of the database application. HSQL will understand SELECT X,Y FROM "View1" JOIN "View2" ON ...
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 27760
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Need a little help with this join Code SQL

Postby eremmel » Thu Mar 03, 2016 11:57 pm

Hi Steve, You can get your last date played also via the derived table Q3 (only inner section of your query shown):
Code: Select all   Expand viewCollapse view
     (
    SELECT
        "GolferID",                              --here we're going to count all * the GolferScore per GolferID and put it
        COUNT( * ) as "#",                        --in the Last_three_Average colume after we average them
        AVG( "GolferScore") "Last_three_Average",
        MAX( "GolfDate" ) "Last_date_played"
    FROM
        (
        SELECT "A"."GolferID", "A"."GolfDate", "A"."GolferScore"
          FROM "RoundTBL" "A"
        LEFT JOIN "RoundTBL" "B"
                ON "A"."GolfDate" <= "B"."GolfDate"
                     AND "A"."GolferID" = "B"."GolferID"
        GROUP BY "A"."GolferID", "A"."GolfDate", "A"."GolferScore"
        HAVING COUNT( * )  <= 3
        ) AS Q2
    GROUP BY "GolferID"
    ) AS Q3
It's Microsoft marketing that tells you computers are qualified for non-technicians
eremmel
 
Posts: 1041
Joined: Tue Dec 30, 2008 1:15 am

Re: Need a little help with this join Code SQL

Postby sdkautoa » Fri Mar 04, 2016 9:49 am

Yahoo!!! You rock eremmel. That worked like a charm.
Thank you soooooo much to both of you for all your help and for helping me in the understanding of SQL.
Very thankful Steve Kotsiris.
OpenOffice 4.1.2 on Windows Vista
sdkautoa
 
Posts: 14
Joined: Sat Jan 23, 2016 11:52 am


Return to Tables & Queries

Who is online

Users browsing this forum: MSN [Bot] and 2 guests