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

[sdkautoa]

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

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

[MTP]

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.

[sdkautoa]

I need to capture the most recent date each player played.
Thanks Steve Kotsiris

[Villeroy]

Code: Select all

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.

[sdkautoa]

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.

[Villeroy]

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

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

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.

[sdkautoa]

[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.

[Villeroy]

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 ...

[eremmel]

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

     (
    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

[sdkautoa]

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.