Reputation: 8016
access query needed
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
Upvotes: 1
Views: 104
Answers (4)
Reputation: 17058
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?
Upvotes: 2
Reputation: 97131
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;
Upvotes: 2
Reputation: 43299
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.
Upvotes: 0
Reputation: 10940
You need a subquery - as follows:
SELECT *
FROM tablename
WHERE nameid IN
(
SELECT DISTINCT nameid
FROM tablename
WHERE include = 1
)
Upvotes: 1