Help me optimize this query please


I just need it to return 1 record (the "next" client to call) but it doesn't return the correct one if I use TOP x.

I'm worried about the joins with SELECTs inside, I think that might be slowing it down.

It's running on MSSQL Server 2000. Clients has about 150k records, Calls has about 450k.
Code: SELECT C.Id AS ClientId FROM Clients C INNER JOIN ClientDatasets CD ON CD.ClientId=C.Id INNER JOIN CampaignDatasets CaD ON CaD.DatasetId=CD.DatasetId AND CaD.CampaignId=16 LEFT OUTER JOIN ( SELECT Id, ClientId, COUNT(Id) AS CallCount FROM Calls WHERE CampaignId=16 AND StatusId IN(6,7,8) GROUP BY Id, ClientId ) DT ON DT.ClientId=C.Id LEFT OUTER JOIN ( SELECT Id, ClientId, COUNT(Id) AS NoCallCount FROM Calls WHERE CampaignId=16 AND StatusId IN(2,3) GROUP BY Id, ClientId ) NoDT ON NoDT.ClientId=C.Id WHERE C.StatusId NOT IN(2,3,4,5) AND (DT.CallCount < 3 OR DT.CallCount IS NULL) AND (NoDT.NoCallCount=0 OR NoDT.NoCallCount IS NULL) ORDER BY DT.CallCount ASC
Posted On: Thursday 25th of October 2012 11:13:59 PM Total Views:  171
View Complete with Replies

RELATED TOPICS OF MYSQL PROGRAMMING LANGUAGE




mysql query help required

In one table there are some records for a field: option=com_content&id=1&task=view option=com_content&id=10&task=view option=com_content&id=91&task=view and so on. How to write a query to get the records only when id=1
VIEWS ON THIS POST

379

Posted on:

Thursday 25th October 2012
View Replies!

MS Access Querie or VBA Code help

I have an access database table which includes the following 2 fields (vendorID and Frequency) the Frequency column contains the following records (weekly, monthly, quarterly, half yearly and yearly) what i want to do is display the vendor records in a query which are due based on there frequency critea matching the current date. for example weekly in frequency will get displayed every week monthly in frequency will get displayed every month quarterly in frequency will get displayed every 4 months i want this because i have a list of reports that get sent to me based on the frequency criteria, and it would help if i can have a querie that displays the reports i'm suppose to recieve and if not i can chase up on them. i've heard that dateDiff() could be used for this
VIEWS ON THIS POST

152

Posted on:

Thursday 25th October 2012
View Replies!

Creating DB help

Hi , I need some help for my databases. My know knowledge with SQL is not big. I want to make a database for job candidates. I've made the html form and now im working on the db. So i created a db and i need to creat one table but im not sure now to do it right. The columbs are: 0. ID 1.Name 2.Surname 3.Family Name 4. Personal ID 5. Sex 6. Job Type 7. Email 8. Phone primary key ( ID, Personal ID ). Also the ID and PerID should be unique. I've made a simple sql code but it's not right. pls have a look and tell what to do. The second thing is how the users will fill those tables with info. Code: CREATE TABLE users ( userid int unsigned not null auto_increment, name int char(20) not null, surname int char(20) not null, familyname int char(20) not null, personalid int unsigned char (10) not null, sex int char(10) not null, jobtype int char (20) not null, email \t \t \t, phone \t \t \t, );
VIEWS ON THIS POST

158

Posted on:

Thursday 25th October 2012
View Replies!

Need help writing a query to join multiple fields

I am trying to write a query for a single table where each record contains five part number fields. I've never done this type of query before and could use some help. Here is the format of the table: Code: Table Name: MyTable Fields: widget_1_partnum widget_2_partnum widget_3_partnum widget_4_partnum widget_5_partnum widget_1_description widget_2_description ... I want a query that will merge and return all distinct widget part numbers in a single result set field called partnum, independent of whether the part numbers are related to widget_a, widget_b, etc. Basically I want to lump all the widget part numbers (from all five fields) into a single group and then have it return distinct part numbers from that group in a single result variable that I can index through in PHP (let's call it "partnum"). Anyone know how to do this Here is the code I'm trying to generate with this query: Code: $result=mysql_query(""); $num_rows=mysql_num_rows($result); for ($i=0;$i
VIEWS ON THIS POST

163

Posted on:

Thursday 25th October 2012
View Replies!

Replace help?

Hi Guys, I've googled this but can't find the right answer. Say I want to replace all instances of the character & in my database for 1,000's of records. I thought it would be something like so: Code: update table set headline = replace('&','&'); Can anyone help
VIEWS ON THIS POST

251

Posted on:

Thursday 25th October 2012
View Replies!

REPLACE help in a query

Hi , I want to run a query in phpmyadmin: Code: SELECT story_id, site_id, area_covered FROM `cms_stories` WHERE area_covered = 'ascot'; Which then brings out all my data like so: But then I want to take all of these query results and replace each site_id column with 51 INSTEAD of 52. Is there a way I can do this in a query rather than editing manually
VIEWS ON THIS POST

154

Posted on:

Thursday 25th October 2012
View Replies!

SQL query... please help

I am trying to generate an sql query but am having problems getting the result in a single query. Could someone please help point me in the right direction. table is as follows: PHP Code: \tid\t\t\t\turl\t\t\t\t\t\t\t\t\t\t\t\t\t\ttimer\t\t\t\t\tvalue\t1\t\t\tlocalhost/index.php\t\t\t\t\t\t\tconnect\t\t\t\t\t1.0\t2\t\t\tlocalhost/index.php\t\t\t\t\t\t\tdownload\t\t\t\t1.3\t3\t\t\tlocalhost/test.php\t\t\t\t\t\t\tconnect\t\t\t\t\t0.9\t4\t\t\tlocalhost/test2.php\t\t\t\t\t\t\tconnect\t\t\t\t\t1.1\t5\t\t\tlocalhost/test2.php\t\t\t\t\t\t\tdownload\t\t\t\t1.1 What I'm trying to do is generate a list of urls that are the quickest to connect AND download. therefore the results should not include localhost/test.php (only connected). from the data above, the generated list should be something like 1) localhost/test2.php -> 2.2 2) localhost/index.php -> 2.3 so far I've got it working with two queries PHP Code: $query="SELECTCOUNT(url)ask,urlfromtableGROUPBYurl";$result=mysql_query($query);while($row=mysql_fetch_assoc($result)){\t\tif($row['k']==2)\t\t{\t\t\t\t$urls[]=$row['url'];\t\t}}$urls='\''.implode('\',\'',$urls).'\'';$query="SELECTurl,SUM(value)asmaxfromtableWHEREurlIN($urls)GROUPBYurlORDERBYmaxASCLIMIT10";$result=mysql_query($query);while($row=mysql_fetch_assoc($result)){\t\tshow($row);\t\t}functionshow($array){\t\techo'';\t\tprint_r($array);\t\techo'';\t\t} Is it possible to select the data with a single query\t
VIEWS ON THIS POST

147

Posted on:

Thursday 25th October 2012
View Replies!

complex query help

, I'm completely stumped. PHP Code: Table1locationid|location|lat|longTable2routeid|routename|deplocation|arrlocation So that's an abstract of my table structure. One table holds the locations (the airports), and another table (could be routes, reports), etc. locationdep and locationarr both reference back to table 1. Here's my problem... I need lat/long for both of them included into the query, so in the end: PHP Code: routeid|routename|deplocation|deplat|deplong|arrlocation|arrlat|arrlong I figure pulling them from MySQL at once beats doing 3 queries to pull one complete record - when there would be upwards of 20/30 records being pulled per page. I've tried UNION, JOIN, etc, but I can't really wrap my head around this. It's almost 2 am... need some sleep. I'm not even sure what this type of query is called, so it's tough to search for a solution.
VIEWS ON THIS POST

296

Posted on:

Thursday 25th October 2012
View Replies!

3 table join in a php function help?

Hi , I wrote this function a while ago and it works fine but now I want to make it better by moving the query out of the while loop because I think this is causing alot of mysql overhead. If you can give me some idea as to how I would do this or even just some suggestions that would be great. Also any ideas on how to better write the code in general is greatly appreciated. Well here is the code. Code PHP: function GetGalleries ($user_id) { //this gets the categories that belong to the current user $get_cats = "SELECT id, user_id, cat_name, cat_desc from cats where user_id = '$user_id'"; $cats_res = mysql_query($get_cats)or die(mysql_error()); $num_of_cats = mysql_num_rows($cats_res); while ( $category = mysql_fetch_array($cats_res) ) { $cats_id = $category['id']; $cats_name = stripslashes($category['cat_name']); //then this gets the category name and the first picture to use as the thumnail link on the page $get_cat = "SELECT cat_id, photo_id from cat_photo JOIN photos on cat_photo.photo_id = photos.id WHERE cat_id = ".$cats_id." ORDER by photos.date limit 1"; $cat_res = mysql_query($get_cat)or die(mysql_error()); $cat = mysql_fetch_array($cat_res); $pid = $cat['photo_id']; $cats[] = array( 'cat_id' => $cats_id, 'cat_name' => $cats_name, 'pid' => $pid); } //then it returns an array of the galleries return($cats); }
VIEWS ON THIS POST

178

Posted on:

Thursday 25th October 2012
View Replies!

help with a query

hello, I'm going crazy trying to figure this out: I have 2 tables named videos and channelsForeign key is Channel_ID where I store the id of the channel the video belongs toRelevant fields for table videos are: Video_ID, Video_PostedOn, Channel_IDRelevant fields for table channels are: Channel_ID, Channel_Name I need to get the 3 most recent videos for each Channel_ID currently present in the videos table, ordered by Channel_Name ASC. I would really appreciate it if someone could help me out with this. maybe r937
VIEWS ON THIS POST

261

Posted on:

Thursday 25th October 2012
View Replies!

Need help with complex query

i've got 2 tables TABLE 1 contain a list of color with field color_id, title, description TABLE 2 contain connection between different color ex: colorA | colorB 1 - - - - 2 1 - - - - 8 1 - - - - 9 2 - - - - 3 8 - - - - 6 8 - - - - 7 what I want is to be able to list all ColorB for a specific ColorA, but ordered by the most connection from all colorB ..... so if I list connection for colorA #1 it should give me 8 2 9 as 8 have 2 colorB, 2 have 1colorA and 9 have no colorB at all! Is there a way to do this in 1 query
VIEWS ON THIS POST

172

Posted on:

Thursday 25th October 2012
View Replies!

Error trying to import to mysql...help please

I exported a small database to my computer Desktop. A .sql file. I asked my new web host to help me import it my new web mysql. I uploaded it and they attempted it directly to mysql(no phpmyadmin) and they replied "the importing of the backuped file to your database gives errors, and overwrites existing settings" and then provided the foloowing information below. Can someone help me tell me what this means and explain what went wrong and how this can be remedied Thank you. Query: CREATE TABLE `accounts` ( `account_id` bigint(20) NOT NULL auto_increment, `user_name` varchar(50) NOT NULL, `first_name` varchar(100) NOT NULL default '', `middle_initials` varchar(10) NOT NULL default '', `last_name` varchar(100) NOT NULL default '', `password` varchar(100) NOT NULL, `security_question` varchar(250) NOT NULL default '', `security_question_answer` varchar(250) NOT NULL default '', `email` varchar(200) NOT NULL, `phone` varchar(100) NOT NULL default '', `mobile_phone_number` varchar(100) NOT NULL default '', `account_type_id` int(11) NOT NULL default '1', `address` varchar(255) NOT NULL default '', `address_second` varchar(255) NOT NULL default '', `state` varchar(150) NOT NULL default '', `city` varchar(150) NOT NULL default '', `zip_code` varchar(150) NOT NULL default '', `country_id` int(11) NOT NULL default '0', `country_name` varchar(100) NOT NULL default '', `status_id` int(11) NOT NULL default '1', `status_title` varchar(50) NOT NULL, `date_creation` date NOT NULL, `date_last_login` date NOT NULL, `user_agreement` varchar(20) NOT NULL, `last_ip_address` varchar(20) NOT NULL, `activation_code` varchar(100) NOT NULL default '', `date_last_payment` date default NULL, `terms` varchar(3) default 'yes', `login_count` int(11) NOT NULL default '0', PRIMARY KEY (`account_id`), UNIQUE KEY `ix_accounts_email` (`email`), UNIQUE KEY `ix_accounts_user_name` (`user_name`), KEY `ix_accounts_first_name` (`first_name`), KEY `ix_accounts_family_name` (`last_name`), KEY `ix_accounts_country_id` (`country_id`), KEY `ix_accounts_status_id` (`status_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=23 Error occured at:2007-10-01 19:26:42 Line no.:59 Error Code: 1050 - Table 'accounts' already exists
VIEWS ON THIS POST

444

Posted on:

Thursday 25th October 2012
View Replies!

TSQl help: Testing a stored proc with output params in SQL Server Managment Studio

I have an SP like this (edited for brevity): set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[TESTING_SP] @Username MediumText, @Password MediumText, @UserKey int OUTPUT, @RoleKey int OUTPUT, @UserGroupKey int OUTPUT, AS BEGIN SELECT @UserKey = UserKey FROM UserProfile WHERE Username = @UserName AND [Password] = @Password END I want to execute this sp in Managment Studio (MS) and see what is being returned but I'm getting this error: Msg 201, Level 16, State 4, Procedure TESTING_SP, Line 0 Procedure 'TESTING_SP' expects parameter '@UserKey', which was not supplied. How do I set up the output parameters and then select the values in MS for testing purposes
VIEWS ON THIS POST

254

Posted on:

Thursday 25th October 2012
View Replies!

Complex SELECT statement help - is it possible?

Ok..I have a many to many relationship and I want to look at one table, one row at a time, and print all values for each row. However, for each row, if there a relationship has been found in another table, I want to print that tables relationship data also....briefly: scan table1, 1 row at a time; if table2.valueid = table1.value { print table1.allvalues and table2.allvalues } else print table1.allvalues My question is this: Is this possible through solely using a SELECT statement
VIEWS ON THIS POST

130

Posted on:

Thursday 25th October 2012
View Replies!

Need help understanding error message

I'm not terribly experienced with databases. Can someone tell me what's wrong with this query I've included the error message below - I'm not sure what it's telling me or how I fix it. CREATE TABLE saClient( clientID INTEGER UNSIGNED NOT NULL , firstName VARCHAR NOT NULL AUTO_INCREMENT , middleName VARCHAR NULL , lastName VARCHAR NULL , address1 VARCHAR NULL , address2 VARCHAR NULL , city VARCHAR NULL , state VARCHAR NULL , zip VARCHAR NULL , birthday VARCHAR NULL , ssn VARCHAR NULL , fax VARCHAR NULL , email1 VARCHAR NULL , email2 VARCHAR NULL , phoneCell VARCHAR NULL , phoneHome VARCHAR NULL , phoneWork VARCHAR NULL , phoneOther VARCHAR NULL , emergContactName VARCHAR NULL , emergContactPhone1 VARCHAR NULL , emergContactPhone2 VARCHAR NULL , emergContactRelationship VARCHAR NULL , sex TINYINT UNSIGNED NULL , notes TEXT NULL , STATUS TINYINT UNSIGNED NULL , PRIMARY KEY ( clientID ) ) MySQL said: Documentation #1064 - 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 'NOT NULL AUTO_INCREMENT, middleName VARCHAR NULL, lastName VARCHAR NULL, ' at line 3
VIEWS ON THIS POST

132

Posted on:

Thursday 25th October 2012
View Replies!

Date Search Query help

I'm trying to filter my search results by date. The field name I'm running this for is final_date and is a DATE field. I want my results to show records that have sales_reps.final_date within the past 30 days. Query: Code: SELECT sales_reps.sr_id, sales_reps.name, sales_reps.job_number, sales_reps.status, sales_reps.stage, UNIX_TIMESTAMP(sales_reps.final_date) as final_date, UNIX_TIMESTAMP(sales_reps.date_to_shop) as date_to_shop, shop_orders.community, users.user_id, u.fname, u.lname, users.builder, users.division, ei.shop_result, ei.good_tape, sa.shopper_id, shop_orders.order_id, UNIX_TIMESTAMP(shop_orders.date) as order_date, pi.fname as pfname, pi.lname as plname , ei.exit_id FROM sales_reps LEFT JOIN shop_orders ON sales_reps.order_id=shop_orders.order_id LEFT JOIN users ON users.user_id=shop_orders.builder_id LEFT JOIN report_types ON report_types.type_id=sales_reps.report_type LEFT JOIN shop_assignments as sa ON sa.sr_id=sales_reps.sr_id LEFT JOIN exit_interviews as ei ON ei.shop_id=sa.shop_id LEFT JOIN users as u ON u.user_id=sa.shopper_id LEFT JOIN users as pi ON pi.user_id=sa.pi_id WHERE DATE_SUB(CURDATE(), INTERVAL 30 DAY)
VIEWS ON THIS POST

145

Posted on:

Thursday 25th October 2012
View Replies!

Need help with mysql.exe

Extreme newbie here. I'm going through Kevin Yank book about PHP and MySQL. I haven't gotten very far. I installed PHP and MySQL on my computer for practice purposes. I followed the instructions thus far to the letter. In chapter 2 it advises to open the file mysql.exe. When I do, it takes me to a command prompt for about 1 second, then beeps and shuts down the prompt. What the heck What am I doing wrong
VIEWS ON THIS POST

234

Posted on:

Thursday 25th October 2012
View Replies!

SQL Statement help please - ORDER BY not appearing to work

, I am currently experiencing a slight problem with one of my SQL statements I'm using. Basically the SQL statement selects a group of table rows that have identical values for images.sculpturesID, and they are selected by values within the URL (lngRecordNo). An example URL would be Code: slideshow.aspID=186 This would return all records that have a sculptures.ID of 186 My complete SQL statement is below:- Code: "SELECT sculptures.ID, sculptures.Name, sculptures.shortDescription, sculptures.ArtistID, sculptures.sculpturetypeID, artist.firstname, artist.surname, sculptures.NameURL, sculptures.Price, images.ImgURL, images.ID" & strCRLF & _ " FROM (sculptures" & strCRLF & _ " INNER JOIN artist ON artist.NameURL = sculptures.artistID)" & strCRLF & _ " INNER JOIN images ON images.sculpturesID = sculptures.ID" & strCRLF & _ " WHERE sculptures.ID=" & lngRecordNo' ORDER BY images.ID DESC From my SQL statement, I am looping through my recordset to display multiple records that have identical values within the images.sculpturesID field. However, currenty, the outputted records are not in any order, and I would like to change this. So as you can see in my SQL statement above, I added an ORDER BY- (images.ID is an Autonumber, by the way). An example of a group of related within my 'images' table would be:- ID | Img URL | SculpturesID | ---------------------------------------- 870 | z2261b.jpg | 186 | 871 | z2261c.jpg | 186 872 | z2261d.jpg | 186 873 | z2261e.jpg | 186 874 | z2261f.jpg | 186 875 | z2262a.jpg | 187 876 | z2262b.jpg | 187 ----------------------------------------- You can see from my table structure example, that there are two different SculpturesID groups; 186 and 187. Currently the images.ID fields for a particular SculpturesID group, aren't in order- they are appearing in a random order such as:- 872 871 873 870 instead of 870 871 872 873. So I added my ORDER BY clause in to try and get the images.ID's in order, however they are still outputted in the random order. Is the formatting of the SQL statement incorrect I am a relative newbie to SQL so please explain things gently Any ideas on how to fix this would be much appreciated Kind
VIEWS ON THIS POST

156

Posted on:

Thursday 25th October 2012
View Replies!

If condition help

SET @RecordCount = 0, @PageCount = 0, @PageNumber = 0, @PageSize = 0; SET @PageSize = 10; IF(1 > 1.0) THEN SET @PageCount := (@RecordCount / @PageSize) + 1; END IF; Is this correct
VIEWS ON THIS POST

103

Posted on:

Thursday 25th October 2012
View Replies!

Fed up with MySql, can someone help me run this ****ing query?

I come from MS SQL 2005 background and I cannot get this MySql query to run.. INSERT INTO [lookup] ([Type],[Key],[Value],Rank,Status,Created) VALUES (1,2,'a',1,1,CURRENT_DATE()); Why won't this run\t (I know type, key, value are MySql "keywords" BUT I use the column definitions []). In MS SQL 2005, this works fine perfectly. Also, how the ***** do I run multiple sql statements in MySql query browser Which ****** developer wrote it so you can only run one statement at a time It's these little annoying ****** that make me switch back to MS SQL. Also, how the ***** do you declare variables in MySQL WITHOUT using a ***** stored procedure Here's what I want to run IN MS SQL 2005: DECLARE @Id INT; SET @ID = 0; DECLARE @ResultDate Date; SET @ResultDate = GETDATE(); Begin IF @ID > 0 BEGIN INSERT INTO User (Name, Created) VALUES ('test', @ResultDate); SELECT @ID = @@IDENTITY; END ELSE BEGIN UPDATE User SET Name = @Name, Modified = @ResultDate WHERE ID = @ID; END SELECT @ID as ID, @ResultDate as ResultDate Try and Convert that into MS SQL (INLINE SQL...not a function, not a procedure, INLINE SQL). Is this possible in MySql If it isn't, I'm done with open source crapola. People keep talking how Microsoft sucks, but at the end of the day...Microsoft actually has products that get the job done.
VIEWS ON THIS POST

178

Posted on:

Thursday 25th October 2012
View Replies!