I’ve been using chatgpt to alter this query
SELECT DISTINCT "Plots"."name" AS "Plot", "latin_name", "start_month", "start_day", "end_month", "end_day", "practice", "detail", "phenological_event", "Common_name" FROM "plot_annual_cultivation_dates_view" JOIN "Plots" ON "plot_annual_cultivation_dates_view"."Plot" = "Plots"."plot_id" WHERE CURRENT_DATE <= CAST( YEAR( CURRENT_DATE ) || '-' || RIGHT( '0' || "end_month", 2 ) || '-' || RIGHT( '0' || "end_day", 2 ) AS DATE ) AND CURRENT_DATE BETWEEN CAST( CASE WHEN "start_month" <= "end_month" THEN YEAR( CURRENT_DATE ) ELSE YEAR( CURRENT_DATE ) - 1 END || '-' || RIGHT( '0' || "start_month", 2 ) || '-' || RIGHT( '0' || "start_day", 2 ) AS DATE ) AND CAST( CASE WHEN "start_month" <= "end_month" THEN YEAR( CURRENT_DATE ) ELSE YEAR( CURRENT_DATE ) + 1 END || '-' || RIGHT( '0' || "end_month", 2 ) || '-' || RIGHT( '0' || "end_day", 2 ) AS DATE ) ORDER BY "end_month" ASC, "end_day" ASC, "practice" ASC
so that it only returns like @Villeroy demonstrates
SELECT "Plots"."name", "beds"."bed_name", "plants".*
FROM "Plot", "Plots", "beds", "plants"
WHERE "Plot"."Plot" = "Plots"."plot_id"
AND "beds"."plot_id" = "Plots"."plot_id"
AND "plants"."bed_id" = "beds"."bed_id"
AND "beds"."bed_name" = 'seeds'
ORDER BY "plants"."latin_name" ASC
I’m wanting the qwery on the right of the screenshot to return only the plants that are in the form on the left of the screenshot that also meet the date criterias. The query returns Oemlaria cerasiformis which is not in the form on the screenshot on the left. The query should only return the plants that are in the form on the left.
There are other plots that have plants with the bed_name “seeds” and i don’t want to see those ones. Like I said I want the query only to return results where “beds.plot_id”=“Plot.Plot”
here is the sql statement that Chatgpt made:
SELECT DISTINCT “Plots”.“name” AS “Plot”, “beds”.“bed_name” AS “Bed”, “latin_name”, “start_month”, “start_day”, “end_month”, “end_day”, “practice”, “detail”, “phenological_event”, “Common_name” FROM “plot_annual_cultivation_dates_view” JOIN “Plots” ON “plot_annual_cultivation_dates_view”.“Plot” = “Plots”.“plot_id” JOIN “beds” ON “beds”.“plot_id” = “Plots”.“plot_id” WHERE “beds”.“bed_name” = ‘seeds’ AND CURRENT_DATE BETWEEN CAST( CASE WHEN “start_month” <= “end_month” THEN YEAR( CURRENT_DATE ) || ‘-’ || LPAD( “start_month”, 2, ‘0’ ) || ‘-’ || LPAD( “start_day”, 2, ‘0’ ) ELSE ( YEAR( CURRENT_DATE ) - 1 ) || ‘-’ || LPAD( “start_month”, 2, ‘0’ ) || ‘-’ || LPAD( “start_day”, 2, ‘0’ ) END AS DATE ) AND CAST( CASE WHEN “start_month” <= “end_month” THEN YEAR( CURRENT_DATE ) || ‘-’ || LPAD( “end_month”, 2, ‘0’ ) || ‘-’ || LPAD( “end_day”, 2, ‘0’ ) ELSE ( YEAR( CURRENT_DATE ) + 1 ) || ‘-’ || LPAD( “end_month”, 2, ‘0’ ) || ‘-’ || LPAD( “end_day”, 2, ‘0’ ) END AS DATE ) AND LOWER ( “practice” ) LIKE ‘%seed%’ ORDER BY “end_month”, “end_day”, “practice”
