Update TABLE from a Related Table with Multiple Entries (only wanting the latest val)


The example below is not real, but if I can solve this, I know I can solve the bigger problem I have!

Using MYSQL, I have two tables. Farm and Animals. Animals can have several items connected to farm with the "farmid" serving as a related key.

FARM
id
farmname
latestanimal

ANIMALS
animalid
farmid
animalname
dateadded

What I need to do is run an update query, that automatically populates "farm.latestestanimal" with the "animalname" from the latest entry of Animals that is connected to the farm.

I know this sample below is wrong, but it might show what I am trying to do:
"UPDATE farm SET farm.latestanimal = animals.animalname WHERE animal.farmid = farm.id ORDER BY animal.dateadded DESC LIMIT 1"

Can someone point me in the right direction to making this work
Posted On: Monday 31st of December 2012 01:06:28 AM Total Views:  327
View Complete with Replies

RELATED TOPICS OF MYSQL PROGRAMMING LANGUAGE




log-update in my.ini

If i put log-update=update in my ini it works fine,but as soon as try to specify a different location i.e. log-update=/backups/update i cannot access mysql. I have a folder called backups in the data folder and one in the MySQL Server 5.0 folder (just incase), but it still will not work. Any advice would be greatly appreciated.
VIEWS ON THIS POST

76

Posted on:

Thursday 25th October 2012
View Replies!

MySql update multiple rows in single table

Hi Everyone, I have a Bids table and I need to set prices for products and create bids. So, my problem is if the user selects a 100 or 200 products and sets prices for bidding, is there any efficient way to update all of them at once Right now its updating one record at a time.
VIEWS ON THIS POST

101

Posted on:

Thursday 25th October 2012
View Replies!

I need to update a large amount of values in 2 columsn in 1 table

I have a table that I need to change all values for all rows in 2 columns (used for mapping products). Is there any SQL that will let me just say something like, update the 2 columns and insert these values in this order Example: these are the values: 2, 4, 6, 3, 4, 1, 3, 4 And those values would be inserted as the values for the first 8 rows Sorry, noob question probably Thank you
VIEWS ON THIS POST

104

Posted on:

Thursday 25th October 2012
View Replies!

insert...update in mysql?

I am trying to insertupdate into multiple tables how do i do that I have 3 tables e.g. table x table y table z. table x (x_id) table y( y_id, x_id) //x_id-foreign key referencing table x table z (z_id, y_id) //y_id-foreign key referencing table y
VIEWS ON THIS POST

190

Posted on:

Thursday 25th October 2012
View Replies!

How to update part of the entries?

, Say I have the following field in the database (file_path) with the following data c:\myfolder\something.doc c:\myfolder\somthing2.doc c:\myfolder\somthing3.rar c:\myfolder\asad.doc c:\myfolder\kit.avi c:\myfolder\ghourab.doc How Can I replace(update) all the data entries with d:\hisfolder\something.doc d:\hisfolder\somthing2.doc d:\hisfolder\somthing3.rar d:\hisfolder\asad.doc d:\hisfolder\kit.avi d:\hisfolder\ghourab.doc so in other words how can I update a substr of a column
VIEWS ON THIS POST

87

Posted on:

Thursday 25th October 2012
View Replies!

using select for update

Dear all, i am a new bee to postgres. select statment on one table in my application ,might select multiple records from database. if multiple are fetched, randomly any one of them is picked up.(this logic is implemented in program after select stmt execution) i want to update one of the columns of the selected rows after selection, for which i need to use select for update. now the problem is select for update will lock all the rows that are fetched. but i want lock only on the randomly selected row not all that are fetched as i am not using others. can i set lock on that specific row any help greatly appreciated.
VIEWS ON THIS POST

89

Posted on:

Thursday 25th October 2012
View Replies!

unable to update this ??!?!?!?!?

update post_tbl,items set post_tbl.END='2007-01-01 00:00:00' where items.ID=post_tbl.Items_ID and items.SOLD=0 and items.MemberID=85 can you please tell me whats wrong in this !!\t
VIEWS ON THIS POST

129

Posted on:

Thursday 25th October 2012
View Replies!

How to update all fields with a multiple of the field value?

Is there a MySQL command to replace all the values of a field with the field's original value that's had a mathematical formula applied to it Basically, I want to divide the field's original value in half, then add 40% more. The formula would be y=x/2+.2x where y is the new value and x is the original value. I know I could write a PHP script that would select each field's value, apply the formula, then update the new value, but it'd be cool if there's a MySQL built-in function to do this.
VIEWS ON THIS POST

89

Posted on:

Thursday 25th October 2012
View Replies!

Query cache question(s)

, Our mysql server had a hiccup today so I was looking around at some settings and reading information (online and in books). The one thing that came up is query caching which we have turned off. It seems, like with anything, there are advantages and disadvantages to using it(). In our environment we serve many sites, that are similar but different, each making queries to the db based on the specific site but those queries are the same, just different ids, etc. Based on what I read, I don't know if query caching would benefit (not sure I completely understand where it would to be honest). I am wondering what information you could sure to help me understand it better and if you think it might be a good fit for the usage of mysql in our environment I am assuming any changes would go in my.cnf What variables would I need to set What is the normal values to set those variables How do you know when they should be upped or downed Etc Thank you for your time and help, cranium
VIEWS ON THIS POST

143

Posted on:

Monday 29th October 2012
View Replies!

How to optimize this query

I'm struggling to find a way to speed up this query" Code: SELECT DISTINCT hd.hotel AS hotelID, r1.contractName, r1.availToken, r1.contractOffice, r1.supplierDest, r1.hotelCurr, r1.hotel, r1.boardName AS boardName1, r1.roomName AS roomName1, r1.price AS price1, r1.roomCode AS roomCode1, r1.roomTypeCode AS roomTypeCode1, r1.roomTypeChar AS roomTypeChar1, r1.boardCode AS boardCode1, r2.boardName AS boardName2, r2.roomName AS roomName2, r2.price AS price2, r2.roomCode AS roomCode2, r2.roomTypeCode AS roomTypeCode2, r2.roomTypeChar AS roomTypeChar2, r2.boardCode AS boardCode2, r3.boardName AS boardName3, r3.roomName AS roomName3, r3.price AS price3, r3.roomCode AS roomCode3, r3.roomTypeCode AS roomTypeCode3, r3.roomTypeChar AS roomTypeChar3, r3.boardCode AS boardCode3 FROM searchRoomOptions r1 JOIN hotelDetails hd ON hd.code=r1.hotel AND hd.source=2 JOIN searchRoomOptions r2 ON r2.hotel=r1.hotel AND r2.search=r1.search AND r2.adults=2 AND r2.children=0 JOIN searchRoomOptions r3 ON r3.hotel=r1.hotel AND r3.search=r1.search AND r3.adults=2 AND r3.children=0 WHERE r1.search=498853 AND r1.adults=2 AND r1.children=0 ORDER BY r1.hotel EXPLAIN returns this: Code: "id","select_type","table","type","possible_keys","key","key_len","ref","rows","Extra" 1,"SIMPLE","r1","index_merge","hotel,adults,children,search","search,adults,children","4,5,5","",94,"Using intersect(search,adults,children); Using where; Using temporary; Using filesort" 1,"SIMPLE","hd","ref","source,code","code","5","testdb.r1.hotel",2,"Using where" 1,"SIMPLE","r3","ref","hotel,adults,children,search","hotel","5","testdb.hd.code",7,"Using where" 1,"SIMPLE","r2","ref","hotel,adults,children,search","hotel","5","testdb.r3.hotel",7,"Using where" Table definition: Code: "Field","Type","Null","Key","Default","Extra" "id","int(10) unsigned","NO","PRI","","auto_increment" "search","int(10) unsigned","NO","MUL","","" "boardName","varchar(50)","YES","","","" "roomname","varchar(50)","YES","","","" "price","float","YES","","0","" "roomCode","varchar(40)","YES","","","" "roomTypeChar","varchar(20)","YES","","","" "roomTypeCode","varchar(20)","YES","","","" "boardCode","varchar(20)","YES","","","" "adults","int(10) unsigned","YES","MUL","0","" "children","int(10) unsigned","YES","MUL","0","" "minadults","int(10) unsigned","YES","","0","" "maxadults","int(10) unsigned","YES","","0","" "minchildren","int(10) unsigned","YES","","0","" "maxchildren","int(10) unsigned","YES","","0","" "occupancy","int(10) unsigned","YES","","0","" "childage1","int(10) unsigned","YES","","0","" "childage2","int(10) unsigned","YES","","0","" "childage3","int(10) unsigned","YES","","0","" "hotel","int(10) unsigned","YES","MUL","0","" "contractName","varchar(20)","YES","","","" "availToken","varchar(45)","YES","","","" "supplierDest","varchar(10)","YES","","","" "contractOffice","varchar(10)","YES","","","" "hotelCurr","varchar(10)","YES","","","" Any ideas
VIEWS ON THIS POST

81

Posted on:

Monday 29th October 2012
View Replies!

Databases with huge tables how to organize all the info

I wanted to ask a few questions regarding building databases that will have tables with lots of rows. How big should a table in mysql be For example, lets say we are saving videos where each row will represent a video and then that video will have some metadata in the different columns, something like this: id video name description image 01 /car1.flv cars video this video is.... 01.jpg 02 /bar2.flv bars video bars place on.... 01.jpg 03 /war3.flv war video the 1945 war.... 01.jpg 04 /rat4.flv rats video rats are big.... 01.jpg What is the recomended size of any given table where saving information like this when will it start to get slow if anything what is reccomended to do once it reaches a certain size build another table just like it and start from the beginning For speed reasons for example, would you make a table for every video category or would you put all the videos under the same table regardless of the category etc Any resources that speak about this things will be appreciatted. As you can see mainly what I want to know is how to better organize information for speed reasons.
VIEWS ON THIS POST

75

Posted on:

Monday 29th October 2012
View Replies!

How can we conver mysql 5.0.22 database into MySQL 4.1.20?

Is there any way to convert database developed in MySQL 5.0.22 into MySQL 4.1.20 RISHI
VIEWS ON THIS POST

122

Posted on:

Monday 29th October 2012
View Replies!

SELECT, LIMIT with codition

I have a table 'itemmaster'. I want (4 records at Random) records from the table which satisfies the condition " enumHomeFeatured='Y' " If count of these 'Home Page Featured' items is less than 4, then I want to include other records too which satisfies the condition " enumCategoryFeatured='Y' ", but priority should be of 'enumHomeFeatured'. I can do this with two queries and merging the records with PHP Script. But I am curious to know how is this possible with single query.
VIEWS ON THIS POST

64

Posted on:

Monday 29th October 2012
View Replies!

"most recent" problem in sql

I have a problem where I'm trying to build a table that shows me the most recent entry in a table as of a month for each person. Let me give the examples because I know that's not very clear. Table 1 id person date notes 1 5 jan15 blah 2 5 mar21 blah2 3 7 feb15 blah 4 7 apr15 blah 5 7 jun21 blah Table 2 - date table id name first_day last_day 1 Jan jan1 Jan31 2 Feb feb1 Feb28 3 Mar Mar1 Mar31 4 Apr Apr1 Apr30 I want this Month Person Most_Recent 1 5 Jan15 2 5 Jan15 2 7 Feb15 3 5 Mar21 3 7 Feb15 4 5 Mar21 4 7 Apr15 5 5 Mar21 5 7 Apr15 I'm just not sure how to get the most recent.. Max() only shows the results from the latest entry onwards. The date table is probably un-needed here, but I have another need for that, and it allows control over which months I need this for. Anyway.. Help on this one would be wonderful!! Cheers, whiteatom
VIEWS ON THIS POST

60

Posted on:

Monday 29th October 2012
View Replies!

Help with my sql query

hello i am fairly new to SQL and still a bit confused regarding some of its concepts. So, i would gratefully appricate some advice. i will be using a mySQL database. i have a member's database comprising several tables .i.e user table ( the table of users).languagelist ( the table containing the list of langauges and their respectives ids).the spoken_ languages table ( the link between the languagelist and the user table i.e the spoken_ languages table gives the id of the language spoken plus the Id of the member who speaks that language. i.e PHP Code: USEmembers; CREATETABLEspoken_languages( language_idMEDIUMINTUNSIGNEDNOTNULL, user_idMEDIUMINTUNSIGNEDNOTNULL, INDEXlanguage_id(language_id,user_id), INDEXuser_id(user_id,language_id) ); thelanguage_idcolumnistheIdofthelanguage(derivedfromthethelanguagelisttable theuser_idcolumnistheidofthememberdrawnfromtheuser table. i know how to write an SQL query to extract the list of all members who speak a particular language. i.e PHP Code: $lang\t\t=\t\tmysql_query("SELECTid\tFROMspoken_languagesWHERElanguage_id='1'ORDERBYidASC"); the above query , if i am correct, will give me all the Id of members who speak the language with a language_id of 1. MY CONFUSION HOWEVER IS THIS; how can i add to the same query, a search for a full list of all other languages spoken by the selected members. i.e each member has a profile page that contains information on each langauage spoken by them. i need to extract this list and add it to the query. i am unsure how to draft the sql query to extract a list of the langauges spoken by them. i.e i suspect that i will first have to; get the language Id of each language spoken by the selected member then run another query to the languagelist table to find out what the language is. i.e language Id 1 = English. please advise me on the correct procedure. i hope my question is clear. warm regards Andreea
VIEWS ON THIS POST

69

Posted on:

Monday 29th October 2012
View Replies!

Insert problem

Hi all. I am using this script to insert a record to a table. INSERT INTO `sh15r_virtuemart_category_categories` (id, category_parent_id, category_child_id, ordering) values ( (SELECT 30000, u1.category_parent_id AS category_parent_id, u2.category_child_id AS category_child_id, 0 as ordering FROM (SELECT virtuemart_category_id AS category_parent_id, 1 AS x FROM `sh15r_virtuemart_categories_de-DE` WHERE ERPCode = 10001)u1 INNER JOIN (SELECT virtuemart_category_id AS category_child_id, 1 AS x FROM `sh15r_virtuemart_categories_de-DE` WHERE ERPCode = 20111)u2 ON u2.x = u1.x) ) But always I get the error >[Error] Script lines: 1-15 ------------------------- Column count doesn't match value count at row 1  I can't understand why. When I am runing the select alone the return values are correct.
VIEWS ON THIS POST

89

Posted on:

Monday 29th October 2012
View Replies!

What does show_db_priv do?

I find that if I deny show_db_priv to a mysql user, that user can still execute the command: Code: show databases; What can that user not do if he is denied the show_db_priv
VIEWS ON THIS POST

88

Posted on:

Monday 29th October 2012
View Replies!

How do you declare a constant value in a select?

I know this is probably an easy one, but I am stumped. I have the need to declare a variable with a value that will always be returned from a select statement. Basically, I have two databases with the same table structure for storing user data. I want to select from both databases using a UNION statement but then need to be able to determine from which database my results came from. So I need something like this: PHP Code: (SELECTusernamedatabase_name='db1'FROMdb1.user) \t\tUNION (SELECTusernamedatabase_name='db2'FROMdb2.user) I want to be able to look at the results of this user defined "constant" called database_name in my results to determine which database my record came from I've searched around for how to declare a constant value in MySQl but cannot find any reference, or it is called something else in MySQL and I am just missing it.
VIEWS ON THIS POST

82

Posted on:

Monday 29th October 2012
View Replies!

Why won't this work? [RELATIONSHIP]

, I have a 3-way link table. The link table is called Externalp_LT which has 3 tables called Case, External_solicitor and External_solicitor_client connected to it. Now for some reason I can't get the data to the link table from the linked tables (case, external_solicitor, etc). I have included the code relating to the tables! I am using Oracle Express 10g Edition!! Code: CREATE TABLE "EXTERNALP_LT" ( "ID" NUMBER(4,0), "OSOLICITOR_ID" VARCHAR2(5), "OCLIENT_ID" VARCHAR2(5), "CASE_ID" VARCHAR2(4), PRIMARY KEY (OSOLICITOR_ID, OCLIENT_ID, CASE_ID), FOREIGN KEY (OSOLICITOR_ID) REFERENCES 'EXTERNAL_SOLICITOR'(OSOLICITOR_ID), FOREIGN KEY ("OCLIENT_ID") REFERENCES 'EXTERNAL_SOLICITOR_CLIENT'(OCLIENT_ID), FOREIGN KEY ("CASE_ID") REFERENCES "CASE" ("CASE_ID")ENABLE ); CREATE TABLE external_solicitor ( osolicitor_id VARCHAR(5), solicitor_surname VARCHAR(20), solicitor_forename VARCHAR(32), phone_number VARCHAR(20), Opposition_or_third_party VARCHAR(12), PRIMARY KEY ( osolicitor_id ) ); CREATE TABLE external_solicitor_client ( oclient_id VARCHAR(5), client_forename VARCHAR(20), client_surname VARCHAR(32), address VARCHAR(75), date_of_birth number(10), PRIMARY KEY ( oclient_id ) ); CREATE TABLE case ( case_id VARCHAR(4), case_Name VARCHAR(30), case_start_date number(12), case_Desc VARCHAR(50), contract_id VARCHAR(3), solicitor_id VARCHAR(4), PRIMARY KEY ( case_id ), FOREIGN KEY ( contract_id ) REFERENCES contract(contract_id), FOREIGN KEY ( solicitor_id ) REFERENCES solicitor(solicitor_id) );
VIEWS ON THIS POST

76

Posted on:

Monday 29th October 2012
View Replies!

Help: Not unique table/alias Error

Hi and girls, I'm getting the following error and I'm puzzled as to what is causing it. As far as I can see I'm aliasing the table names correctly, but why is it complaining about p2c PHP Code: 1066-Notuniquetable/alias:'p2c' PHP Code: selectcount(distinctp.products_id)astotalfromproducts_typeaspt,categoriesascleftjoinproducts_to_categoriesasp2conp2c.products_id=p.products_idleftjoinp2conp2c.categories_id=c.categories_idleftjoinproductsponp.products_type_id=pt.products_type_idleftjoinproducts_descriptionpdonpd.products_id=p.products_idleftjoinmanufacturersmonm.manufacturers_id=p.manufacturers_idleftjoinspecialsassons.products_id=p.products_idleftjoincategories_descriptionascdonc.categories_id=cd.categories_idleftjoincategories_descriptionascd2oncd2.categories_id=c.parent_idleftjoincategories_printer_typesasc2pttonc2ptt.categories_id=c.categories_idleftjoinprinter_typesaspttonptt.printer_types_id=c2ptt.printer_types_idleftjoinproducts_coloursaspconpc.products_id=p.products_idleftjoincoloursascloncl.colours_id=pc.colours_idwherep.products_status='1'andpd.language_id='1'and(concat(cd2.categories_name,'',cd.categories_name)like'%test%')
VIEWS ON THIS POST

75

Posted on:

Monday 29th October 2012
View Replies!

More than one query ?

hello My tables: I want to count most thanked users this query will do that "SELECT files_thanks.id, files.uploader, users.username, count(files_thanks.id ) ,users.id FROM files_thanks, files,users WHERE files_thanks.id = files.fid AND users.id = files.uploader GROUP BY uploader ORDER BY count(files_thanks.id ) DESC LIMIT 0 , 10 "; how can i select user color also!
VIEWS ON THIS POST

134

Posted on:

Monday 29th October 2012
View Replies!

Where is my database stored?

days old newbie. I downloaded MySQL 5.1 and I'm up and running. I created a database & some tables & I'm creating some queries & having fun. However, I decided to take a look at where my database is stored because I'm replacing my machine next week & I can't find it I've ran a search & looked thru the folders for MySQL in explorer and I can't find it. It opens OK when I run MySQL 5.1 but where is it hiding Thank you for your help.
VIEWS ON THIS POST

118

Posted on:

Monday 29th October 2012
View Replies!

No error, but will not update

I am trying to store a phone number. I encrpyt it using AES_ENCRYPT and store it. My code works fine with most, but for some reason it will not display anything for viewing when using AES_DECRYPT with a particular number. There is no error displayed from MySQL and the script displays fine if I store a different #. any ideas
VIEWS ON THIS POST

86

Posted on:

Monday 29th October 2012
View Replies!

Issue converting char to datetime

Hi . I am a bit new at converting data so bear with me. I need to convert a field from our database that is a char into a datetime format so I can compare if something was sent after that.I do it one way and it doesn't give me anything after 123108 probably because 123108 is bigger than 010109. Anyways any help you could provide would be appreciated. My query so far is attached. Many
VIEWS ON THIS POST

103

Posted on:

Wednesday 7th November 2012
View Replies!

Query assistance

Hi . I have a table with the following data for production entries. The time and production quantities for all jobs are put into the same table as 2 records with entry type p(for production qty ) and t ( time ) . like so docketno : entry_type :employee :entry_qty 8719 P john 50 8719 T john 100 I need to query the data such that both time and qty apear on one line in the ouput per job. docketno :employee :entry_qty(of P) :entry_qty(of T) like 1001 john 50 100 Can anyone suggest how to properly structure the query. Appreciated. DC
VIEWS ON THIS POST

54

Posted on:

Wednesday 7th November 2012
View Replies!

Using one temp table with another

This is probably easy but I'm stumped. I have a lengthy query in a sp that produces into a temporary table a list of rental orders for a particular product needed for a specified time window of usually a few weeks (eg 11/10/2003 to 2/11/2004) eg Order no date of order item no qty ordered start rent end rent 1223 11/2/2003 A-1001 10 11/10/2003 12/28/2003 1257 12/5/2003 A-1001 5 12/24/2003 12/30/2003 1288 1/8/2004 A-1001 3 1/21/2004 2/11/2004 etc. In the same sp, I want to build a table by day from the paramaterized start and end date of the window which holds the total quantity needed each day for each specific item, eg how many A-1001s are needed on each day date qty 11/10/2003 10 11/11/2003 10 11/12/2003 10 * * 12/24/2003 15 * 2/11/2003 3 Any suggestions apprecited!
VIEWS ON THIS POST

60

Posted on:

Wednesday 7th November 2012
View Replies!

I need to write a query in SQL

im completely new 2 this so it will probably seem piss easy to any1 reading this but heres the query i have to do thanx Consider the database schema on the relations (where the key attributes are followed by a *) COURSES(Number*, Faculty, CourseName) STUDENT(Number*, Surname, FirstName, Faculty) EXAMS(Student*, Course*, Grade, Date) Relation EXAMS stores the registration number(attribute Student) of students that passed the exam for a given course (attribute Course is the number of course, not the name of the course), the grade and the date of the exam. Write the query that finds the surnames of students and the exams they passed (number of course and corresponding grade) in SQL, Relational Algebra, and Relational Calculus.
VIEWS ON THIS POST

87

Posted on:

Wednesday 7th November 2012
View Replies!

need sql line

hello all i want some help here plz . i make asp script for books down load i want to make i want to get the largest 10 downloads plz tell me what the command i must using .
VIEWS ON THIS POST

81

Posted on:

Sunday 11th November 2012
View Replies!

ODBC Time out Expired

Originally posted by : V.Karthik Vaidyanathan (karthikvv@india.com)Hi ,I am experiencing a MS-SQL Server ODBC Time out expired problem, while running a query which runs for more than a minute. the query has only one output and is working fine when tried with Query Analyser. I would like to know, how i could rectify or solve this problem. Thanking You in advance.
VIEWS ON THIS POST

54

Posted on:

Sunday 11th November 2012
View Replies!

Need help with duplicate checking and message

, I'm new to T-SQL and need some help with creating a trigger or code of some sort which will search to find if an invoice # being entered already exist in the database and if it does, a message displays saying: "Cannot insert duplicate invoice number, please re-enter correct invoice number." but if none exist, the invoice # is entered.
VIEWS ON THIS POST

65

Posted on:

Monday 12th November 2012
View Replies!

Syntax Error Frustrations

Hi , This seems like such a newbie question but I'm really stumped on why this is happening. Here is my insert Insert into emails (subject,bodyhtml,cont,daysafter,mins1,mins2,mins3 ,mins4,mins5,mins6,maxs1,maxs2,maxs3,maxs4,maxs5,m axs6,ptmin,ptmax,lpmin,lpmax,aff,stype,products) values ('"&esub&"','"&bodyhtml&"',"&cont&","&da&","&srmin 1&","&srmin2&","&srmin3&","&srmin4&","&srmin5&","& srmin6&","&srmax1&","&srmax2&","&srmax3&","&srmax4 &","&srmax5&","&srmax6&","&ptmin&","&ptmax&","&lpm in&","&lpmax&","&aff&",'"&signtype&"','"&products& "'); Ok now I know will now say we need rest of code I will show you my validation below Code: 'Start by checking basic content is set StopPro = 0 submitted = request.form("submitted") If submitted = "true" then 'Set varables to catch our input Dim Esub : Esub = exitcheck(request.form("esubject")) Dim Econtent : Econtent = quotereplace(request.form("rte1")) if esub = "" or econtent = "" then StopPro = 1 Mess = "You must enter a subject and content" end if if StopPro 1 then Dim cont : cont = exitcheck(request.form("cont")) If cont "" Then cont = cint(cont) End If Dim ptmin : ptmin = exitcheck(request.form("ptmin")) If ptmin "" Then ptmin = cint(ptmin) Else ptmin = 0 End If Dim ptmax : ptmax = exitcheck(request.form("ptmax")) If ptmax "" Then ptmax = cint(ptmax) Else ptmax = 0 End If Dim lpmin : lpmin = exitcheck(request.form("lpmin")) If lpmin "" Then lpmin = cint(lpmin) Else lpmin = 0 End If Dim lpmax : lpmax = exitcheck(request.form("lpmax")) If lpmax "" Then lpmax = cint(lpmax) Else lpmax = 0 End If Dim aff : aff = exitcheck(request.form("aff")) If aff "" Then aff = cint(aff) Else aff = 0 End If Dim signtype : signtype = exitcheck(request.form("signtype")) Dim da : da = exitcheck(request.form("da")) If da "" Then da = cint(da) Else da = 0 End If Dim srmin1 : srmin1 = exitcheck(request.form("srmin1")) If srmin1 "" Then srmin1 = cint(srmin1) Else srmin1 = 0 End If Dim srmax1 : srmax1 = exitcheck(request.form("srmax1")) If srmax1 "" Then srmax1 = cint(srmax1) Else srmax1 = 0 End If Dim srmin2 : srmin2 = exitcheck(request.form("srmin2")) If srmin2 "" Then srmin2 = cint(srmin2) Else srmin2 = 0 End If Dim srmax2 : srmax2 = exitcheck(request.form("srmax2")) If srmax1 "" Then srmax1 = cint(srmax1) Else srmax1 = 0 End If Dim srmin3 : srmin3 = exitcheck(request.form("srmin3")) If srmin3 "" Then srmin3 = cint(srmin3) Else srmin3 = 0 End If Dim srmax3 : srmax3 = exitcheck(request.form("srmax3")) If srmax3 "" Then srmax3 = cint(srmax3) Else srmax3 = 0 End If Dim srmin4 : srmin4 = exitcheck(request.form("srmin4")) If srmin4 "" Then srmin4 = cint(srmin4) Else srmin4 = 0 End If Dim srmax4 : srmax4 = exitcheck(request.form("srmax4")) If srmax4 "" Then srmax4 = cint(srmax4) Else srmax4 = 0 End If Dim srmin5 : srmin5 = exitcheck(request.form("srmin5")) If srmin5 "" Then srmin5 = cint(srmin5) Else srmin5 = 0 End If Dim srmax5 : srmax5 = exitcheck(request.form("srmax5")) If srmax5 "" Then srmax5 = cint(srmax5) Else srmax5 = 0 End If Dim srmin6 : srmin6 = exitcheck(request.form("srmin6")) If srmin6 "" Then srmin6 = cint(srmin6) Else srmin6 = 0 End If Dim srmax6 : srmax6 = exitcheck(request.form("srmax6")) If srmax6 "" Then srmax6 = cint(srmax6) Else srmax6 = 0 End If Dim products : products = exitcheck(request.form("products")) if instr(products,",") >=1 then pfinder = split(products,",") end if if products = "" then products = "None" End If Now that's all the code that verifies my content and if you cant tell the field types in my db by looking at that code I advise you don't try to help as I need people with experience and the above validation makes it obvious which fields are text/memo and which are number or yes/no. If you require any other details let me know but I can promise all inputs match
VIEWS ON THIS POST

67

Posted on:

Monday 12th November 2012
View Replies!

Where Statement with mulitiple or statements

I am having problems with a where statement. I need to have the query bring back results if any of these cases are true. but it only seems to be bringing back instances of where the first statement is true. where ((t.enrolleddate is null and t.withdrawldate is null)or(t.enrolleddate =t.classdate) or (t.enrolleddate =t.classdate)) If i run each individual where clause it works ... but when i combine them it does not. can someone please help me
VIEWS ON THIS POST

79

Posted on:

Monday 12th November 2012
View Replies!

Dynamic SQL Question

I have a dynamic SQL statement that was created by a co-worker about a year ago. Unfortunately, he is no longer with the company and I can't figure out the syntax of how to sort a certain way. Can someone please put me in the right direction
VIEWS ON THIS POST

44

Posted on:

Monday 12th November 2012
View Replies!

BETWEEN date

. I am trying to have a SQL Statement retrieve all the objects between two dates. The data is in an Access database, and I'm using classic ASP for the development. I was trying the following statement: Code: strSQL = "SELECT * FROM Finances WHERE DateIncured BETWEEN " & CDate(Request.QueryString("date")) & " AND " & now Any ideas
VIEWS ON THIS POST

70

Posted on:

Monday 12th November 2012
View Replies!

Database design before starting

I would appreciate any help or suggesions on starting with database design for an asp.net site. I know this question maybe sounds silly but I feel stuck and don't know where to start. Yours sincerely Andla
VIEWS ON THIS POST

46

Posted on:

Monday 12th November 2012
View Replies!

(0x80040E14) Syntax error (missing operator) in query expression single quotes

Hi , I am recieving the Microsoft JET Database Engine (0x80040E14) Syntax error (missing operator) in query expression when updating data that contains single quote marks, for example: I'm going to.... It's happening...... It'll take place...... The Conn.Execute(strSQL) statement then fails as it appears that it can process the data before the quote marks (i.e. It), but can't process the data following the quote marks (i.e ll take place) Is there a simple solution to this problem
VIEWS ON THIS POST

80

Posted on:

Monday 19th November 2012
View Replies!

Trying to get an Group By clause to work

Originally posted by : Terry (rathbun@usa.net),Im running this Access query on my asp page:SELECT Dispatch.TechID, Units.RepairUnit,Dispatch.RepCompleteDate, [Units]![RepCompleteTime]*24-[Units]![RepStartTime]*24 AS IHPU, [Repair Unit].RBenchMark FROM Dispatch INNER JOIN ([Repair Unit] INNER JOIN Units ON ([Repair Unit].RepairUnit = Units.RepairUnit) AND ([Repair Unit].RepairUnit = Units.RepairUnit)) ON Dispatch.WorkOrder = Units.WorkOrder ORDER BY Dispatch.TechID, Units.RepairUnitIve tried to do a Group By clause but it bombs on the page, any suggestions on how i can get this work
VIEWS ON THIS POST

90

Posted on:

Tuesday 20th November 2012
View Replies!

Updating and saving a table

Code: SELECT a1.username, a1.lvl, COUNT(a2.lvl) rank FROM skillsoverall a1, skillsoverall a2 WHERE a1.lvl
VIEWS ON THIS POST

67

Posted on:

Tuesday 20th November 2012
View Replies!

Totalling higher of two columns by row.

Assume that I have a table containing two fields, Total and Total2. I need the sum of whichever is higher for each row, for example, with the following sample data: Code: Total | Total2 1 | 3 2 | 1 5 | 10 The query should return 15(3+2+10). Any good ideas on how to do this simply
VIEWS ON THIS POST

81

Posted on:

Tuesday 20th November 2012
View Replies!

Altering Column Names

I have table Employee. It contains the columns : name, phone, addr1 I would like to change the addr1 column to Address. How can I do this
VIEWS ON THIS POST

96

Posted on:

Tuesday 20th November 2012
View Replies!

7bit GSM to ASCII and visa versa

Originally posted by : Paul Distant (pdistant@linneydesign.com)I'm creating an application to allow users to upload a readable text file.What the application needs to do is to read from this file, and encoded it as packed 7-bit GSM.Eg: 43 b4 3c 3d 07 bytes = ChrisThe application also needs to do reverse.i.e. read 43 b4 3c 3d 07 bytes from the file and recognise it as 'Chris'.Does anyone know of a tool that can do thisCheers,Paul
VIEWS ON THIS POST

67

Posted on:

Tuesday 20th November 2012
View Replies!

Round off a Real Value without Truncation

I want to perform the following steps 1. Convert the real number into a character. 2. Convert the resulting character to a decimal. 3. Round the value at the N+1 th place. My requirement is When n=2 I want to round 18.005 to 18.01 or 18.0049 to 18.00(at 100th place) when i try with Declare @real real Set @real=18.005 print Round(Convert(Decimal(15,7),Convert(varchar(30),@r eal)),2) i can able to get the result until i get some indefinite real value like -4.8892562E-08. so when i try the same logic, i am getting a conversion error(Error converting data type varchar to numeric.). I noticed when i convert a real value of 19.05 to decimal(15,7) some of the digits are gets truncated. i am getting 19.0049991608 as result. Please help me to get the problem resolved.
VIEWS ON THIS POST

77

Posted on:

Tuesday 20th November 2012
View Replies!

What is wrong with this update statment

well i got an aspx page that supposed to update some data in sql server db and it generates this sql statment but it give error in writing to sql server 2000: 8NewcastleCkB7/8/1962 12:00:00 AMF1980Station Road46584WOInglewood070-4584582983 UPDATE PLAYERS SET NAME = 'NewcastleCk', INITIALS = B, BIRTH_DATE = '7/8/1962 12:00:00 AM' SEX = F, JOINED = '1980' STREET = Station Road, HOUSENO = '4', POSTCODE = 6584WO, TOWN = 'Inglewood' PHONENO = 070-458458, LEAGUENO ='2983' WHERE PLAYERNO = 8; An Error Occurred: System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near 'SEX'. at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at ASP.db_edit2_aspx.DBEditDataGrid_Update(Object Sender, DataGridCommandEventArgs E) I be happy if some one help me what is worong here.
VIEWS ON THIS POST

76

Posted on:

Tuesday 20th November 2012
View Replies!

SQL group by

Two tables: tableTournament, tablePlayerDetails In tableTournament are, among other fields, intPlayerID, intRanking and intMoney. For each tournament a player gets ranking points and money. In tablePlayers I've got strPfirstname and strPsurname. I want to create a ranking list that aggregates intRanking and intMoney and shows the playername. I've tested the following but it does not work: SQLrank = "SELECT tableTournament.intPlayerID,SUM(tableTournament.in tRanking), SUM(tableTournament.intMoney)," & _ "tablePlayerDetails.strPfirstname,tablePlayerDetai ls.strPsurname FROM tableTournament,tablePlayerdetails " & _ "GROUP BY tableTournament.intPlayerID HAVING tableTournament.intPlayerID=tablePlayerDetails.Pla yerID ORDER BY tableTournament.intRanking DESC" Error message: Microsoft JET Database Engine error '80040e21' You tried to execute a query that does not include the specified expression 'strPfirstname' as part of an aggregate function. Any ideas
VIEWS ON THIS POST

57

Posted on:

Tuesday 20th November 2012
View Replies!

normalisation problem

hi i really need you tech boffins help for normalising some tables for my a database for my taxi company that i have brought. i'm quite a technical inthusiast and hope to be able to create a oracle dataase in oracle .i have read on many websites that when designing a database it is important to have normalised tables. so can any one help me normalise my fields i have it in unormalised form. (below). i have looked at tutorials on websites and they don't seem relevant to my database and i would like to learn how to do this for my database. so could some one show me how to do first normal form and then second normal form and then third normal for please.i have already done UNF and i think i have done 1NF below. is this right so far here is unnormalised form. we have a number of offices located in the main cities of Scotland office number address street city postcode phoneno. Each office has four kinds of staff: a Manager, several taxi owners, administrators and a number of drivers. Staff can be located at more than one office manager first name last name address salary d.o.b An owner provides one or more taxis taxi owner first name last name address salary d.o.b administrator first name last name address salary d.o.b driver first name last name address salary d.o.b taxi registration no. model make colour milage M.O.T due date capacity taxis are not available for hire by the public hailing a taxi in the street but must be requested by first phoning the us to attend a given address. There are two kinds of clients-private and business The business provided by private clients is on an ad hoc basis. The details of private clients are collected on the first booking of a taxi Private customer First name last name telephone no. client no. business provided by business clients is more formal and involves agreeing a contract of work with the business.A contract stipulates the maximum number of jobs that Critter-Cabs will undertake for a fixed fee business customer max jobs fixed fee start date end date Description //whether it is a night job or a day job name address telephone no. fax contract no. When a job comes in the appropriate details are recorded for either a private client or a business client. If the job is requested by a business client then the contract number is also recorded jobs contract no. pickup date pick up time pick up address drop off address milage used charge made job completed / yes or no 1NF * = primary key ** = foriegn key// i was'nt sure what to put down for foriegn keys ,so i don't think they are all there ----------------- *office id street city postcode phoneno ---------------- *Staff_id Staff Type first name last name street city postcode salary d.o.b ------------------ *taxi_id registration no. model make colour milage M.O.T due date capacity -------------------- //private clients *client no. First name last name telephone no. ------------------- //business clients *contract no. max jobs fixed fee start date end date Description //whether it is a night job or a day job name street city postcode telephone fax ------------------------------ *job_id **contract no. pickup date pick up time pick up address drop off address milage used charge made job completed / yes or no
VIEWS ON THIS POST

74

Posted on:

Tuesday 20th November 2012
View Replies!

ADOxxx or SQLxxx objects?

Originally posted by : DevPro (mbsoftware@libero.it)I currently have a web site running on NT4 and linked to an Access database. If I want to upgrade to SQLServer I just need to update the connection string in one file.Now I want to rewrite my site with ASP.NET, but I have a big question. I know that I can use ADOConnection, ADOCommand, ADODataSetCommand etc. to use any OLEDB Provider to be able to change the connection string and pass from Access to SQLServer when I want.But if I want to take the maximum advantage of SQL Server I should use SQLConnection, SQLxxx ect, right This is quite difficult, because I still want to be able the Access database, so, do I need to write different code for ADOxxx and SQLxxx objects for Access and SQLServerA second question: put that I choose to use the ADOxxx objects for both Access and SQLServer, even if I'm not sure this is the best situation. How can I know if the connection string points to an Access or SQLserver database, in order to use a stored procedure instead of a simple queryI hope I've been clear enough in explaining myself...Thank you so much for the help you would like to offer.
VIEWS ON THIS POST

95

Posted on:

Monday 26th November 2012
View Replies!

SQL Questions

Originally posted by : Robert Megliorino (rmeglior@alleghenytechnologies.com): I'm trying to do two things:1) How can I have a text field with more than 255 characters I tried memo, but the ASP component returns an error when I select it. I also want the field searchable(if possiable).2) Also: I'm trying to select a time out of the DB. The field is a DATETIME field with a default value of NOW() I'm using:mydate = now()SELECT * FROM mytable WHERE tabledate = '"& mydate &"'.It never finds any records. I've tried it with the LIKE statement and everything. Any ideas
VIEWS ON THIS POST

84

Posted on:

Monday 26th November 2012
View Replies!

Trigger

. I need to create trigger that will run after select. I wrote this Code: DROP TRIGGER IF EXISTS `article_after_select`; DELIMITER $$ CREATE TRIGGER `article_after_select` AFTER SELECT ON `article` FOR EACH ROW BEGIN /*SOME ACTION HERE*/ END; $$ DELIMITER ; I need when article selected, update view count. But I can't create trigger for SELECT.
VIEWS ON THIS POST

63

Posted on:

Friday 28th December 2012
View Replies!

Display all derived fields in a query

I have a very big and complicated query, but the main idea of the query is: Code MySQL: SELECT t.field1, SUM(t.total) AS total FROM ( ( SELECT CASE WHEN field IN ( [list 1] ) THEN "ABC" WHEN field IN ( [list 2] ) THEN "DEF" ELSE "GHI" END AS field1, total as total FROM table1 LEFT JOIN table2 ... WHERE (bunch of where clauses) ) UNION ALL ( SELECT CASE WHEN field IN ( [list 1] ) THEN "ABC" WHEN field IN ( [list 2] ) THEN "DEF" ELSE "GHI" END AS field1, total as total FROM another_table LEFT JOIN yet_another_table ... WHERE (bunch of where clauses) ) ) AS t GROUP BY t.field1 ORDER BY FIELD(t.field1, "ABC", "DEF", "GHI") I'm UNION'ing the results from two queries, then grouping by field1 and summing up the total field for each field1. My question is - if one of the field1 values (say DEF) is not found, the query results do not include DEF. What I'd like is to always show each value of field1 and display 0 for the total if it wasn't found. How would I do this Current Output: ABC 1930 GHI 765 What I'd like to get: ABC 1930 DEF 0 GHI 765
VIEWS ON THIS POST

90

Posted on:

Sunday 30th December 2012
View Replies!