SQL Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL Query

Author

Topic

Cyberskull
Starting Member

6 Posts

Posted - 2010-07-27 : 19:16:51

Hi,

I have two tables here they are:

tblOne
ID,Name,DefaultBid, Active

tblOneStats
ID,One_ID,DateTime,TotalClicks,TotalSales

My first SQL Query gets all the active records in tblONe

SELECT ID,Name FROM tblOne WHERE Active=1

Now I run through the record set:

Do Until RecordSet EOF

Inside here i select all the proper stats for the ID we are on:

SELECT * FROM tblOneStats WHERE One_ID=RecorSet(ID) AND DateTime='2010-07-27"

RecordSet.MoveNext
Loop

My problem is I need to sort the tblOne when I first select it and sort by TotalClicks or TotalSales.

But I can't do that since it's in the tblOneStats, please help out if you know how I can accomplish this.

Thanks for your help.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-07-28 : 00:36:40

Can you give more detailed info on your requirement.

You can join the two queries and make it single select statement and sort on the columns you require and also you can carry out current process with that single recordset itself.

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-07-28 : 01:30:27

if you join both tables it will return all the matches. for example:


select 
  * 
     from tblOne as O
     join tblOneStats as OS
     on O.ID = OS.One_ID
     where
        O.Active = 1
    and OS.DateTime = '2010-07-27'

in this way you will get all the matches. And since tblOneStats hold all the information and tlbOne is more or less just a code table, you will get your desired results for that day (2010-07-27).

Devart
Posting Yak Master

102 Posts

Posted - 2010-07-28 : 03:32:35

Hello,

quote:
My problem is I need to sort the tblOne when I first select it and sort by TotalClicks or TotalSales.

For example:
SELECT
ID,
Name
FROM
tblOne
WHERE
Active=1
ORDER BY
(SELECT SUM(TotalClicks) FROM tblOneStats WHERE One_ID=tblOne.ID AND DateTime='2010-07-27')

Best Regards,

Devart Team

Cyberskull
Starting Member

6 Posts

Posted - 2010-07-28 : 12:35:31

Thanks for the help guys, What I ended up doing is :

SQL = "SELECT tblYCampaigns.ID, tblYCampaigns.Status,tblYCampaigns.StatusReason, tblYCampaigns.Name, " &_ "tblYCampaignsStats.DateTime,tblYCampaignsStats.Impressions,tblYCampaignsStats.CTR,tblYCampaignsStats.Clicks,tblYCampaignsStats.CPC,tblYCampaignsStats.Assists,tblYCampaignsStats.Conversions," &_
"tblYCampaignsStats.Cost,tblYCampaignsStats.MarketHealthSales,tblYCampaignsStats.MarketHealthSalesValue" &_
" FROM tblYCampaigns, tblYCampaignsStats" &_
" WHERE tblYCampaignsStats.YCampaigns_ID = tblYCampaigns.ID" &_
" AND tblYCampaignsStats.DateTime='" & Session("DateFrom") & "'" &_
" AND tblYCampaigns.Company_ID=" & Session("CompanyID") &_
" ORDER BY Impressions DESC"

Kristen
Test

22859 Posts

Posted - 2010-07-28 : 12:41:43

"" AND tblYCampaignsStats.DateTime='" & Session("DateFrom") & "'" &_
" AND tblYCampaigns.Company_ID=" & Session("CompanyID") &_"

You'll probably tell me that as they are Session objects the user could not tamper with them, but if it was me I would wrap those in a function that surrounded with single quote, and replace any embedded single quotes with a pair of them (to prevent SQL Injection)

Cyberskull
Starting Member

6 Posts

Posted - 2010-07-28 : 13:00:20

Hey Kristen,

Very good advice, Thank you. I'm usually very careful about making sure that all the SQL injection holes are covered. In this case I'm actually not properly replacing the DateFrom. I am however making sure that a valid date is passed to the session before it's assigned.

Thanks again for that catch :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources