CODEWITHSUNDEEP
javamysqlsql
Sorin Grecu
Sorin Grecu

Reputation: 1034

compare values from two tables

I have 2 tables. First one holds the total values of some shopping lists and the second table holds the products in that list. When a shopping list is done the total value is added into the total table together with some informations like the list number(nrList which is some kind of list id) and the number of products on that list nrProducts while the products go into the listproducts table.Lets say there are 3 products tomato,oranges and apples.They will all share the same nrList which,as mentioned before,is something like the list id.

First table totals:

CREATE TABLE IF NOT EXISTS `totals` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`nrList` int(11) NOT NULL,
`nrProducts` int(11) DEFAULT NULL,
`total` double NOT NULL,
`data` date DEFAULT NULL,
`ora` time DEFAULT NULL,
`dataora` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`Operator` varchar(50) DEFAULT NULL,
`anulat` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
UNIQUE KEY `id` (`id`)
)

Second table listproducts: 

CREATE TABLE IF NOT EXISTS `listproducts` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`nrList` int(11) DEFAULT NULL,
`product` varchar(50) DEFAULT NULL,
`quantity` double DEFAULT NULL,
`price` double DEFAULT NULL,
`data` date DEFAULT NULL,
`operator` varchar(50) NOT NULL,
`anulat` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
)

Now,i have two things i want to do,they are very similar. Lets say i have a list with 3 products.In the totals table there will be a row with some info and with the total=10$ nrProducts=3 and nrList=1.In the listproducts table i will have 3 rows all having nrList=1 and each having price=3$,3$,4$. Now,i want the check the following : 1.That if the value of nrProducts=3 then i have products for that list in the other table. 2.Check if the total in the first table is equal to the sum of the products in the second table.(quantity*price SUM)

I've done some stuff but i don't know what to do next. I managed to get the number of products for each list from the second table by using this:

 SELECT nrList,operator,COUNT(*) as count FROM listproducts GROUP BY nrList

But i don't know how to compare if the values are equal without doing two queries.

For the second thing again, I know how to get the sum but i don't know how to compare them without doing two separate queries.

SELECT SUM(price*quantity) FROM `listproducts` WHERE nrList='10' and operator like '%x%'

I can also do something like what i've done in the other select,this is not the issue.

The issue is that i don't know how to do the things i want in a single select instead of doing two and comparing them.I'm doing this in java so i can compare but i'd like to know if and how i can do this in a single query.

Thanks and sorry for the long post.

Upvotes: 0

Views: 245

Answers (1)

MauriDev
MauriDev

Reputation: 445

You can try something like this:

SELECT totals.nrList,
       IF (totals.nrProducts = t.nrProductsActual, 'yes', 'no') AS matchNrProducts,
       IF (totals.total = t.totalActual, 'yes', 'no') AS matchTotal
  FROM totals INNER JOIN 
      (SELECT nrList,
              COUNT(*) AS nrProductsActual,
              SUM(quantity*price) AS totalActual 
         FROM listproducts 
        GROUP BY nrList) AS t ON totals.nrList = t.nrList

Upvotes: 1

Related Questions