CODEWITHSUNDEEP
c#mysqlsql
Nulltiton
Nulltiton

Reputation: 25

SQL query with subqueries

I try to get the count of record used in other tables but SQL return null

Here my code:

DBConnectionClass.Command.CommandText = 
    "SELECT COUNT(IMO) 
    FROM vessel 
    WHERE ('" + IMO + "' = 
    (SELECT IMO FROM ActOfExemption)) 
    OR ('" + IMO + "' = (SELECT IMO FROM Waybill))";
            
object SQL = DBConnectionClass.Command.ExecuteScalar();
byte Result = Convert.ToByte(SQL);

Same query but working and without subqueries:

DBConnectionClass.Command.CommandText = 
    "SELECT COUNT(IMO) 
    FROM ActOfExemption 
    WHERE IMO = '" + IMO + "'";

object SQL = DBConnectionClass.Command.ExecuteScalar();
byte Result = Convert.ToByte(SQL);

DBConnectionClass.Command.CommandText = 
    "SELECT COUNT(IMO) 
    FROM Waybill 
    WHERE IMO = '" + IMO + "'";
       
SQL = DBConnectionClass.Command.ExecuteScalar();
Result += Convert.ToByte(SQL);

How can I do this in one query with subqueries?

Upvotes: 0

Views: 194

Answers (2)

dougp
dougp

Reputation: 3087

Your question seems to be about the SQL, so I'll ignore the apparent problems with the C# code...

Assuming the value of the C# variable IMO is "abc", the SQL would look like this:

SELECT 
(
  SELECT COUNT(v.IMO)
  FROM vessel v
    INNER JOIN ActOfExemption a on a.IMO = v.IMO
  WHERE v.IMO = 'abc'
) +  
(
  SELECT COUNT(v.IMO)
  FROM vessel v
    INNER JOIN Waybill W on W.IMO = v.IMO
  WHERE v.IMO = 'abc'
)

But this may lead to counting the same vessel record twice.

Upvotes: 1

atp9
atp9

Reputation: 890

As suggested by @maccettura Consider to change the code as it is prone to SQL Injection. It seems like, you are trying to get IMO from vessel where IMO exists in ActOfExemption or in WAybill. If so, you can change your query from

DBConnectionClass.Command.CommandText = "SELECT COUNT(IMO) FROM vessel WHERE ('" + IMO + "' = (SELECT IMO FROM ActOfExemption)) OR ('" + IMO + "' = (SELECT IMO FROM Waybill))";

to

DBConnectionClass.Command.CommandText = "SELECT COUNT(IMO) FROM vessel WHERE ('" + IMO + "' IN (SELECT IMO FROM ActOfExemption)) OR ('" + IMO + "' IN (SELECT IMO FROM Waybill))";

Also this is not the effective way to do that. You can actually do a left join your vessel table with ActOfExemption and left join with Waybill and consider rows where IMO is NOT NULL on those two tables.

Upvotes: 0

Related Questions