Select from two tables giving more rows than expected

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.

phoog · Accepted Answer · 2012-01-12T20:31:00.100

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";

edited Jan 12 '12 at 20:31

answered Jan 12 '12 at 18:16

phoog

42,068
6
79
117

Just perfect. Something close to what I was thinking of! – nawfal Jan 12 '12 at 18:17
No the subquery is required. That condition has to be met! – nawfal Jan 12 '12 at 18:19
1

@nawfal but it should be possible to achieve the same result by putting those conditions into the `WHERE` clause, which would be more efficient than a sub query. – phoog Jan 12 '12 at 18:21
Ya, i didnt know a way to achieve that, I mean i am not an expert on Joins and hence went for the sub query approach.. – nawfal Jan 12 '12 at 18:23
When I run this query I get "JOIN" not supported error message from Access! But I remember having done a LEFT OUTER JOIN successfully before with access – nawfal Jan 12 '12 at 18:28
1

@nawfal Edit your question to add your new query text and the full text of the error message. It should be pretty easy to get the join working. – phoog Jan 12 '12 at 18:31
Yes and as I understand this isn't an Access issue, rather applicable to all databases? – nawfal Jan 12 '12 at 20:36
@nawfal of course, you will find implementation-specific differences in some aspects of SQL, such as expression syntax, but the basic behavior of cartesian product ("cross") joins, inner joins, and outer joins is well defined and consistent across implementations. – phoog Jan 12 '12 at 23:02

Li0liQ · Answer 2 · 2012-01-13T06:05:11.520

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

edited Jan 13 '12 at 06:05

answered Jan 12 '12 at 18:17

Li0liQ

11,158
35
52

This seem to be the perfect answer. Get back to you guys after some testing :) – nawfal Jan 12 '12 at 18:21
When I run this query I get "JOIN" not supported error message from Access! But I remember having done a LEFT OUTER JOIN successfully before with access – nawfal Jan 12 '12 at 18:27
@nawfal The join is not supported because you can't use `AND a.status=@status AND a.deleted=@del1` in the `JOIN` clause. Join expressions should relate columns from one side of the join to another side of the join. These expressions only use columns from a. They could be incorporated into the `WHERE` clause. – phoog Jan 12 '12 at 19:59
@phoog exactly!! I got your point now. My bad. Can you update your answer with my post (which finally works) so that I can mark it? Can help newbies like me in future to see the working code from the one marked as answer. Thanks for your efforts.. :) – nawfal Jan 12 '12 at 20:21

score 0 · Answer 3 · answered Jan 12 '12 at 19:36

0

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;

answered Jan 12 '12 at 19:36

nawfal

70,104
56
326
368

1

That's functionally equivalent to the query implied by my answer. Why do you think something's wrong with Access? – phoog Jan 12 '12 at 20:01
@phoog Yes functionally. I thought the query I updated in the question would work fine with MySQL (which I'm more used to) and not in Access. It was my gut feeling after having worked a bit with MySQL and Access. I never faced any issues in MySQL with JOIN statements. I can understand if I got the syntax of JOIN statement wrong, but here Access merely says that JOIN expression is not supported. I could make it work after reading more from internet – nawfal Jan 12 '12 at 20:09
Let me state another quirk from MS Access. If im passing the values to be updated via parameters and placeholders, I need to $%%^ pass it in the EXACT EXACT EXACT order the query reads those values.This becomes so huge a task when you have some complex queries 'cos u cant have all the time looking the query and figure out which query is first executed, the order etc. MySQL (I believe other powerful databases like DB2 etc too) doesnt have this problem. Another one, Access doesnt support a lot of those top notch keywords I can use in other databases. All these made me think its an Access thing – nawfal Jan 12 '12 at 20:11
for the above problem mentioned, pls see my this link.. http://stackoverflow.com/questions/7165661/is-order-of-parameters-for-database-command-object-really-important Also the link I posted in the question states that different databases implements complex selects slightly different.All this made me believe so :) – nawfal Jan 12 '12 at 20:18

Select from two tables giving more rows than expected

3 Answers3

Linked