PARAMETERS/value in SQL, how to set?

  • Thread starter Thread starter Edwin Knoppert
  • Start date Start date
E

Edwin Knoppert

I would like to use access (for testing, then ASP.NET) to execute a query
which uses another query where a value must be set like ID = 2 or so.

How can i do that?

Here is my example, i created Table2 with an ID field and a name field
(totally useless but it's they idea what matters).
Query2 is a total:

SELECT Count(Table2.ID) As T
FROM Table2
WHERE ID = ?

Now i want QUERY1 to select it and fill ?
Select * FROM QUERY2 WHERE ? = 1

Of course the WHERE ? part will not work.
Please don't point me to parameters stuff in ado.net, i'm aware of those it
the matter of preparing selects IN selects.

Thanks,
 
I'm having a little trouble following along ...

Lets say Table2 has the following:

ID Value
--- ------
1 A
1 B
2 C
3 D

Are you asking for:

SELECT * FROM
(SELECT ID, COUNT(ID) AS IDCOUNT FROM Table2
WHERE ID = 1 GROUP BY ID)
WHERE IDCOUNT = 1

In which case the result is 0 rows, because for ID 1, the IDCOUNT is 2

Or, are you looking for:

SELECT * FROM
(SELECT ID, COUNT(ID) AS IDCOUNT FROM Table2 GROUP BY ID)
WHERE IDCOUNT = 1

Which returns 2 rows:
ID IDCOUNT
--- -------
2 1
3 1

Or are you looking for:
SELECT A.* FROM Table2 AS A
INNER JOIN (SELECT ID, COUNT(ID) AS IDCOUNT FROM Table2 GROUP BY ID) AS B
ON A.ID = B.ID
WHERE B.IDCOUNT = 1

Which returns 2 rows:
ID Value
--- ------
2 C
3 D

Robert
 
Back
Top