Re: Using view returns different results for same query

Your database can be simplified by using a single "Reservations" table and adding the "ResType" as a column to contain 'Waterfront' or 'Camping' depending on the reservation type.

Fred

On Thu, Aug 29, 2019, at 17:46, Andrew Richardson via Hsqldb-user wrote:
> 
> Please disregard this thread. I found my view was not getting dropped then recreated in script as I had assumed. Now view is working as expected.
> Cheers,
> Andrew
> 
> On Thursday, August 29, 2019, 12:33:26 PM EDT, Andrew Richardson via Hsqldb-user <[email protected]> wrote:
> 
> 
> 
> Sorry for the spam. I upgraded to HSQLDB v2.5.0, same results.
> 
> On Thursday, August 29, 2019, 11:57:42 AM EDT, Andrew Richardson <[email protected]> wrote:
> 
> 
> Hello All
> I have a campground application using HSQL Database Engine v. 2.4.1 in client/server configuration. In SQLToool, I crafted the SQL query below and it return 263 rows as expected. I then created a view using syntax CREATE VIEW PUBLIC."Transactions" AS <same query> , which was successful. However, when I query the view I get 2900+ rows. Seems like the joins are not executed correctly when view is used.
> Can someone shred light on why this might be? Possible bug?
> Thanks,
> Andrew Richardson 
> 
> 
> SELECT 'Waterfront' "ResType", 
> "W".ID "ID",
> "P"."TransactionDate" "Date",
> "C"."FullName" "Name",
> "P"."PaymentType" "PaymentType",
> "P"."CashierInitials" "Initials",
> "P"."Amount" "Amount",
> "P"."CCInvNumber" "Inv#",
> "P"."CCApprovalCode" "Approval" 
> FROM PUBLIC."WaterfrontReservations" "W",
>  PUBLIC."Payments" "P", 
> PUBLIC."Customers" "C" 
> WHERE "W"."CustomerID" = "C"."ID"
>  AND ("W"."ID" = "P"."ResID" AND 'W' = "P"."ResType")
> UNION 
> SELECT 'Camping', 
> "M".ID,
> "P"."TransactionDate",
> "C"."FullName",
> "P"."PaymentType",
> "P"."CashierInitials",
> "P"."Amount",
> "P"."CCInvNumber",
> "P"."CCApprovalCode" 
> FROM PUBLIC."CampingReservations" "M",
>  PUBLIC."Payments" "P", 
> PUBLIC."Customers" "C" 
> WHERE "M"."CustomerID" = "C"."ID"
>  AND ("M"."ID" = "P"."ResID" AND 'C' = "P"."ResType")
> ORDER BY 3,4;
> 
> _______________________________________________
> Hsqldb-user mailing list
> [email protected]
> https://lists.sourceforge.net/lists/listinfo/hsqldb-user
> 
> _______________________________________________
> Hsqldb-user mailing list
> [email protected]
> https://lists.sourceforge.net/lists/listinfo/hsqldb-user
>

_______________________________________________
Hsqldb-user mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user