Hi all
I have a main table (which contains name & date) and several tables related to that table (those other tables are credit cards, accidental punches in the cash register, petty cash receipts and several more). The query I have selects all fields from all tables. However if one of those tables is empty (example, no accidental punches) then the query doesn’t return anything.
What am I missing?
Thanks
W10 with LO 7.1.4.2 and MySQL 8.0.25 backend
Hello,
Likely something to do with the association of the tables. Need at least SQL posted.
Edit:
In a Q&D test, normal select was empty as you state but worked with a Left Join. This seems as the one NULL all NULL.
A lot depends upon the results you need. Would imagine you would join on the related fields and not just select all.
Here’s my SQL
SELECT dte_tbl., overpunch., cards., cash., cheques., coupons., notes., petty cash., tips.*
FROM scratch_tickets.overpunch overpunch, scratch_tickets.dte_tbl dte_tbl, scratch_tickets.cards cards, scratch_tickets.petty cash petty cash, scratch_tickets.tips tips, scratch_tickets.cash cash, scratch_tickets.coupons coupons, scratch_tickets.acct_rec acct_rec, scratch_tickets.cheques cheques, scratch_tickets.notes notes
WHERE overpunch.dte_fk = dte_tbl.dte_id
AND cards.dte_fk = dte_tbl.dte_id
AND petty cash.dte_k = dte_tbl.dte_id
AND tips.dte_fk = dte_tbl.dte_id
AND cash.dte_fk = dte_tbl.dte_id
AND coupons.dte_fk = dte_tbl.dte_id
AND acct_rec.dte_fk = dte_tbl.dte_id
AND cheques.dte_fk = dte_tbl.dte_id
AND notes.dte_fk = dte_tbl.dte_id
Was putting together an answer. Let me also look at this SQL.
Edit:
The initial reaction to the SQL you present is that for a record to be created ALL conditions must be met (use of AND throughout). May be best to incorporate joins as in the sample within my answer.
I used the design mode for display purposes. Use that or SQL. For SQL see → SQL Joins
Hello,
This may help. If using the design mode for Queries, joins may default to an inner join.
Using these tables:
and the default inner join created, here is the result:
One record returned as Table4 only had one item to match. Changing to a left join produces:
Going a step further, empty Table4 and use inner join:
Nothing. No matches. But change same to a left join:
The issue seems to be in how you constructed your Query.
Edit 2021-07-04:
Will not get involved with accounting. Debits and credits are up to you. You can proceed with totaling each table individually and then use that to produce another total. Or take a direction I eluded to in the comment. Extract all records and use that as input to Report Builder or something of your choice.
Here are some tables I used:
Then the SQL and result:
SQL you can copy:
SELECT `dte`, `emp`,`shift`,`petty_id` As `Tran ID`, 'Petty Cash' As `Rec Type`, `petty_desc` As `Desc`, `petty_amt` As `Debit Amount`, '0.00' As `Credit Amount` FROM `dte_tbl`, `petty cash` WHERE `dte_tbl`.`dte_id` = `petty cash`.`dte_petty_fk`
Union
SELECT `dte`, `emp`,`shift`,`tips_id` As `Tran ID`, 'Tips' As `Rec Type`, `tip_receipt_nbr` As `Desc`, '0.00' As `Debit Amount`,`tips_amt` As `Credit Amount`FROM `dte_tbl`, `tips` WHERE `dte_tbl`.`dte_id` = `tips`.`dte_tips_fk`
Union
SELECT `dte`, `emp`,`shift`,`cheques_id` As `Tran ID`, 'Cheque' As `Rec Type`, `ch_desc` As `Desc`, '0.00' As `Debit Amount`,`cheque_amt` As `Credit Amount`FROM `dte_tbl`, `cheques` WHERE `dte_tbl`.`dte_id` = `cheques`.`dte_cheque_fk`
This should be sorted & can be used for balancing also. Easy to create report from with groupings at Date and employee and more if wanted.
Left join and everything works. Thanks Ratslinger
Great. Left sounds better than the right. Don’t recall when I needed a right join.
I’ve been hunting for a problem that has arisen from Left Join - duplicate records. DISTINCT doesn’t work. GROUP BY doesnt work. I’ve added an image to my original post. It looks a mess. The “Claw Machine” should have only 2 entries. OP_amount 2 entries and tips_amt should have two entries (they are all on different tables).
Not certain I understand some of these relations. For example what is the output you are expecting/looking for? Some relations are not clear. How are tips and petty cash related - by the image you are trying to put these together. Appears you are attempting to create some type of recap by employee by date for a report.
If so, my direction would be to get each item separately, create a Union between records and pass that on to Report Builder. Sorry, just thinking as typing.
I’m trying to create a deposit sheet. The first table contains date, employee and the shift (opening, closing, double). All the other tables contain different types of info that would be entered and related to the employee & date. Those other tables are mispunches (overpunch), debit/credit transactions, tips received etc.
I’ve tried making a query out of multiple queries and no luck either.
I’ve added the form I created for data entry. Each pane has it’s own table and are subforms of the date/emp table
Just a note regarding this in my first comment:
Don’t recall when I needed a right join.
Found where I use a right Join → Query for a report sample of an Invoice. Posted here → Libre Data - multi-level report
SQL was basis in my answer here in the edited section.