Select from two tables giving more rows than expec

Question

I am not sure why this behaves this way. I need to select few values from two tables based on some criteria which should be clear from the query i tried below.

query = @"SELECT n.borrower, a.sum, n.lender FROM Notification AS n, Acknowledgment AS a 
          WHERE n.deleted=@del2 AND n.id IN (SELECT parent_id FROM Acknowledgment
                                             WHERE status=@status AND deleted=@del1)";

This returns more rows (12) than expected.

I have two tables Notification and Acknowledgment both which have field "sum". When I try the query below it gives the correct 3 rows as expected.

@"SELECT n.borrower, n.sum, n.lender FROM Notification AS n 
  WHERE n.deleted=@del2 AND n.id IN (SELECT parent_id FROM Acknowledgment
                                     WHERE status=@status AND deleted=@del1)";

Now I need to extend this query so that I need a.sum and not n.sum. But when I try the first query, it gives a lot more rows, I mean the WHERE condition doesn't work. I dunno if its a quirk with MS Access or something wrong with query. I appreciate an alternate implementation in access if my query seems fine 'cos it simply doesn't work! :)

I have read here that different databases implement select in different ways. Dunno if its something specific with access..

After suggestion from Li0liQ, I tried this:

@"SELECT n.borrower, a.sum, n.lender FROM Notification AS n 
  INNER JOIN Acknowledgment AS a ON a.parent_id = n.id AND a.status=@status AND a.deleted=@deleted1 
  WHERE n.deleted=@deleted2"

But I now get a "JOIN expression not supported" error.

Answer 1

In first query you seem to be trying to perform a JOIN.
However you end up performing CROSS JOIN, i.e. you query for all possible combinations from both tables (I bet you have 4 rows in the Acknowledgment table).

I hope the following query could do the trick or at least help you think in the right direction:

SELECT
    n.borrower, a.sum, n.lender
FROM
    Notification AS n
INNER JOIN
    Acknowledgment AS a
ON
    a.parent_id = n.id
WHERE
    n.deleted=@del2 AND a.status=@status AND a.deleted=@del1

Answer 2

This is expected behavior because of the cartesian product:

FROM Notification AS n, Acknowledgment AS a

If you have 10 notifications and 5 acknowledgements, you'll get 50 rows in the result, representing all possible combinations of a notification and an acknowledgement. That set is then filtered by the WHERE clause. (That's standard for SQL, not specific to MS Access.)

It sounds like you need a JOIN:

FROM Notification AS n INNER JOIN Acknowledgement AS a ON n.id = a.parent_id

You can then get rid of the subquery:

WHERE n.deleted=@del2 AND a.status=@status AND a.deleted=@del1

EDIT

As requested by nawfal, here is the solution he arrived at, which essentially incorporates the above recommendations:

string query = @"SELECT n.borrower, a.sum, n.lender FROM Notification AS n   
           INNER JOIN Acknowledgment AS a ON a.parent_id=n.id   
           WHERE a.status=@status AND a.deleted=@deleted1 AND n.deleted=@deleted2"; 

Answer 3

Something seems to be wrong with Access, that I could get this working only by reconstructing the query provided by the answerers here this way:

string query = SELECT n.borrower, a.sum, n.lender FROM Notification AS n 
               INNER JOIN Acknowledgment AS a ON a.parent_id=n.id 
               WHERE a.status=@status AND a.deleted=@deleted1 AND n.deleted=@deleted2;