SQL Queries

[Cap2010]

HI,

3 Tables

Table 1 Table 2 Table 3
memid id1 id2
name memid memid
paid status 'y' date features
amt


1) Web site - a user access the site, clicks on search,
and also clicks professional from an option.
Submits the query - it displays on the site the list of
professionals from the database.

Question is when the user/other user at the same time
makes the same search with same option -
professionals -

[red]the record shouldn't display the same set of
records which it displayed on first search made.
i.e. record extraction is random
(it can display first record on 1st query and on 2nd
same query can display the first record some where in
the middle or last)[/red]

2) There are 40000 records or more in table 1, where the
member who has made payments.

There will be member who has made double payments.

question is want to know how many members have made
double or excess payment. (Payment by
each member is only once)


[red]Want to know the SQL query for the above.
(Site is on ASP) [/red]

Thanks,


Lad

 

[tlbroadbent]

1) You can return the result set in random order by using the RAND function in MS SQL Server. Other databases should have an equivalent.

SELECT * FROM SourceTbl
ORDER BY RAND(datepart(ms,getdate())*1000*memid)

2) To find duplicate records or multiple records per member you could use the following.

Select MemID, Sum(amt) As TotAmt, count(amt) As PayCnt
From Members
GROUP BY MemID
Having Count(amt)>1

OR

Select MemID, Sum(amt) As TotAmt, count(amt) As PayCnt
From Members
GROUP BY MemID
Having Sum(amt)><single payment amt> Terry

http://members.home.net/tlbroadbent/prog.htm

The reason why worry kills more people than work is that more people worry than work. - Robert Frost