[Solved] Criteria error

Creating tables and queries
Post Reply
gkick
Posts: 324
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile

[Solved] Criteria error

Post by gkick »

Hello,

going in circles with a basic query definition.

In a query I would like to show on which dates a client is booked and on which days the client is still available.

There is something wrong in my query because it does not display 1/10 for Black , likewise if the filter is set to Smith then 7/10 does not show up.

Code: Select all

SELECT "tblCalendar"."date", "tblBooking"."client", "tblBooking"."bdate", "tblFilter"."client" 
 FROM { oj "tblBooking" RIGHT OUTER JOIN "tblCalendar" ON "tblBooking"."bdate" = "tblCalendar"."date" }, "tblFilter" 
 WHERE ( "tblBooking"."client" = "tblFilter"."client" OR "tblBooking"."bdate" IS NULL )
Thanks for pointers

db attached
Attachments
dev.odb
(4.46 KiB) Downloaded 160 times
Last edited by gkick on Fri Oct 22, 2021 5:53 am, edited 1 time in total.
Libre Office 6.4.6 on Windows 10 HSQL 2.51 backend
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Criteria error

Post by FJCC »

I think the problem is that your outer join makes a table where "client" can be Smith, Black, Mayer or Null and then your WHERE statement filters out rows where "client" does not equal the value stored in tblFilter. Try running your query with the WHERE clause removed to see this. If the filter is set to Smith, dates where Black or Mayer have bookings will be eliminated. I would filter the bookings table before doing the join, as in this example

Code: Select all

SELECT "tblCalendar"."date", "T1"."client", "T1"."bdate" 
FROM 
  { oj 
   (SELECT "tblBooking"."client", "tblBooking"."bdate" 
    FROM 
      "tblBooking" 
      INNER JOIN 
      "tblFilter" ON "tblBooking"."client" = "tblFilter"."client"
   ) AS "T1" 
   RIGHT OUTER JOIN 
   "tblCalendar" 
   ON "T1"."bdate" = "tblCalendar"."date" } 
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
gkick
Posts: 324
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile

[Solved] Re: Criteria error

Post by gkick »

@FJCC

Excellent, thank you- filtering before the join does it
Libre Office 6.4.6 on Windows 10 HSQL 2.51 backend
Post Reply