0

I have many tables in my inventory system like product sale, product purchase, employee salary, office/shop expense. now I want to show all the table activity in a single table. so what I did is used the union query, so that, I can print all the other table activity in a table format. which I am naming as a Cash_Report, like shop Tally book.

 SELECT `selldate`, `customerid`, `payment_taka`, `token` FROM `sell` WHERE `token` = 's_Cash'
 UNION
 SELECT `recievedate`, `cusotmer_id`, `amounts`, `bycashcheque` FROM `recevecollection` WHERE `bycashcheque` = 'rac_Cash' 
UNION 
SELECT `pay_date`, `sup_id`, `amnts`, `status` FROM `supplierpayment` WHERE `status`='pts_Cash' 
UNION 
SELECT `expiredate`, `customerid`, `amount`, `fromtable` FROM `cheque` WHERE `approve`='1' .....and goes on and on.

now the main issue is I have got What I wanted to make happen. but suddenly I have noticed a problem. from the sell table all the database entries are not being fetched. But when I run the exact same query on sell table it works perfectly. I just wanted to know what went wrong with union, I tried this code with union all though, but nothing seems to work for this case.

A recommendation I was just wondering if anyone can tell how this job is done. how to bring all the tables together and show them in a single table by query. I just to know the procedure.

enter image description here

Result with union query enter image description here

image without union

As I have mentioned with the red mark on the picture that with union query i get 12 rows where it should come 16 rows.

Atik Hashmee
  • 393
  • 3
  • 12

1 Answers1

1

The column names in the result-set are usually equal to the column names in the first SELECT statement in the UNION.

The UNION operator selects only distinct values by default. To allow duplicate values, use UNION ALL

Check the following links

UNION ALL

https://www.w3schools.com/sql/trysql.asp?filename=trysql_select_union_all

UINON

https://www.w3schools.com/sql/trysql.asp?filename=trysql_select_union

Sathish kumar Ramalingam
  • 267
  • 4
  • 8