access query needed

Question

I am looking for an access query, but a sql server 2008 could be sufficient as I can use a passthrough feature in access.

My data looks like this .

--------------------------------------------------------------
id   nameid   name        score         diff          include 
--------------------------------------------------------------
1     0001     SO          100           0                0
2     0001     SO          100           0                0
3     0001     SO          100           0                0
4     0001     SO          100           0                0
5     0001     SO          100           0                0
6     0001     SO          100           0                0

7     0002     MO          10            0                0
8     0002     MO          18            0                1
9     0002     MO          20            0                0
10    0002     MO          14            0                0
11    0002     MO          100           0                0
11    0002     MO          100           0                0

12    0003     MA          10            0                0
13    0003     MA          18            0                1
14    0003     MA          20            0                0
15    0003     MA          14            0                0
16    0003     MA          100           0                1
17    0003     MA          100           0                0

Now what i want is to go through each row and only select the rows where include = 1. THIS IS EASY however ,I don't want the entire row.. I want to select the "group". The group can be identified by the nameid (or name).

So for the above I want the following result:

--------------------------------------------------------------
id   nameid   name        score         diff          include 
--------------------------------------------------------------

7     0002     MO          10            0                0
8     0002     MO          18            0                1
9     0002     MO          20            0                0
10    0002     MO          14            0                0
11    0002     MO          100           0                0
11    0002     MO          100           0                0

12    0003     MA          10            0                0
13    0003     MA          18            0                1
14    0003     MA          20            0                0
15    0003     MA          14            0                0
16    0003     MA          100           0                1
17    0003     MA          100           0                0

You will be fine with a subquery in Access – BonyT Jun 28 '11 at 15:40 — BonyT, Jun 28 '11 at 15:40

score 2 · Accepted Answer · edited May 23 '17 at 11:48

2

Ask your table for row with include = 1.

Then join again with the table to have all the rows corresponding to the first query's nameid :

SELECT DISTINCT m.*
FROM myTable m
    INNER JOIN myTable m2
        ON m.nameid = m2.nameid
        AND m2.include = 1

A join query will work better than an 'in' query for big amount of datas. You still need an index on the field 'nameid', and on 'include' could not hurt too.

An equivalent is with 'WHERE EXISTS' :

SELECT m.*
FROM myTable m
WHERE EXISTS
   (
      SELECT * 
      FROM myTable m2
      WHERE m2.include = 1
      AND m2.nameid = m.nameid
   )

You could see the difference here :

Can an INNER JOIN offer better performance than EXISTS

And why you have to use a Where exists when you have a filter with a lot of IDs :

Difference between EXISTS and IN in SQL?

edited May 23 '17 at 11:48

Community

1
1

answered Jun 28 '11 at 15:34

Cyril Gandon

16,830
14
78
122

If multiple rows within the group have include set to 1, then your query will return the required rows multiple times. – BonyT Jun 28 '11 at 15:39
This is for SQL server I presume. I am going to try out the access queries first if you dont mind. I dont know why someone downvoted you, but I brought it back up. – masfenix Jun 28 '11 at 15:43
Add parenthesis for access ! I don't remember where, but is is possible that the query doesn't work because of that. – Cyril Gandon Jun 28 '11 at 15:43
Alright I'll try. It's still trying to run cularis' query. It may have halted and im probably going to need to end task it. Also, access complains that the join is not supported. So I dont think its a matter of parenthesis – masfenix Jun 28 '11 at 15:46
@masfenix : check again my answer for a no join solution – Cyril Gandon Jun 28 '11 at 15:49
haha the last statement.. super fast. do you mind explaining why? i dont have any indexes or primary keys for that matter since the table is built on the fly. – masfenix Jun 28 '11 at 15:51
Removed downvote since you added distinct - however - "A join query will work better than an 'in' query" is not true as they will both result in identical execution plans. If there is no key on the table then the exists will perform better - but masfenix - you can still create an index even if you're building the table on the fly - in fact I would advise you to do this if you're going to query it. – BonyT Jun 28 '11 at 16:02
1

@Scorpi0 sadly Access gives an error like "JOIN Operation not supported" when you try and do `ON m.nameid = m2.nameid AND m2.include = 1` I found this out answering [this question](http://stackoverflow.com/questions/6457712/access-get-distinct-count-of-rows-based-on-different-column-values/6459627#6459627) and had to go to inline queries instead – Conrad Frix Jun 28 '11 at 16:04
1

"They will both result in identical execution plans" is not something you can say for certain for every database engine. Performance-wise, Jet/ACE definitely favors JOINs over IN/EXISTS, particularly when it's NOT IN/NOT EXISTS. This indicates quite clearly that the execution plans are not identical. In this case, of course, Jet/ACE is irrelevant, as the data is in SQL Server. I don't know how SQL Server optimizes these. Obviously, you'd run it through the query analyzer and figure it out, I'd think. – David-W-Fenton Jun 28 '11 at 21:43

score 2 · Answer 2 · answered Jun 28 '11 at 15:55

I think this query identifies the nameid values you want included in your main query.

SELECT DISTINCT nameid 
FROM YourTable 
WHERE include = 1;

If that is true, incorporate it as a subquery and use an INNER JOIN with YourTable to return only those rows for which a nameid value is associated with include = 1 ... in any row of the table.

SELECT id, nameid, name, score, diff, include
FROM
    YourTable AS y
    INNER JOIN (
        SELECT DISTINCT nameid 
        FROM YourTable 
        WHERE include = 1
        ) AS q
    ON y.nameid = q.nameid;

The Access query designer will probably substitute square brackets plus a dot in place of the parentheses enclosing the subquery.

SELECT id, nameid, name, score, diff, include
FROM
    YourTable AS y
    INNER JOIN [
        SELECT DISTINCT nameid 
        FROM YourTable 
        WHERE include = 1
        ]. AS q
    ON y.nameid = q.nameid;

score 1 · Answer 3 · answered Jun 28 '11 at 15:32

1

You need a subquery - as follows:

 SELECT *
 FROM tablename 
 WHERE nameid IN
   (
      SELECT DISTINCT nameid 
      FROM tablename 
      WHERE include = 1
   )

answered Jun 28 '11 at 15:32

BonyT

10,750
5
31
52

score 0 · Answer 4 · answered Jun 28 '11 at 15:33

0

SELECT * FROM yourTable WHERE nameid IN (SELECT DISTINCT nameid FROM yourTable WHERE include=1)

What you do is, select every row, whose nameid is in your subquery. The subquery selects the nameid for rows where include=1.

answered Jun 28 '11 at 15:33

Jacob

41,721
6
79
81

1

This is fine, but it is very slow on access. I have about 24000 rows. – masfenix Jun 28 '11 at 15:42
You say that you have no indexes. Why not add the indexes? – David-W-Fenton Jun 28 '11 at 21:43

access query needed

4 Answers4

Linked