select in some range , but finishdate on table B

[sql]

I need to get rang of University head count

select  A.UniversityN, count(*)
from Post
and UpdateId like'U%'
group  BY UniversityN and ReceiveDate>='2010-01-01' and ApprovalDate<=''2010-02-02';

but the problem is the ApprovalDate on table B , and table Post and table Post is one to many relationship , ther connected By UserId .
any ideal??
________
Yamaha Dt250

[SDas]

sql, it would help if you gave some examples of what you want. If I followed what you have, you want a count for each distinct UNIVERSITYN values in table POST where their RECIEVEDATE between two values on table APPROVALDATE and the tables are related by USERID.

This might get you what you want or at least get you in the direction you need to go:

SELECT A.UNIVERSITYN, COUNT(*) AS CNT
FROM POST A
WHERE UPDATEID LIKE 'U%'
AND A.USERID EXISTS(SELECT *
                    FROM APPROVALDATE B
                    WHERE A.USERID = B.USERID
                      AND B.RECEIVEDATE BETWEEN '2010-01-01'
                                            AND '2010-02-01'
                   )
GROUP BY A.UNIVERSITYN

[sql]

Thank you fro the quickly reply!


"I want a count for each distinct UNIVERSITYN values in table POST table
and the data rang between ReceiveDate and ApprovalDate
and ReceiveDate on table Post , ApprovalDate on table PaymentRecords"

  i want SELECT A.UniversityN, COUNT(*) AS CNT
FROM Post as A
WHERE UpdateId LIKE 'U%'
and ReceiveDate>='2010-01-01' group with select Max(ApprovalDate),UserId from PaymentRecords where ApprovalDate !='NUll' and  ApprovalDate<='2010-02-02' group by UserId

for example on table Post I have
UserId 263,
UniversityN 121212121212
ReceiveDate='2010-01-01';
UserId 264
UniversityN 1234567890
UserId 475
ReceiveDate='2010-01-01';
UniversityN 121212121212
ReceiveDate='2010-01-01';
on table PaymentRecords
I have
PaymentId 1
UserId 263
ApproveDate='2010-01-07'
PaymentId 3
UserId 264
ApproveDate='2010-01-07'
PaymentId 7
UserId 263
ApproveDate='2010-01-10'
<<<<<<<<<<<<<<<<
and I try your suggestion

 SELECT A.UniversityN, COUNT(*) AS CNT
FROM Post as A
WHERE UpdateId LIKE 'U%'
and ReceiveDate>='2010-01-01'
AND A.UserId EXISTS(select Max(ApprovalDate)
                    FROM  PaymentRecords as B
                    WHERE A.UserId = B.UserId
                     and  ApprovalDate<='2010-02-02' group by UserId
                   )
GROUP BY A.UniversityN

I get error near Error code 1064, SQL state 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'EXISTS(select Max(ApprovalDate)
FROM PaymentRecords as B
' at line 5
________
CHEVROLET CORVETTE C5 SPECIFICATIONS

[SDas]

This correction should allow the original SQL to work now.

SELECT A.UNIVERSITYN, COUNT(*) AS CNT
FROM POST A
WHERE UPDATEID LIKE 'U%'
AND EXISTS(SELECT *
           FROM APPROVALDATE B
           WHERE A.USERID = B.USERID
              AND B.RECEIVEDATE BETWEEN '2010-01-01'
                                    AND '2010-02-01'
          )
GROUP BY A.UNIVERSITYN

However, I have never used or looked at MyQL so there is always the possibility some aspects of the SQL won't work.

I am still not sure if I follow what you want but this might do it:

SELECT A.UNIVERSITYN, COUNT(*) AS CNT
FROM POST A
WHERE UPDATEID LIKE 'U%'
  AND RECEIVEDATE BETWEEN '2010-01-01' 
                      AND '2010-02-01'
  AND EXISTS(SELECT *
             FROM APPROVALDATE B
             WHERE A.USERID = B.USERID
              AND B.APPROVDATE BETWEEN '2010-01-01'
                                   AND '2010-02-01'
            )
GROUP BY A.UNIVERSITYN

Only rows in the sub-query from table APPROVALDATE with rows that have an Approval date between your date range will be used to filter rows in the outer query on the table POST.

The outer query on table POST will only return rows with a Received date between your date range that also may a matching row in the suq-query result based on User id.

The rows returned will will be a distinct list of Universities and a Count for how many of each.

[sql]

Dear SDas,
Thank you for the reply.
the following code work , but if you found some hidden problem , please tell

SELECT A.UniversityN, COUNT(*)
FROM Post as A
WHERE UpdateId LIKE 'U%'
and ReceiveDate>='2010-01-01'
AND  ExISTS(select Max(ApprovalDate)
                    FROM  PaymentRecords as B
                    WHERE A.UserId = B.UserId
                    and ApprovalDate<>'Null'
                     and  ApprovalDate<='2010-02-02' group by UserId
                   )
GROUP BY A.UniversityN;

but I still have some point will like to discuss with you
1) the time range is between ReceiveDate and ApprovalDate. on the other word is begin on start date ending on ApprovalDate, why you suggest have an range between both receive and approve date
>AND RECEIVEDATE BETWEEN '2010-01-01'
> AND '2010-02-01'
2)my table post and PaymentRecords is one to many relations, so I have to use group by . and I want Max(ApprovalDate), but I just wonder since I use max in here , if I run
Select Max(ApprovalDate)
FROM PaymentRecords
where ApprovalDate<>'Null'
and ApprovalDate<='2010-01-01' group by UserId
why I did not get Null value row?
thank you
________
FORD VX54 PLATFORM SPECIFICATIONS

[SDas]

sql, I will deal with number 2 "Why is a Null value returned when MAX is used?" first.

MAX is an Aggregate function (along with MIN, AVG, COUNT, etc.). All Aggregate functions will always return at least 1 row. If the Where clause finds at least 1 row that matches all of its criteria, the Aggregate function value will be the value from the table (or a number when using Count). If the Where clause does not find any matching rows, the value returned will be NULL (or 0 if using Count).

So in your example:

SELECT Max(ApprovalDate)
FROM PaymentRecords
WHERE ApprovalDate<>'Null'
  AND ApprovalDate<='2010-01-01' group by UserId

If there are no rows in the PaymentRecords table with an ApprovalDate <= '2010-01-01', the Max(ApprovalDate) will be a NULL.

Because of that, the "AND ApprovalDate <> 'NULL' doesn't really do anything in your example.

As for number 1 "Why do I use a Between?", it is because I am not sure of your data and made a few assumptions.

A) Post table has a row for USERID. entered on some ReceivedDate.
B) Eventually that posted data gets a row added to the PaymentRecord table with an ApprovalDate.
C) The Approval could never happen before the ReceiveDate and the ReceiveDate could never be after the ApprovalDate.

Going on this assumption, If you just used ReceiveDate >= '2010-01-01' in the outer query and ApprovalDate <= '2010-02-02' in the sub-query, you would never find any matches with either an ReceiveDate after 2010-02-02 (becasue there couldn't be any ApprovalDate rows or any ApprovalDate rows before '2010-01-01' because they haven't been Received yet.

Using BETWEEN limits the table search to a smaller set of rows instead of the open-ended >= , <= but you now your data, tables, and process better than I do.

[sql]

busy for a few days , just have time come back here
my situation is
C) The Approval could never happen before the ReceiveDate and the ReceiveDate could never be after the ApprovalDate.
plus , at the moement I get ReceiveDate the records insert into payment table
with the relate UserId . do i still need to use between.
if my question is stupid , just ignore it !
________
Portable vaporizer