SELECT by non-primary key


I have a table
CREATE TABLE IF NOT EXISTS `reviews` (
`id` smallint unsigned NOT NULL auto_increment,
`from` varchar(50) NOT NULL,
`length` varchar(20) NOT NULL default '',
`date` date NOT NULL,
`providerid` smallint unsigned NOT NULL,
PRIMARY KEY (`id`)
);

I'm trying to make a query on it that would select a row depending on the providerid
select * from reviews where providerid = '39';
but nothing is returned, why is this

Posted On: Monday 31st of December 2012 01:44:21 AM Total Views:  351
View Complete with Replies

RELATED TOPICS OF MYSQL PROGRAMMING LANGUAGE




Using LIKE for search purposes

I'm using LIKE to search for business names in a directory. However, it's being a bit strict. For example, searching for "normans liquor" will not return "Norman's Liquor" because the search string does not include the apostrophe. Is there a way to loosen LIKE's strictness Or am I simply approaching this the wrong way
VIEWS ON THIS POST

90

Posted on:

Monday 29th October 2012
View Replies!

New to SQL language, Help modify syntax please

I am not sure if this is a good place for this thread. But I am trying to modify some SQL syntax. I have no knowledge of SQL language. The SQL language is being used within SAS (Statistical Analysis Software). There is a PROC (procedure) in SAS that uses SQL language in database development. My problem: The syntax below creates a database by considering all 'usable pairs' to be either 1) event vs event, or 2) event vs nonevent. I want to change this syntax so that a 3rd type of usable pair is included in the database, 3) nonevent vs nonevent. Is this possible Could anyone modify the syntax for me. If you need more information please let me know. The dataset evtset contains all events and teh variables idn_j, y_j, and x_j. The dataset obs contains all observations events and nonevents and variables idn, surv, and combdays. Now construct all usable pairs and create variable for concordance. PROC SQL creates dataset that including all usable pairs by a Cartesian join. Dataset concord includes a new variable concord that identifies if the pair is concordant or not. proc sql; create table allset as select idn_j, y_j, x_j, idn as idn_i, surv as y_i, combdays as x_i from evtset, obs where idn_jidn; quit; data concord; set allset; if (x_iy_j) or (x_i>x_j and y_iXj and Yi
VIEWS ON THIS POST

82

Posted on:

Monday 29th October 2012
View Replies!

Conditional Not Working in Script

I posted this question over at the MySQL forums as well, but I like this forum better. I have written an update script that is supposed to check individual revision numbers and apply the revisions to a database if they do not exist. The conditional statements are not working, however. I can't seem to figure out what I am doing wrong with it. Even a simple IF 1 = 1 THEN select * from revisions; END IF; statement fails on running. Here is the script: Code: SET @cur_rev_num = 0; SET @description = NULL; -- UPDATE 1 - (PLACE DESCRIPTION HERE) COPY AND PASTE BELOW CODE TO PRIOR TO EDITTING CURRENT REVISION SET @cur_rev_num := 1; -- REPLACE THIS VALUE WITH CURRENT UPDATE NUMBER SET @description := "REPLACE THIS WITH DESCRIPTION"; IF (SELECT COUNT(revision_number) FROM revisions WHERE revision_number = @cur_rev_num) = 0 THEN --PLACE UPDATE CODE HERE INSERT INTO users (username, password) values ('stu', 'catdog'); INSERT INTO revisions (revision_number, description, committed_on) VALUES(@cur_rev_num, @description, NOW()); END IF; The error I am getting when I run this script is: ERROR 1064 (42000): 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 'IF (SELECT COUNT(revision_number) FROM revisions WHERE revision_number = @cur_re' at line 1 Can anyone shed some light on why no conditional statement seems to be working currently
VIEWS ON THIS POST

54

Posted on:

Monday 29th October 2012
View Replies!

Get record from table1 which not exist in table2

hi to all, I have two table for example table a with one field a_id and second table b with three fields one is b_id,a_id,c_id Now i want to get all records of table a whose a_id and a constant c_id is not in table b i can get records of table a whose has a_id not in table b but i want to check the combination of a_id and c_id where c_id is any value for example constant For example table a has records a_id 1 2 3 4 table b has records b_id a_id c_id 1 1 10 2 3 10 i want to get records form a a_id 2 4 because these two records not exist in table b with c_id 10 get records from a where a.a_id not equal to b.a_id and b.c_id not equal to 10 regards.
VIEWS ON THIS POST

84

Posted on:

Monday 29th October 2012
View Replies!

Database design help for Photography website

1. gallery(gallery_id*,gallery_title,gallery_description,gallery_thumbnail_id) picture(picture_id*,picture_title,picture_description,picture_image_path) gallery_picture(gallery_id*,picture_id*) 2. format(format_id*,format) class(class_id*,class_name (e.g. 'Panoramics','Square','Portrait','Custom1'), class_description) format_class(class_id*,format_id*) * = (component of) PRIMARY KEY Now you can relate images and formats to price or price tier, either as additions or multiplications of that price. You might want some tables like this: image_price(image_id,price_id) format_price(format_id,price_id) , **Not to reply to my own post, but I wanted someone to know I made another comment.**
VIEWS ON THIS POST

381

Posted on:

Monday 29th October 2012
View Replies!

Update column of the same row

is it possible to update column of the same row ID Table Y -------------------------------------- id name score add total -------------------------------------- 01 michael 35 5 40 02 james 15 2 17 I want to to modify the score for id 01 and the column "total" automatically updates: BEGIN UPDATE ON Y SET NEW.id=OLD.id, score=NEW.score, add=OLD.add, total=NEW.score + OLD.add; END I can't figure out what's wrong with my statement.
VIEWS ON THIS POST

100

Posted on:

Monday 29th October 2012
View Replies!

Table join

Hi. I have 2 tables. Products : -CategoryID -ProductID -Productname -PPrice -Postage -Description Colours : -ColourID -ProductID -Colour My problem is that I know that my pc case comes in black, white and blue for example. However, when you click on a menu item eg: "cases" it passes a category ID through url. I get that on the next page and it shows all records for that category. I am trying to join the products and colour table so that it shows available colours for each product in a dropdown. My sql is as follows but doesn't work how I want it to. [MYSQL]SELECT * FROM products INNER JOIN colourchoice ON products.ProductID = colourchoice.ProductID WHERE CategoryID = colname[/MYSQL]
VIEWS ON THIS POST

126

Posted on:

Monday 29th October 2012
View Replies!

SQL count question

all I have two tables, movies and users. The movies table has the column movies.user_id. Now I want to query for the users who have the most uploaded movies (count the most occuring user_id's in the movies table). I'm guessing I need to do some sort of count with a group by function, but I can't wrap my head around how to form the query. Any ideas
VIEWS ON THIS POST

66

Posted on:

Monday 29th October 2012
View Replies!

Mysql not finding users

I just installed Apache2, PHP5, MySQL5 and phpMyAdmin on my computer so I can work on my site locally and I'm trying to set up a user account in MySQL so that it mimics my host and I don't have to change settings from my local installation and my hosts. It doesn't seem to be working. I added a new user via phpMyAdmin in the mysql.users table and used the password() function to encrypt the password, but when I execute scripts PHP tells me that access is denied for the user, as if there is no user.. any ideas why this might happen
VIEWS ON THIS POST

104

Posted on:

Monday 29th October 2012
View Replies!

Nested queries needed?

i have a simple 5 table design. The "main" table is a list of ITEMS. Each item has a many-many relationship with AREAS and CATEGORIES (link tables exist for these: items_areas and items_categories). Now i'm making a Select statement for search results on a website. The two values supplied for the search are: category_id AND area_id. The following code works ok except you only get the first category and area it finds. If an item gets selected i want to show the user ALL the categories and areas it belongs to, not just the ones they searched on. Code: SELECT DISTINCT items.item_id, items.title, areas.name, categories.name FROM items LEFT JOIN areas ON areas.area_id = items_areas.area_id LEFT JOIN items_areas ON items_areas.item_id = items.item_id LEFT JOIN categories ON categories.id = items_categories.category_id LEFT JOIN items_categories ON items_categories.item_id = items.item_id WHERE items_areas.area_id = 'foo' AND items_categories.category_id = 'bar' Is this easy and/or inefficent
VIEWS ON THIS POST

105

Posted on:

Monday 29th October 2012
View Replies!

Check the existence of a specific value

Is there any function in mysql that returns true or false depending on the existence of a specific value in a field from a table
VIEWS ON THIS POST

47

Posted on:

Monday 29th October 2012
View Replies!

Custom Datatype in MySQL

Is there any wat to create the Custom Datatype in MySQL To create the Customdata Type in Oracle, I am using the Following Syntax. CREATE OR REPLACE TYPE AS OBJECT ( , ); I wish to know what is the Equivalent in MySQL. Please Let me know ASAP
VIEWS ON THIS POST

44

Posted on:

Monday 29th October 2012
View Replies!

` vs '

I'm not sure what the difference between ` and ' are. From what I can summize by stumbling through my code ` specifies a column while ' specifies data. I've only run into a problem when using the DELETE function. So does this only apply to that function PHP Code: DELETEFROM`accountcodesusers`WHERE`userName`=`test` Results in: Quote: #1054 - Unknown column 'test' in 'where clause' It works fine if I run it like this: PHP Code: DELETEFROM`accountcodesusers`WHERE`userName`='test' Server I'm working in is 3.23.41. Could be that since I'm used to working in 4.x.
VIEWS ON THIS POST

55

Posted on:

Monday 29th October 2012
View Replies!

Do I need another JOIN ?

This doesn't seem to work: PHP Code: $sql="SELECTt.id,t.title,t.name,\tt.company,t.address,tc.country,t.postcode,t.telephone,t.email,\t tm.msg,ts.site,tt.best_time\t FROMtrialsASt\t LEFTJOINadc_countriesAStcON(tc.code=t.country) LEFTJOINtrials_sitesAStsON(ts.trial_id=t.id) LEFTJOINtrials_msgAStmON(tm.trial_id=t.id) LEFTJOINtrials_timeASttON(tt.trial_id=t.id)"; if($safe_vars['c']) { \t\t$sql.="ANDt.code='{$safe_vars['c']}'"; } The purpose of the $safe_vars variable is to allow the user to view only rows from 1 country. Every row will have a country-code but the actual country names are stored in a reference table. Am I supposed to leave off the " AND t.code =" bit and instead write it within the JOIN e.g. PHP Code: $sql="SELECTt.id,t.title,t.name,\tt.company,t.address,tc.country,t.postcode,t.telephone,t.email,\t tm.msg,ts.site,tt.best_time\t FROMtrialsASt\t LEFTJOINadc_countriesAStcON(tc.code=t.countryANDt.country='{$safe_vars['c']}') LEFTJOINtrials_sitesAStsON(ts.trial_id=t.id) LEFTJOINtrials_msgAStmON(tm.trial_id=t.id) LEFTJOINtrials_timeASttON(tt.trial_id=t.id)"; EDIT: It didn't work.
VIEWS ON THIS POST

102

Posted on:

Monday 29th October 2012
View Replies!

3.28.58 to 4.1.20 kills my server, the command execution order is different, help me

Folks, I have an old server with MySQL 3.28.58 and I just got a new server and installed MySQL 4.1.20 I kept the same config file, so there shouldn't be sufficient change (heck, the new server got 4gigs ram, the old one just 2). but now I've noticed that the 2 versions are carrying the commands differently, for example OLD SERVER: Code: mysql> EXPLAIN SELECT t.*, p.*, g.g_dohtml AS usedohtml FROM ibf_topics t LEFT JOIN ibf_posts p ON (t.tid=p.topic_id) LEFT JOIN ibf_members m ON (p.author_id=m.id) LEFT JOIN ibf_groups g ON (m.mgroup=g.g_id) WHERE t.forum_id IN (106) AND t.approved='1' AND p.new_topic='1' ORDER BY start_date DESC LIMIT 0,5; +-------+--------+--------------------+----------+---------+-------------+-------+----------------------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-------+--------+--------------------+----------+---------+-------------+-------+----------------------------+ | t | range | forum_id,last_post | forum_id | 2 | NULL | 1397 | where used; Using filesort | | p | ref | topic_id | topic_id | 4 | t.tid | 12690 | where used | | m | eq_ref | PRIMARY | PRIMARY | 3 | p.author_id | 1 | | | g | eq_ref | PRIMARY | PRIMARY | 4 | m.mgroup | 1 | | +-------+--------+--------------------+----------+---------+-------------+-------+----------------------------+ 4 rows in set (0.02 sec) New Server: Code: mysql> EXPLAIN SELECT t.*, p.*, g.g_dohtml AS usedohtml FROM ibf_topics t LEFT JOIN ibf_posts p ON (t.tid=p.topic_id) LEFT JOIN ibf_members m ON (p.author_id=m.id) LEFT JOIN ibf_groups g ON (m.mgroup=g.g_id) WHERE t.forum_id IN (106) AND t.approved='1' AND p.new_topic='1' ORDER BY start_date DESC LIMIT 0,5 -> ; +----+-------------+-------+--------+----------------------------+---------+---------+-------------------------+---------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+----------------------------+---------+---------+-------------------------+---------+----------------------------------------------+ | 1 | SIMPLE | p | ALL | topic_id | NULL | NULL | NULL | 1269621 | Using where; Using temporary; Using filesort | | 1 | SIMPLE | t | eq_ref | PRIMARY,forum_id,last_post | PRIMARY | 4 | dgemu_ibfdb.p.topic_id | 1 | Using where | | 1 | SIMPLE | m | eq_ref | PRIMARY | PRIMARY | 3 | dgemu_ibfdb.p.author_id | 1 | | | 1 | SIMPLE | g | eq_ref | PRIMARY | PRIMARY | 4 | dgemu_ibfdb.m.mgroup | 1 | | +----+-------------+-------+--------+----------------------------+---------+---------+-------------------------+---------+----------------------------------------------+ 4 rows in set (18.02 sec) Due to the difference in how it processes the query, I'm running through over a MILLION AND A QUARTER more queries on the new server than what I did in the old server - what on earth makes it work like that how can I make it work the queries work like they did in 3.23.58 Please help, I'm pretty desperate.. - Ben Pere
VIEWS ON THIS POST

115

Posted on:

Monday 29th October 2012
View Replies!

A union with a limit clause

suppose I have a table of a single column of uid. values are as follows: 1,2,3,4,5,6,7,8,9 suppose I have a second table of friends two columns uid and fid the values I have are (1,2),(1,3),(1,8). I want to do a union on the two tables such that all friends of uid =1 are returned and additional rows from , but I want a total of only 7. Does the query below written in this manner assure that the friends will always be returned and a random sampling of else will be returned Code: select * from ( (select fid from friends where uid=1) union (select uid from where uid != 1 order by rand() ) ) as DT limit 7 I want to ensure that all friends of user1 are returned in the results. If they have 7 friends I don't want any other users to show up. If they only have 3 friends then I want four random values to bring the total up to 7.
VIEWS ON THIS POST

95

Posted on:

Monday 29th October 2012
View Replies!

Duplicating tables help.

I am looking to duplicate a bunch of tables from one DB to another (on the same server) and in my research I found this method of doing it: CREATE TABLE destination_db_name.destination_table_name SELECT * FROM source_db_name.source_table_name HOWEVER after I thought that this would work perfectly, I now realise it doesn't duplicate indexes and autonumbers etc... Is there anyway of doing that so that will duplicate that too I want the duplicate table in the other DB to be a FULLY duplicate table. My MySQL is MySQL 4.0.26-standard
VIEWS ON THIS POST

133

Posted on:

Monday 29th October 2012
View Replies!

Page 2 - Selecting next record

Quote: Originally Posted by r937 those arrays don't mean a thing to me, i don't do php the reason they are "blank" is probably because there is no id that satisfies the WHERE clause in the subquery for example, in your original sample data, what is the next_id for flah.jpg answer: NULL p.s. why are you looking at a DB2 site if you're using mysql well.. that db2 site.. helped me understand it abit anways.. the problem is that... lets say.. one image belongs to lets say... categorie_one wich has an id of 1 right now.. lets say there is another image that belong to another categorie.. now this image wont get the id.. but yet your query is correct.. but anyways.. i will keep playing around with it and see what i can do.
VIEWS ON THIS POST

49

Posted on:

Monday 29th October 2012
View Replies!

How to send to subcribers when listing is inserted by other users using mysql & email

Hi I have a table for users and one for listings. I am wanting the users to fill out an email alert subscription where they may be able to select preferences of listings worked out by location and category, to be emailed to them. Does anybody have any sugestions on what mysql query to write to let this happen. I know how to send the email using php. I think I need to write a query after users have inserted their listing advert and somehow mysql to determine which subscribers suit this listing for an email to get sent to them. Would it be SELECT* FROM users,subscription WHERE users.userid LIKE subscription.stateCode AND regid.regid LIKE subscription.class_category I wonder if this is joining to the listing that is being inserted at the time Hope it makes sense.
VIEWS ON THIS POST

96

Posted on:

Monday 29th October 2012
View Replies!

Export Stored Procedures

How can I export my stored procedures I couldn't find anything in the documentation or on Google.
VIEWS ON THIS POST

44

Posted on:

Monday 29th October 2012
View Replies!

Need help on WHERE clause

I recently purchased a forum from http://www.webwizguide.com/webwizforums/ I need to modify the SQL slightly as when i click a link to one of my forums within my main forum, I only want that Category forum to show. eg; default.aspc=1 to only show Category 1 forum Code:
VIEWS ON THIS POST

113

Posted on:

Wednesday 7th November 2012
View Replies!

acess another sql server

How can I access another sqlserver database table with a SQL statement In the same database, one can access by ..
VIEWS ON THIS POST

64

Posted on:

Wednesday 7th November 2012
View Replies!

**Large database with 30,000 hit at a time ** HELP !!!!!

Originally posted by : Salil Kumar Ghosh (mcc@cal.vsnl.net.in)If I'm going to have a 30,000 hit at a time in my website with a database of 0.4 million records what kind of platform will be ideal. a. NT with SQL server b. Unix with Oracle Database c. Linux with Oracle DatabaseAfter the site is ready, how do I test to ensure that it can handle the expected traffic of 30,000 hits Many
VIEWS ON THIS POST

65

Posted on:

Wednesday 7th November 2012
View Replies!

New to the Sql server

Hi , Iam new to the SQL server.Whats the best way to learn and practice t-sql,sql queries(now iam learning syntax).Can anyone please guide me.
VIEWS ON THIS POST

68

Posted on:

Saturday 10th November 2012
View Replies!

SQL INSERT and Numbers

Originally posted by : Mike Horton (mhorton@planet.eon.net)I'm using a form to get information to insert into a database. I know from using Response.Write that the information passed is correct but I keep getting the following error: Syntax error in INSERT INTO statement.Here's my INSERT code:SQLstmt = "INSERT INTO Drivers (First Name,Last Name,Country,Helmet)"SQLstmt = SQLstmt & " VALUES (" SQLstmt = SQLstmt & "'" & First_Name & "',"SQLstmt = SQLstmt & "'" & Last_Name & "',"SQLstmt = SQLstmt & "'" & Country & "',"SQLstmt = SQLstmt & "'" & Helmet & "'"SQLstmt = SQLstmt & ")"Now Country and Helmet are numbers (I've got them tied into relationships). I've used this exact same code when all the fields were text and it worked great. I've also seen where you use a # around a date so I've tried that with now success. Any help would be greatly appreciatted. BTW there's also a Yes/No field in the table. Does that have to be filled on the INSERT statement
VIEWS ON THIS POST

52

Posted on:

Saturday 10th November 2012
View Replies!

data display from a drop down

Originally posted by : Julie (julie@hangtownwebdesign.com)I could sure use a little help here. I have a drop down menu that offers 6 choices that all works fine but when I tell it to display my info, I have a set criteria that I want displayed. For instance.......... I offer Vehicles, Cars, Trucks, Vans, Sport Utility and Sport Cars in the drop down. You click on one and only those requested SHOULD show. Instead it gives all my cars in the data base. I have three tables made in the database. In the first table I fields that have the same fields Cars, trucks ect.... in each one I labeled all vehicles 1, cars 2, trucks 3 ect... what am I doing wrong
VIEWS ON THIS POST

61

Posted on:

Saturday 10th November 2012
View Replies!

Please help! Stored procedures

Hi , I'm using Stored procedure in SQL Server 2005 to store record in a temp table before generating a report. From this stored procedure, after import some data into the temp table, I will to delete some records if those records in the temp table has type no. "90" and the top record in Source table also has type no. "90", those records should be deleted. If VB code, this is how it goes: cTemp = "select serialnumber, partnumber from OP_TempTable where type = '90'" RSTemp.open ctemp,globalsqlconn,adopenstatic, adlockreadonly, adcmdtext if not rstemp.eof then do until rstemp.eof RSSource.open "select top(1) type from OP_Partsmovement where partnumber = '" & rstemp.fields(1) & "' and serialnumber = '" & rstemp.fields(0) & "' and (type = "90" or type = "41") order by docdate desc, lastupdatedate desc", globalsqlconn, adopenstatic, adlockreadonly, adcmdtext if not rssource.eof then if rssource.fields(0) = "90" then rstemp.delete end if end if rssource.close rstemp.movenext loop end if rstemp.close But I need to create a SQL statement in stored procedure for the above action. I have tried some coding in stored procedure but it does not work. Here's the wrong sql statement: delete from OP_temptable where type = '90' and (select top(1) type from sourcetable where (sourcetable.type = '90' or sourcetable.type = '41') and sourcetable.partnumber = OP_temptable.partnumber and sourcetable.serialnumber = OP_temptable.serialnumber order by docdate desc, lastupdatedate desc) = '90' Can anyone help me with in I have searched for days but no solution.
VIEWS ON THIS POST

62

Posted on:

Sunday 11th November 2012
View Replies!

help optimizing query

I have a query that is taking about 12 minutes to run. I'm sure there has to be a way to speed it up...i'm just not sure how. any suggestions on how to rewrite this would be much appreciated. i think it has something to do with the "not in" join. Select Distinct T.TSNUMB from TSR T where T.TSSTAT='C' and T.TSCDAT >= '01-Oct-03' and T.TSCDAT
VIEWS ON THIS POST

102

Posted on:

Sunday 11th November 2012
View Replies!

paging with stored procedures with parameters

Originally posted by : Oliver (oliver@idea-uk.com)How do you use recordset paging if you are using a stored procedure that has parameters.ADO recordsets don't use paramteter so I am using the command object to pass in the paramters.The paging code works if there are no input paramsbut if they are it will not pass the params through the spCan anyone help
VIEWS ON THIS POST

95

Posted on:

Sunday 11th November 2012
View Replies!

Newbie mysql question

I have successfully connected to a mysql database from an asp page, both of them running on a web hotel. But I can't get it to work on my own web server, all I get is this error message: Microsoft OLE DB Provider for ODBC Drivers (0x80004005) [Microsoft][ODBC Driver Manager] No data source and no Standard driver (translated from Swedish...) /mysql/inc_first_page.asp, line 3 Line 3 looks like this: Code: myConn.Open("DRIVER={MySQL ODBC 3.51 Driver};Server=localhost;Database=bah;UID=userid;PWD=password;") And yes, the database exists and the userid and password are correct. Do I need to install the drivers or what
VIEWS ON THIS POST

74

Posted on:

Monday 12th November 2012
View Replies!

Converting from hours/minutes to seconds

I have a varchar field that contains values like 00:17:17, 01:04:47 which symbolize the time. How can I convert this to seconds 00:17:17 --> 1037 seconds 01:04:47 --> 3887 seconds
VIEWS ON THIS POST

63

Posted on:

Monday 12th November 2012
View Replies!

Access fields called into asp page--how do I make hyperlinks?

Originally posted by : Austin (harrisaj@muohio.edu)I am designing a webpage for a project in one of my classes. We are setting up a database using MSAccess for a movie theater and we are going to create a website that will show movie titles, listings, and capacity in the different theaters. I figured out how to call the movie titles into the page but I can't figure out how to get those movies to link to another asp page that just shows the listings for that movie. Ex. If Cast Away is a movie, I want it to be linked to Cast Away.asp which has its listings and so on. Can anyone help me out
VIEWS ON THIS POST

101

Posted on:

Thursday 15th November 2012
View Replies!

sql 70-228 cert

I am going to be taking the sql certification test in the 3 months I was wondering if anyone had any suggestions on what books I should use as a study guide. I already purchased a meassure up practice test
VIEWS ON THIS POST

66

Posted on:

Monday 19th November 2012
View Replies!

Copying a Recordset ...

Originally posted by : Yves (ypetinot@rand.com)I want to copy a Recordset in my Session object, therefore I'm using Set ... still it only provides a reference to this object and not a copy of it ..Has anyone an idea on how to do this
VIEWS ON THIS POST

45

Posted on:

Tuesday 20th November 2012
View Replies!

SQL - 3 table join

Sorry folks, you can ignore this post (I can't delete it), I found another problem so I'll have to go a different way. Cheers though.
VIEWS ON THIS POST

100

Posted on:

Tuesday 20th November 2012
View Replies!

Bit data type displaying as '-1' ??

, I'm confused about the bit datatype. I have two tables that I'm accessing in different ways. The first is an XSL transform. When creating an XML file from a recordset and then transforming it, bit values show as '-1' for True. However, when accessed from a script which turns the recordset into an XML file (without transform, just a feed) its value is 'True'. What gives!
VIEWS ON THIS POST

85

Posted on:

Tuesday 20th November 2012
View Replies!

Normalization Question

I'm just trying to get some opinions on something. I have a base table [ACCOUNT]. To simplify the question I'll say the table only has three columns [ACCOUNTID],[FIRMID] and [SPONSORID]. [ACCOUNTID] is the PK [FIRMID] is a FK pointing to a table called [FIRM] [SPONSORID] is a FK pointing to a table called [SPONSOR] In the [SPONSOR] table there are names like ABC. Sponsor has a relationship with firm in that Sponsors can have many firms but the firm could also be the sponsor. Ex. Sponsor ABC can have a firm of ABC, DEF, GHI My thinking is that since they are different entities they should be in seperate tables, even though there may be duplicate entries such as ABC across the two tables. One of my co-workers argues that if the name changes or something to the sponsor you then need to update the firm as well. So if ABC changes to 123 the firm will need to be changed to 123 as well. Therefore FIRMs and SPONSORs should be combined. Your opinions would be appreciated. Thank You
VIEWS ON THIS POST

59

Posted on:

Tuesday 20th November 2012
View Replies!

Using Variable in a Where clause

I have an SQL statement which works when I hard code the name and comes up with a syntax error when I use a variable Can anyone help me out Select companyid from table1 where medname= &rsmedi The error shown is as follows" Microsoft OLE DB Provider for ODBC Drivers error '80040e14' [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'medname= &rsmedi'. Thankyou all, M
VIEWS ON THIS POST

141

Posted on:

Tuesday 20th November 2012
View Replies!

Oracle tool for PL/SQL Developers

I found this software DreamCoder for Oracle and it is fantastic for PL/SQL Developers. URL : http://www.mentattech.com
VIEWS ON THIS POST

90

Posted on:

Tuesday 20th November 2012
View Replies!

WHERE Member_Num = & XMreadCookie("memberCookie") ???

.
VIEWS ON THIS POST

62

Posted on:

Tuesday 20th November 2012
View Replies!

automatic updation of one table if data inserted in another table

Originally posted by : vegobha (vegobha@hotmail.com)i am using sql7Is it possible to insert the values of some columns of one table into another table automatically. If so guide me. For ex. I have a table table1 with 5 columns (a,b,c,d,e)i inserted data into them. simultaneously i need to insert 3 columns data (say a,b,e) into table2
VIEWS ON THIS POST

56

Posted on:

Tuesday 20th November 2012
View Replies!

how to use connect string while connecting to informix database useing odbc

Originally posted by : Ram (rbsaroj@rediffmail.com)hello i am faceing problem in connectstringi am able to connect informix rdbms databse by useing odbc from visual basic . But i am not ableto connect to databse by useing same odbc connect string through my ASP page . Can anybody help me with sending exact syntax my email id is rbsaroj@rediffmail.co
VIEWS ON THIS POST

85

Posted on:

Tuesday 20th November 2012
View Replies!

How to refresh the right side frame in the window?

Originally posted by : carlos (carlos_lai@hongkong.com)i have design a web page that is divided into two frames. Left side had a text box and a submit button. I want to refresh right frames when the text box is empty and the user click the submit button. Could anyone suggest some method for me I'm pleading for a help. Thank You.
VIEWS ON THIS POST

76

Posted on:

Tuesday 20th November 2012
View Replies!

Query help

Is there anything wrong about this query strSQL = "SELECT source, message, cdate, ctime From alarms WHERE (SELECT p1.source, p1.message, p1.cdate, p1.ctime FROM alarms as p1 WHERE p1.ctime = (SELECT max(ctime) FROM alarms WHERE source=p1.source AND cdate=p1.cdate) ORDER BY cdate DESC, ctime DESC) AND message LIKE '%Node Down%'" I think it may be overboard...and I'm getting an error "Operand should contain 1 column(s)" But I'm experimenting with this query to try and get what I want and wanted to see if this error was just something minor or not.
VIEWS ON THIS POST

80

Posted on:

Tuesday 20th November 2012
View Replies!

Wiritng to diff tables

hi im completely new at this, so pls bear with me my database front and backend are Access 2002. what i want to know is whether SQL will let me write to multiple tables from one form the prob im having with access is that the subforms that are being produced are not what i need - i need a single page form, with controls that write to 4 diff tables. i was thinking that if i simply created the form with the fields, and then use sql statements as the controls to write to the diff tables is this possible. / feasible
VIEWS ON THIS POST

57

Posted on:

Tuesday 20th November 2012
View Replies!

order by an alias?

I have problems with the following sql query: SELECT c.CarNr, c.Make, c.OutTo, c.OutOn, (t.Date - c.OutOn)+1 AS Days, c.Cost, Days * c.Cost AS Total FROM Cars c, Today t WHERE c.OutOn null ORDER BY Total Asc I get the error: Too few parameters. Expected 1. If I try to order by c.Cost it works. Is there a problem with ordering on an alias It seems like it doesn't find it.. I hope someone can help me
VIEWS ON THIS POST

93

Posted on:

Monday 26th November 2012
View Replies!

how to conver access into sql?

to I'm new and i'm happy that i have found this. I had a question which might have been asked many times: how do I convert access codes into sql I have this data base in access which needs to be in sql . I wanted to know if i have to write codes or something else. I have office xp and sql server 2000 personal edition. hope to get an answer very soon.
VIEWS ON THIS POST

321

Posted on:

Monday 26th November 2012
View Replies!

MySQL Workbench and Primary Keys

I am using MySQL workbench for the first time. I have created an InnoDB relational database. When i connect two tables with a many to one connection it automatically adds a new row in the many table that references the row_id in the other table. When it adds this new row it inserts it as a Primary row. So now i have a table with 2 primary rows. Is this how things should be setup I though every table should only have one primary row Should i set the newly added relational row so that it's not a primary row On a side note. I am using phpMyAdmin through MAMP. I have read some tutorials about connecting MySQL workbench to MAMP's phpMyAdmin. In the tutorials it states that i should configure MySQL workbench to use the file located at /Applications/MAMP/tmp/mysql/mysql.sock but when i go to this location their is no file with this name (http://phpprotip.com/2011/10/using-m...nch-with-mamp/). Anyone know where i can find this file
VIEWS ON THIS POST

51

Posted on:

Friday 28th December 2012
View Replies!

DATE range calucation optimization

I would like to find out if someone can help me to optimize the date range calculation in the following query: Code: SELECT USERID, birthday FROM members WHERE birthday BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 100 YEAR) AND DATE_SUB(CURRENT_DATE(), INTERVAL 0 YEAR) OR birthday='0000-00-00' ; I want a speedy birthday comparison over huge users database. Thank you. , See here http://stackoverflow.com/questions/4...rformance-over. I don't see anything that will help other than POSSIBLY allowing nulls in birthday and switching the default value to NULL so that you are searching for birthday is null rather than birthday = '0000-00-00'
VIEWS ON THIS POST

120

Posted on:

Saturday 29th December 2012
View Replies!

[MySQL 5.0.45-community-nt] JOIN Syntax

Hi all, I hope your help. I have this two tables in MySQL: Code: DROP TABLE IF EXISTS `tbl_1`; CREATE TABLE `tbl_1` ( `id` int(10) NOT NULL AUTO_INCREMENT, `MAC` varchar(3) DEFAULT NULL, `REG` varchar(3) DEFAULT NULL, `CODE` varchar(10) DEFAULT NULL, `NAME` varchar(2) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1; INSERT INTO `tbl_1` VALUES ('1', 'AAA', 'TOS', '380264', 'VF'); INSERT INTO `tbl_1` VALUES ('2', 'BBB', 'TOS', '380264', 'VF'); INSERT INTO `tbl_1` VALUES ('3', 'CCC', 'TOS', '380264', 'VF'); INSERT INTO `tbl_1` VALUES ('4', 'DDD', 'TOS', '380264', 'VF'); DROP TABLE IF EXISTS `tbl_2`; CREATE TABLE `tbl_2` ( `ID` int(10) NOT NULL AUTO_INCREMENT, `CODE` varchar(10) DEFAULT NULL, `matr` varchar(10) DEFAULT NULL, PRIMARY KEY (`ID`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1; INSERT INTO `tbl_2` VALUES ('1', '380264', '112036'); If tried this query: Code: SELECT * FROM tbl_2 WHERE Matr = '112036' AND Code = '380264'; I have this output: Code: ID CODE MATR 1 380264 112036 If tried this join query: Code: SELECT a.Matr, c.Name, COUNT(*) `number` FROM tbl_2 a JOIN tbl_1 c ON a.Code = c.Code GROUP BY c.Code, Matr, a.Code; I have this other wrong output: Code: Matr Name number 112036 VF 4 I need this right output: Code: Matr Name number 112036 VF 1 Group for c.Code and a.Code is not sufficient Can you help me
VIEWS ON THIS POST

74

Posted on:

Saturday 29th December 2012
View Replies!