selecting values from a Table based on the value of certain fields in the table.

[ajoo]

Hi All ,

 

I have a small table with 4 fields namely Day_ID, Dues, Last_Visit, Points. where Day_ID is an auto-increment field. The table would be as follows:

 

Day_ID -- Dues ---  Last_Visit ---  Points.

1  --------- 900 -------- 1/12  --------   6     

2  --------- 700 -------- 4/12  --------   7

3  --------- 600 -------- 7/12  --------   5

4  --------- 600 -------- 9/12  --------   6

5  --------- 600 -------- 10/12 -------   6

6  --------- 600 -------- 14/12 -------   6

 

So this is the record of a person's visit to say a club. The last row indicates the last date of his visit to the club. His points on this date are 6. Based on this point value of 6 in the last row I want to retrieve all the previous BUT adjoining all records that have the same Points i.e. 6. 

 

So my query should retrieve for me, based on the column value of Points of the last row (i.e. Day_ID - 6 ), as follows:

 

4  --------- 600 -------- 9/12  --------   6

5  --------- 600 -------- 10/12 -------   6

6  --------- 600 -------- 14/12 -------   6

 

This problem stated above had been completely resolved, thanks to a lot of help from Guru Barand by this following query :-

$query = "SELECT cv.day_id, cv.dues, cv.last_visit, cv.points

FROM clubvisit cv

WHERE last_visit >=

(

SELECT MAX(last_visit) FROM clubvisit

WHERE points <>

(

SELECT points as lastpoints

FROM clubvisit

JOIN

(

SELECT MAX(last_visit) as last_visit

FROM clubvisit

) as latest USING (last_visit)

)

)";

I am using this and it works perfectly except that now there is a slight change in the table because the criteria for points is now dependent on more than one column cv.points and is more like cv.points1, cv.points2, cv.points3 etc. So now I need to make a selection based on each of these cv.points columns.

 

As of now I can still get the results by running the query multiple times for each of the cv.points columns ( seperately for cv.points1, cv.points2, cv.points3) and it works correctly. However I am wondering if there is a better way to do this in just one go. This not only makes the code repetitive but also since the queries are interconnected, involves the use of transactions which I wish to avoid if possible. 

 

The values that I require for each of the cv.point columns is 

1. day_id of the previous / old day on which the cv.points value changed from the current day value, and 

2. cv.points on that old/ previous day. 

 

So for  example if the table is as below:

 

Day_ID -- Dues ---  Last_Visit ---  Points1 --- Points2.

1  --------- 900 -------- 1/12  -----------   9  ------------ 5   

2  --------- 600 -------- 4/12  -----------   6  ------------ 6

3  --------- 400 -------- 7/12  -----------   4  ------------ 7

4  --------- 500 -------- 9/12  -----------   5  ------------ 8

5  --------- 600 -------- 10/12 ----------   6  ------------ 8

6  --------- 600 -------- 11/12 ----------   6  ------------ 8

7  --------- 600 -------- 13/12 ----------   6  ------------ 7

8  --------- 500 -------- 15/12 ----------   5  ------------ 7

9  --------- 500 -------- 19/12 ----------   5  ------------ 7

 

Then I need the following set of values :

1. day_id1 -- Day 7,      points1 ---- 6,          days_diff1 -- (9-7 = 2)  . // Difference between the latest day and day_id1

2. day_id2 -- Day 6,      points2 ---- 8,          days_diff2  -- (9-6 = 3)

3. day_id3 -- ....

 

and so on for other points. 

 

Thanks all !