Help

[kaminari]

I have a sql statement
sql += "SELECT user_id,COUNT (*) "+
"FROM cit_main "+
"c WHERE c.user_id in "+
"(SELECT user_id "+
"FROM adm_users a "+
"WHERE a.supervisor in "+
"(SELECT b.user_id "+
"FROM adm_users b "+
"WHERE lower(b.login_userid) = '"+id+"')) "+
"and case_no is null and rvw_disposition='N' GROUP BY user_id";
I want this sql statement to cycle through the Id and the count and create a multidementional array.
What happens is I get the array with only the last row of information how do I not write over the previous results?

 

[rac2]

I am a bit confused by the idea of cycling through rows in a SQL statement. And by a multi-dimensional array?

A GROUP BY query produces a list summarizing categories of rows. What one might expect from your main SELECT statement would be a list of user_id's and the number of times each user_id occurred in the table cit_main.

Now that implies that many rows in cit_main will have the same user_id. Either that or the count is 1 so why do a query.

The WHERE a IN (x, y, z, ...) condition means to retrieve a row whenever the value of a matches one of the values in the list (x, y, z, ...).

So WHERE c.user_id in ( . . . ) would imply that you want some of the user_id's but not all of them.

The remainder of your query seems to say that you only want user's whose supervisor is logged in. Which may be why you are getting "only the last row of information". Oh, and SQL does not write over rows so maybe it is not the last row but it is the only row. Possibly the row for the supervisor who is logged in. A lot of that is speculation.

Could you give us an idea of the tables and columns you are working with and what is the question you want to answer.

 

[kaminari]

Sorry for the confusion I actally got the sql stmnt to work. Here is the sql I came up with.

SELECT unique user_id,COUNT (*) FROM cit_main
WHERE user_id in (SELECT user_id FROM adm_users a
WHERE a.supervisor in (SELECT b.user_id FROM adm_users b
WHERE lower(b.login_userid) = 'mturner')) group by user_id Order by user_id

I wanted to see how many times the user Ids in the id array came up it the cit_main table. this new sql statment does this, however, I have another problem now. there is one id 264 that doesn't show in the cit_main table, which means that there are no records for 264 in the the cit_main
looking at the results from the query that id # doesn't show. is there a way to make the Id show and a 0 instead of a null?