Using view returns different results for same query

Hello AllI 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