Click here to Skip to main content
15,904,348 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
SQL
SELECT userid, company, car_moto, packet, SUM(clear_protect) FROM companies EXCEPT SELECT userid, company, car_moto, packet, paid FROM orders where userid='$userid'


I want to select all the rows of the 2 tables that have the same values of the columns userid, company, car_moto, packet but
SQL
SUM(clear_protect)
and
SQL
paid
not equal. I thought
SQL
EXPECT
would work ... Any suggestion?
Posted
Updated 12-Sep-13 4:54am
v4
Comments
Love 2 code 12-Sep-13 8:08am    
Hi,
not exactly clear what you want to do. I guess you should use a 'Group By' clause And a 'HAVING' clause like 'HAVING SUM(clear_protect) != paid' .
gvprabu 12-Sep-13 9:05am    
hi,

Give some sample data... then Its help full to us for give you the right solution :-)
bmaglar 12-Sep-13 9:32am    
companies
id, company, car_moto, packet, clear_protect
1, hello, hi, good, 22
2, hello_2, hi_2, good_2, 25
3, hello, hi, good, 28
4 hello_3, hi, good_2, 26

orders
id2, company, car_moto, packet, paid
1, hello, hi, good, 24
2, hello_2, hi_3, good_2, 29
3, hello, hi, good, 50

22+28=50 (also expert in maths :-))

i want to select

id2 company car_moto packet paid
1 hello hi good 24

not

3 hello hi good 50

Try:
SQL
;with cte1 as (
SELECT userid, company, car_moto, packet, SUM(clear_protect) AS SumCP
FROM companies 
GROUP BY userid, company, car_moto, packet
)
SELECT userid, company, car_moto, packet 
FROM cte1 
INNER JOIN orders ON cte1.userid = orders.userid
                    AND cte1.company = orders.company
                    AND cte1.car_moto = orders.car_moto
                    AND cte1.packet = orders.packet
WHERE cte1.SumCP <> orders.paid
 
Share this answer
 
v2
Comments
bmaglar 12-Sep-13 9:55am    
thanks but ... ;with cte1 ... is something wrong?
Corporal Agarn 12-Sep-13 9:56am    
I forgot the group by.
bmaglar 12-Sep-13 10:46am    
how can i query this to mysql ? the sum should be SUM(clear_protect)
Corporal Agarn 12-Sep-13 10:48am    
Sorry my answer was for SQL Server and I am unfamiler with MYSQL
SQL
;
WITH CTE AS (
SELECT id2,company,car_moto,packet,SUM(paid) AS SumCP
FROM orders
GROUP BY id2,company,car_moto,packet ,paid
)
SELECT CTE.id2 AS Id,CTE.company,CTE.car_moto,CTE.packet,CTE.SumCP
FROM CTE
INNER JOIN orders
ON CTE.id2       = orders.id2
AND CTE.company  = orders.company
AND CTE.car_moto = orders.car_moto
AND CTE.packet   = orders.packet
WHERE CTE.SumCP  = (SELECT  MIN(SumCP) FROM CTE)
GROUP BY CTE.id2,CTE.company,CTE.car_moto,CTE.packet,CTE.SumCP
 
Share this answer
 
PHP
$userid=$_SESSION['userid'];
				$q="SELECT DISTINCT id, company, car_moto, packet, paid FROM orders WHERE userid = '$userid' GROUP BY company, car_moto, packet";
				mysql_query("set character set 'utf8'");
				$result = mysql_query($q) or die(mysql_error());
				while($row = mysql_fetch_array($result)){
				$q_2="SELECT DISTINCT company, car_moto, packet, SUM(clear_protect) FROM companies WHERE userid = '$userid' AND company='".$row['company']."' AND car_moto='".$row['car_moto']."' AND packet='".$row['packet']."' GROUP BY company, car_moto, packet";
				mysql_query("set character set 'utf8'");
				$result_2 = mysql_query($q_2) or die(mysql_error());
				while($row_2 = mysql_fetch_array($result_2)){
				$q_3="SELECT * FROM orders WHERE userid = '$userid' AND company='".$row_2['company']."' AND car_moto='".$row_2['car_moto']."' AND packet='".$row_2['packet']."' AND paid < '".$row_2['SUM(clear_protect)']."'";
				mysql_query("set character set 'utf8'");
				$result_3 = mysql_query($q_3) or die(mysql_error());
				if (mysql_num_rows($result_3) > 0) {
				while ($ev_3 = mysql_fetch_array($result_3)) {
echo "...";
				}
				}
				}
				}


it works fine
 
Share this answer
 
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900