a sql issue....any solutions?

If i want to select antim_id from following table using query
Q1...which has all the missile_id values returned by another query Q2
.
For eg. if Q2 returns 100,300,400
my Q1 should return 3000...How to do it.? Will using ALL will help?
DEFUSE_CAPABILITY
+--+--+
| antim_id | missile_id |
+--+--+
| 1000 | 100 |
| 1000 | 200 |
| 2000 | 100 |
| 2000 | 200 |
| 2000 | 300 |
| 3000 | 100 |
| 3000 | 200 |
| 3000 | 300 |
| 3000 | 400 |
| 3000 | 500 |
+--+--+
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/Programming...54.h
tml
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz
.com/eform.php?p=769680SELECT D.antim_id
FROM defuse_capability AS D
JOIN (SELECT missile_id FROM Q2) AS Q2
ON D.missile_id = Q2.missile_id
GROUP BY D.antim_id
HAVING COUNT(*)=
(SELECT COUNT(*)
FROM Q2)
David Portas
SQL Server MVP
--|||That's relational division:
http://www.examnotes.net/gurus/articles/113.asp
Jacco Schalkwijk
SQL Server MVP
"bishu" <UseLinkToEmail@.dbForumz.com> wrote in message
news:4_769680_998784ccb95151a62f2e182992
e143ac@.dbforumz.com...
> If i want to select antim_id from following table using query
> Q1...which has all the missile_id values returned by another query Q2
> .
> For eg. if Q2 returns 100,300,400
> my Q1 should return 3000...How to do it.? Will using ALL will help?
> DEFUSE_CAPABILITY
> +--+--+
> | antim_id | missile_id |
> +--+--+
> | 1000 | 100 |
> | 1000 | 200 |
> | 2000 | 100 |
> | 2000 | 200 |
> | 2000 | 300 |
> | 3000 | 100 |
> | 3000 | 200 |
> | 3000 | 300 |
> | 3000 | 400 |
> | 3000 | 500 |
> +--+--+
> --
> Posted using the http://www.dbforumz.com interface, at author's request
> Articles individually checked for conformance to usenet standards
> Topic URL:
> http://www.dbforumz.com/Programming...pict223454.html
> Visit Topic URL to contact author (reg. req'd). Report abuse:
> http://www.dbforumz.com/eform.php?p=769680|||"David Portas" wrote:
> SELECT D.antim_id
> FROM defuse_capability AS D
> JOIN (SELECT missile_id FROM Q2) AS Q2
> ON D.missile_id = Q2.missile_id
> GROUP BY D.antim_id
> HAVING COUNT(*)=
> (SELECT COUNT(*)
> FROM Q2)
> --
> David Portas
> SQL Server MVP
> --
Same problem i am simplifying...
David can you translate your sql query so that i can understand
better.
In the emp table if i want to select emp_id which has all the
account_id in accounts what is the select statement i need to write.
for eg : if account_id {100,300,400} then my output should be 3000
if account_id {100,200} - op is 1000,2000,3000
if account_id {100,300} - op is 2000,3000
table:emp
+--+--+
| empid_id | account |
+--+--+
| 1000 | 100 |
| 1000 | 200 |
| 2000 | 100 |
| 2000 | 200 |
| 2000 | 300 |
| 3000 | 100 |
| 3000 | 200 |
| 3000 | 300 |
| 3000 | 400 |
| 3000 | 500 |
+--+--+
table : account
+--+
| account_id |
+--+
| 100 |
| 300 |
| 400 |
+--+
can u just let me know the solution..
thanks a lot
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/Programming...54.h
tml
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz
.com/eform.php?p=769869|||In my original query just replace defuse_capability with Emp and Q2
with Account.
David Portas
SQL Server MVP
--