Need help for my database design


can i ask some help for my db design,i want to make cake ordering...but i have some doubt on my tables and my fields,i am not good in this db design,..I hope you can help me.

Please see my attachment.

Thank you in advance.
Posted On: Monday 31st of December 2012 01:45:15 AM Total Views:  365
View Complete with Replies

RELATED TOPICS OF MYSQL PROGRAMMING LANGUAGE




Combining two SQL statments

I have two Sql statements. The first one is: SELECT format(rental_amount,2) FROM lease_event, lease_event_category, lease WHERE lease_event.member_id = '$member_id' And lease_event.due_date >= now() AND lease_event_category.lease_event_cat_id=lease_event.lease_event_cat_id AND lease_event_category.lease_event_cat_desc like '%rent%' AND lease.lease_id = '$lease_id' AND lease.lease_id = lease_event.lease_id ORDER BY due_date which retrivies the annual rental amount. The next one: SELECT elt(field(rent_frequency,'M','W','F','Q','O'),'monthly','weekly','fortnightly','quartly','other') FROM lease WHERE lease_id='$lease_id' determines the frequency for how often rent is paid. I need in one SELECT statement to determine the annual rental and then divide that by the frequency (12 for monthly, 4 for quarterly etc) to calculate the payment per frequency. I am sure this can be done but don't know where to start.
VIEWS ON THIS POST

63

Posted on:

Monday 29th October 2012
View Replies!

Help with Matrix Multiplications in MySQL

, As part of a university project I am migrating a small poll application from PostgreSQL to MySQL and there is one question in particular which has stumped me for a while now. How would one attempt to adjust the following SQL statement which uses matrix multiplication (i.e linear algebra) to work with MySQL The original SQL statement, which works with PostgreSQL 6: update items set newval=COALESCE((select sum(o.oldval) from items o,votes v1,votes v2 where items.id=v1.itemid AND o.id=v2.itemid AND v1.userid=v2.userid and v1.rank
VIEWS ON THIS POST

111

Posted on:

Monday 29th October 2012
View Replies!

Import Backup

Hi , I have a system used to backup my entire SQL database into a .sql file on my server. What i need is a way to re-import it, anyone have any ideas I cant use LOAD DATA INFILE as i do not have permissions...
VIEWS ON THIS POST

57

Posted on:

Monday 29th October 2012
View Replies!

Invalid use of group function - v3

I have tested my query on MySQL 5 and it works, sadly the server it is to be run on only has 3. I was just wondering what I need to update in the following query to get it working on 3 or if it is even possible to get it running on 3. Code: ..... and keyword IN ('keywords') group by people having count(*) >= 1 ORDER BY people ASC Thank you for reading.
VIEWS ON THIS POST

86

Posted on:

Monday 29th October 2012
View Replies!

Using group_concat

I'm querying a set of tables to obtain a result set of articles with a list of authors and keywords that are assigned to the article. I wanted to make sure that I was using the group_concat syntax correctly and that I don't have to include any other columns in my group by clause. I still haven't quite mastered the group by, but I am getting there. Anyhow, here is the query Code: select a.id , a.title , a.posted_date , group_concat( distinct concat( auth.firstname, ' ', auth.lastname ) ) as authors , group_concat( k.keyword ) as keywords from cdcms_articles as a inner join cdcms_article_authors as auth on auth.id in ( select cdcms_articles_authors_id from cdcms_articles_have_authors where cdcms_articles_id = a.id ) inner join cdcms_article_keywords as k on k.id in ( select cdcms_article_keywords_id from cdcms_articles_have_keywords where cdcms_articles_id = a.id ) group by a.id order by posted_date desc;
VIEWS ON THIS POST

91

Posted on:

Monday 29th October 2012
View Replies!

MAX USER_CONNECTIONS Error

I have my own server , but i am really worried about error of max_user_connect limit. I am using MYSQL 5.0.24-standard I am using mysql_connect() function not (mysql_pconnect) My Database user is "Test", Mysql configuations for test are max_connections = 0 "0" for unlimited , I donot know why the erro is coming. Also after upgradtion their is warning for privilige table. Warning: Your privilege table structure seems to be older than this MySQL version! Please run the script mysql_fix_privilege_tables that should be included in your MySQL server distribution to solve this problem! This warning is form phpMyAdmin. Any solution for above two issues
VIEWS ON THIS POST

48

Posted on:

Monday 29th October 2012
View Replies!

Multilingual site, put the prices in tabel or calculate?

like...should i just have a base price and calculate the rest when needed i use php, mysql and mod_rewrite
VIEWS ON THIS POST

76

Posted on:

Monday 29th October 2012
View Replies!

Search multiple results from same column

I am building a search form to search products/product attributes values (two tables) to return products with matching attributes. The structure (simplified) is as follows: PRODUCTS_TABLE products_id; products_name; PRODUCTS_ATTRIBUTES_TABLE attributes_id products_id attributes_value The attributes_value(s) are posted from the form and I am using a loop (PHP) to iterate through them for my query (all posted variables must match for a valid result) - this currently results in a query like the following (but does not work): Code: select p.products_id, p.products_name from PRODUCTS_TABLE p, PRODUCTS_ATTRIBUTES_TABLE pa where p.products_id = pa.products_id and ((pa.attributes_value = 'value1') and (pa.attributes_value = 'value2') and (pa.attributes_value = 'value3')) order by p.products_name What is the correct way to structure this query Muffin
VIEWS ON THIS POST

158

Posted on:

Monday 29th October 2012
View Replies!

Joining an average

Been working on this for hours and I'm not getting anywhere. Help would be appreciated! Two tables: recipe and recipe_rating, for example. I have 20 recipes in the recipe table, and 5 ratings in the recipe_rating table I want to pull all rows from the recipe table, and join the average of all the ratings for each recipe if ratings are availabe. If not, still bring in the recipe. If there are ratings for a recipe everything works great, but if there are no ratings for a particular recipe, I get no recipe either. Here is one of the queries I tried: Code: SELECT recipe.id, recipe_rating.id, avg( recipe_rating.rating ) AS r FROM recipe LEFT JOIN recipe_rating ON recipe.id WHERE recipe.id = recipe_rating.id GROUP BY recipe.id I'm figuring that "WHERE recipe.id = recipe_rating.id" is causing the query to not grab recipes that do not have a rating associated with the recipe id, but I'm stuck. If I eliminate the where clause, I get 20 records of the same thing, and the averages should be aligned with correct recipe id. MySQL 4.0
VIEWS ON THIS POST

77

Posted on:

Monday 29th October 2012
View Replies!

Multiple column unique key

I'm not sure if that's a proper title as using those key words in Google didn't seem to get me the results I needed but it's the best I have right now. I'm going to have a table that will keep track of what users say they're favorite games are. It will link userid to gameid and each user can have multiple favorite games. I could swear I read some where along the way that there's a way to set up the table so it will make sure the combination of userid and gameid fields are unique and therefore won't allow the insertion of the same userid -> gameid values for a single user Is this the case or am I off my rocker
VIEWS ON THIS POST

60

Posted on:

Monday 29th October 2012
View Replies!

Subtract value from sum

i have a user table in which one col is named as POINTS. it contains the points of user gained from different events. now i want to subtract a specific value from the total points that i get using SUM(point) mysql function. i have written a query that is wrong but it will give you the idea what i am looking to do. say i want to subtract 100 points from user then i am using query like Code: update users set SUM(points - 100) please guide me how can i do this.
VIEWS ON THIS POST

55

Posted on:

Monday 29th October 2012
View Replies!

Date_format - how to force display of 2 digit months ?

Here is a bit of php code, that will display a date in mm/dd/yyyy format, but always use 2 digits for the day. Code: date("m/d/Y", strtotime($start_date)); Is there something equivilant i can use in mysql. currently im using this in my query: Code: Date_format(start_date,'%m/%e/%Y') as t_date But the day part is only 1 digit if < 10, is there any way around this
VIEWS ON THIS POST

98

Posted on:

Monday 29th October 2012
View Replies!

Do While Loop in a stored procedure

At the moment I do the following in code but want to know how I can do the same in a stored procedure. Help will be appreciated. Code: Set RS = cn.execute("Select * from datatemp where rdt_id < 500") Do While NOT RS.EOF cn.execute("Update MyData set dat_value = "&rs("rdt_value")&" where dat_id = "&rs("rdt_datid")&"") RS.MoveNext Loop RS.Close Set RS = nothing
VIEWS ON THIS POST

90

Posted on:

Wednesday 7th November 2012
View Replies!

A (hopefully) little problem with SQL executeUpdate

, I'm writing a very basic java database program that reads from a table in MS Access, pastes into gui text fields and allows the user to change the fields and save the changes. The problem I think that I'm having is in the syntax of the executeUpdate statement, and I can't seem to find anything that works. What I've got is: String update = "UPDATE CourseInfo" + " SET CourseNumber = 1204" + " WHERE CURRENT OF " + record.getCursorName(); I've also tried setting up a field named ID and using " WHERE ID = '1' " and a few other variations. Unfortunately my experience in SQL is so limited that I have utterly no idea where I'm going wrong. The error I'm getting is : "Syntax error (missing operator) in query expression 'CURRENT OF SQL_CUR0ad7e658' ".
VIEWS ON THIS POST

102

Posted on:

Wednesday 7th November 2012
View Replies!

user_defined optional field

Originally posted by : elecom (suimos@yahoo.com)hi, in asp/sql2000 applications people often find that the fields and tables provided can't meet their actual business situation so how can i not only provide user_defined optional fields or tables but also let user themself set dependencies with each other.I know how to add , remove, rename a field. i want to go futher more .first i want to insert a field in a position exactly the user wishes in ie broswer. and second I want to design a administrator's backend interface allowing authrized user or administrators to define some fields or tables to meet their special business whitout having to modify program's soucecode reaptlyFor example, administrators can create a Products field that has a selection for each product their company develops and a Versions field that has a selection for each product version. they can set up dependencies for the Products field that tailor the selections available in the Versions field. When users select a product from the Products field, only applicable versions are available in the Versions field. In this case, the Products field is theindependent field and the Versions field is the dependent field. if anyone know the methods pls contact me
VIEWS ON THIS POST

200

Posted on:

Wednesday 7th November 2012
View Replies!

Help with Inset Into

Hi I have been banging my head over this one for a couple of days and cant see where i'm getting the error. Code: strSQL = "INSERT INTO tblSite (Page,SiteSection,Action,menu_item,linkURL,Heading,ContentText)VALUES('" & strPage & "','" & strSection & "','" & strAction & "','" & strMenuItem & "','" & strLink & "','" & strHeading & "','" & strContent & "')" adoCon.Execute(strSQL) where this is the result of the above line of code. INSERT INTO tblSite (page,sitesection,action,menu_item,linkURL,heading ,contenttext)VALUES('site','info','postinfo','Post Help','index.aspmode=site&action=postinfo&menu=in fo','Post Help Item','Posting help item, by completing form and submitting.') and i am getting this error. Microsoft JET Database Engine error '80040e14' Syntax error in INSERT INTO statement. /site.asp, line 139 I am trying to insert into the following table. Page,SiteSection,Action,menu_item,linkURL,Heading, ContentText Any sugestions gratefully recieved.
VIEWS ON THIS POST

57

Posted on:

Saturday 10th November 2012
View Replies!

SQL Help

i have this query: declare @SubCompanyCode VARCHAR(6) declare @ProgramCode VARCHAR(10) declare @AttendeeTypeOption INT DECLARE @AttendeeTypeOptionOP CHAR(2) set @SubCompanyCode= 'A011' set @ProgramCode = 'ACL52B' set @AttendeeTypeOption= '' If (@AttendeeTypeOption'') SELECT @AttendeeTypeOptionOP = 'EQ' ELSE SELECT @AttendeeTypeOptionOP = '' SELECT M.City AS MeetingCity, M.State AS MeetingState, CASE WHEN MA.AttendeeType = 1 THEN 'Participants' WHEN MA.AttendeeType = 2 THEN 'Speaker/Faculty' END AS AttendeeType, Count(A.AttendeeID) as NoofRSVP FROM Programs P INNER JOIN eCDReservations M ON P.SubCompanyCode = M.SubCompanyCode AND P.ProgramCode = M.ProgramCode left outer JOIN MeetingAttendees MA ON M.ReservationID = MA.MeetingID left outer JOIN Attendees A ON MA.AttendeeID = A.AttendeeID left outer JOIN Regions R ON MA.RegionCode = R.RegionCode WHERE P.SubCompanyCode = @SubCompanyCode AND P.ProgramCode = @ProgramCode AND CASE @AttendeeTypeOptionOP WHEN '' THEN 1 WHEN 'EQ' THEN CASE WHEN MA.AttendeeType = 1 THEN 1 ELSE 0 END END=1 GROUP BY M.City, M.State, MA.AttendeeType ORDER BY MA.AttendeeType this query returns all 4 rows even though there is no matching meetingID in MeetingAttendees table. I have to modify this query (or write another query) in such a away that if I run it for "Participants" only means MA.AttendeeType = 1 (I will pass @AttendeeTypeOption=1 for this case) then NoofRSVP should show #of participants and if MA.AttendeeType 1 then NoofRSVP should show 0 and AttendeeType will be empty (means it should ignore 'Speaker/Faculty').
VIEWS ON THIS POST

43

Posted on:

Saturday 10th November 2012
View Replies!

Converting Hours and Decimal Mintues

I would like to know how to modify the following script to convert the units from hours and decimal mintues to hours and mintues. The units format is decimal 9(5,2), I basiclly take the result and pass it crystal reports where I would like to to see units in hours and mintues. SELECT o.empno, m.employeename, o.rcd_type, o.job_code, o.ppdate , o.tsdate,o.unit_type,o.units,o.acct_code,o.pct,o.r ate, o.rcd_code FROM output_hold o,master m WHERE isnull(rtrim(rcd_code),'') 'XX' and m.empno = o.empno
VIEWS ON THIS POST

49

Posted on:

Saturday 10th November 2012
View Replies!

i need some help in SQL server 2000 Query

i am doing a real time project. in that i need an sql query. plase go through the data in the table and help me to get the result. Data in the table is : BulbNo State ChangeTime 2 OFF 19/08/2004 21:23:10 2 ON 19/08/2004 21:22:10 2 OFF 19/08/2004 21:20:10 4 ON 19/08/2004 21:18:50 4 OFF 19/08/2004 21:17:00 7 OFF 19/08/2004 21:13:30 7 ON 19/08/2004 21:12:50 7 OFF 19/08/2004 21:12:40 9 ON 19/08/2004 21:12:30 9 OFF 19/08/2004 21:11:00 10 ON 19/08/2004 21:12:30 10 OFF 19/08/2004 21:11:00 2 ON 19/08/2004 20:22:10 2 OFF 19/08/2004 20:20:10 5 ON 19/08/2004 20:12:30 5 OFF 19/08/2004 20:11:00 7 ON 19/08/2004 20:12:50 7 OFF 19/08/2004 20:12:40 9 ON 19/08/2004 20:12:30 9 OFF 19/08/2004 20:11:00 The result I am expecting is like this : To show the Previous and the Present states of each bulb, with its state change timings and the time diff. between these States B.No Cur. State Cur. Time Prev. state Prev. Time Time Diff.(in sec.) 2 OFF 19/08/2004 21:23:10 ON 19/08/2004 21:22:10 60 4 ON 19/08/2004 21:18:50 OFF 19/08/2004 21:17:00 110 5 ON 19/08/2004 20:12:30 OFF 19/08/2004 20:11:00 90 7 OFF 19/08/2004 21:13:30 ON 19/08/2004 21:12:50 40 9 ON 19/08/2004 21:12:30 OFF 19/08/2004 21:11:00 90 10 ON 19/08/2004 21:12:30 OFF 19/08/2004 21:11:00 90 thanx in adv. Mohan G
VIEWS ON THIS POST

104

Posted on:

Saturday 10th November 2012
View Replies!

Tree Search in SQL

I have a DB looks like: Accounts: + AccountID + AccountName + ParentID I want to search all the Accounts under Parent (but parent has also more parents) so Example: i have an accountid and i want to find is there any other accounts under the same Parent. How can i search it
VIEWS ON THIS POST

60

Posted on:

Sunday 11th November 2012
View Replies!

Sorting Table

I'm fairly new to SQL Reporting and i have a question for a report i do daily in excel. I usually sort the data below by selecting all columns, Data - Subtotal by Login using the Count Function.Then delete the count Login column. Login Created Issue # Response ASHAPIRO 9-Jul-07 1-34616195 LE ASHAPIRO 9-Jul-07 1-38200621 ASHEEHAN 26-Jul-07 1-313726666 LE BLEAHY 30-Jul-07 1-315532609 LE BSWINIMER 18-Jul-07 1-309022034 LE BSWINIMER 6-Jul-07 1-301944125 LE BWILCOX 26-Jul-07 1-311771940 LE CANCRUM 25-Jul-07 1-313274642 LE CANCRUM 14-Jun-07 1-288965475 LE CCOLLETTE 19-Jul-07 1-309651927 LL These are my results in Excel. Do you know how i could get the same or similar results using SQL store procedures or reporting services Any help will be greatly appreciated. ASHAPIRO 9-Jul-07 1-34616195 LE ASHAPIRO 9-Jul-07 1-38200621 2 ASHEEHAN 26-Jul-07 1-313726666 LE 1 BLEAHY 30-Jul-07 1-315532609 LE 1 BSWINIMER 18-Jul-07 1-309022034 LE BSWINIMER 6-Jul-07 1-301944125 LE 2 BWILCOX 26-Jul-07 1-311771940 LE 1 CANCRUM 25-Jul-07 1-313274642 LE CANCRUM 14-Jun-07 1-288965475 LE 2 CCOLLETTE 19-Jul-07 1-309651927 LL 1
VIEWS ON THIS POST

66

Posted on:

Sunday 11th November 2012
View Replies!

Converting varchar(15) to datetime

I've created a stored procedure to use in crystal reports. I keep getting the error "Syntax error converting datetime from a character string". My where clause is as follows: WHERE StudentMedications.Program Like @Program AND Convert(datetime, StudentMedications.[Date]) Between @StartDate and @EndDate Definitions: @StartDate datetime, @EndDate datetime, @Program varchar(20) = '%' StudentMedications.[Date] is a field in the table defined as varchar(15) Any suggestions This is driving me crazy. I
VIEWS ON THIS POST

73

Posted on:

Sunday 11th November 2012
View Replies!

Connecting SQL server through Proxy server

Originally posted by : Sachin Srivastava @ sache (sachin_soft@yahoo.com)Hi all!I want to know How can I connect SQL server , installed on one of my pc in local intranet, to internet , which I am accessing through my local server which is cioonnnected to internet througuh cable modem.pl reply soonsachin
VIEWS ON THIS POST

95

Posted on:

Sunday 11th November 2012
View Replies!

How to show logged in user info on screen

Originally posted by : Aspen (sfreus@quicknet.nl)That's my question. I'm looking for a script that shows the user's info when he/she is logged in.Data is stored in access db and it has to appear on the screen
VIEWS ON THIS POST

80

Posted on:

Sunday 11th November 2012
View Replies!

Selecting specific records

! In my DB (Access) I have a table named 'articles'. Suppose the table has 20 records, is it possible to make a query where the last article is omitted and only the 3 articles before the last one are drawn (i.e. 19-17) If it is - how do I do that Thank you!
VIEWS ON THIS POST

70

Posted on:

Monday 12th November 2012
View Replies!

ok.. I keep getting the same error over a different SQL select statements...Whats up!

Ok, the error I get is --- ADODB.Recordset error '800a0bb9' Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another. /vendorlist.asp, line 68 This has happened now on two different statements.. and I can't figure it out! This particular statement, i ran in QA and it goes perfect..lemme show ya statement and point out line 68 Code: Dim vendorRS, SQL set vendorRS = Server.CreateObject("ADODB.Recordset") SQL = "SELECT ttVendors.company, ttVendors.website, ttVendors.phone, ttVendors.phone800,ttVendors.vendorID," &_ SQL = SQL & "ttInventory.newprice, ttInventory.refurbprice, ttInventory.oosprice, ttInventory.venddescrip" &_ SQL = SQL & "FROM ttVendors INNER JOIN ttInventory on ttVendors.vendorID = ttInventory.vendorID" &_ SQL = SQL & "WHERE ttInventory.prodID = '" &prodID& "'" vendorRS.Open SQL ,objCn, 1, 3 'line 68 DIM company company = vendorRs("company") vendorRs.close set vendorRs = nothing why is this happening.. I can figure out not a thing! Normally i open rs like... vendorRs.open "Select * from wherever", objCn, 1, 3 but since this is a long one, the method above is the only one I know to get it on a bunch of lines.. The ONLY thing I can think of.. is that prodID is passed to the page from a querystring.. could the it be an STR in the page, but the statement need it to be an INT... I dunno, Im lost.. please help, did i do something silly
VIEWS ON THIS POST

109

Posted on:

Monday 12th November 2012
View Replies!

SQL Query to find data of current month

1. Please tell me that if I have 1 table in MS Access with several DATE record & I want current month record. What will be the SQL STATEMENT 2. Please tell me that I have form on ASP page with 2 text boxes each containing day,year respectively and 1 combo box containing month name. How I can save these values in a variable and compare 2 date so that I can get data(MS Access) from table between these dates.
VIEWS ON THIS POST

61

Posted on:

Thursday 15th November 2012
View Replies!

Help with SQL statement!

Hi im trying to write a SQL statement. I have attached a copy of the relationhips windows so you will understand. In the InterComp table I want to add up all InterComp_Quantity fields and also the InterComp_Cost. Which is easy. The problem is I want to group them by month i.e show the total Quantity and cost for each month and obvously I need to be able to seperate the years (In other words I dont want Jan 05 and Jan 06 added together I need Jan 05 & 06 totals shown seperately!). Is this possibly
VIEWS ON THIS POST

99

Posted on:

Monday 19th November 2012
View Replies!

How to start lessons??

Originally posted by : Maverick (mcchen@quicknetcom.com)i'm a newbie in SQL server 7.0 ,anyone could point me to some good sites that tat give free tutorials on creating database in the fastest way would be much appreciated.
VIEWS ON THIS POST

60

Posted on:

Tuesday 20th November 2012
View Replies!

Inserting data into Access

Originally posted by : seth (seth@immune.co.uk)I am a complete newbie to ASP but trying to pick up the language, using Dreamweaver Ultradev I have managed to create a form that will insert fields into a database. However, I keep getting internal server errors.Error type = "Provider (0x80004005) Unspecified error)I can usually load the page once but if I do a refresh it gives errors. Also, I can only get the data to insert if I am using a browser on the server itself. If I try to use the site from anywhere else, I just get a HTTP 500 Internal Server error with no explanation. My code is below, any help would be greatly appreciated...----------------------------------------------
VIEWS ON THIS POST

97

Posted on:

Tuesday 20th November 2012
View Replies!

LEFT JOIN problem

I have 3 tables: test1 id name1 1 value1 test2 id name2 1 value2 test3 id name3 3 value3_01 4 value3_02 I want to display all records of table test1 with values of table test2 (test1.id = test2.id) and values of test3 (if test.id match with test1.id) - in this case test3 has no common ids with test1 so NULLs are displayed. How to make sql query to display: id1 name1 name2 name3 1 value1 value2 NULL I constructed query: SELECT t1.id as id1, t1.name1 as name1, t2.name2 as name2, t3.name3 as name3 FROM (test1 t1) LEFT JOIN (test3 t3, test2 t2) ON (t1.id=t2.id and t1.id=t3.id) but it gives me: id1 name1 name2 name3 1 value1 NULL NULL name2 is NULL instead of deed "value2". WHY LEFT JOIN DEFINITION: SQL LEFT JOIN Keyword The LEFT JOIN keyword returns all rows from the left table, even if there are no matches in the right table. There are no matches in test3 table when joining test1 table so name3 from table3 is NULL in result. BUT There are matches in table test2 (test2.id=1 and test1.id=1) so why null is displayed Hope anyone can help me. I am in big trouble.
VIEWS ON THIS POST

65

Posted on:

Tuesday 20th November 2012
View Replies!

Filter SQL data according to input from an edit box

Iam trying to create a database using delphi 2009, SQL with a access database. What I want is the following: I have an edit box, a grid and button on a form. When I type something in the edit box (doesn't matter what word), then when I click the button I search in the database and when a title with this word is found, the title is displayed in the grid. I this code already, but get nothing ( only empty grid). Code: procedure TForm1.Button7Click(Sender: TObject); begin with ADOQuery1 do begin //assign new SQL expression SQL.Clear; SQL.Add ('SELECT Songtitle, Genre'); SQL.Add ('FROM songs'); SQL.Add ('WHERE Songtitle = "' + SearchData.Text + '"'); SQL.Add ('ORDER BY Songtitle'); Open; {open query + display data} end; end; Please help.
VIEWS ON THIS POST

117

Posted on:

Tuesday 20th November 2012
View Replies!

Sql Like + Freetext + Contains

Hi Guys I am in need of some assistance please. I am using SQL Server 2005 and C#(winforms). For example if I have a winform with a textbox and search button. If I type in ABCD1234. I won't know what the data is coming into the X Table. So there will be hundreds of description data in the description field in X table. So in the X Table, there are ABCD1234,ABCD_1234,ABCD/1234,ABCD 1234 But I will only pick this 'ABCD1234' record up, and the rest won't be picked. They are variations of the same description. It needs to pick also ABCD_1234,ABCD/1234,ABCD 1234 If also type in 5678, in another column like code in X Table. It would have 005678,05678,56780. But I will only pick this '5678' record up, and the rest won't be picked. They are variations of the same code. It needs to pick also 005678,05678,56780 I have tried LIKE, FREETEXT and CONTAINS. But another idea is a table of alternate descriptions for those that you know about and look those up and search for them all. But how can that be done Any ideas please with examples if possible.
VIEWS ON THIS POST

58

Posted on:

Tuesday 20th November 2012
View Replies!

Help retrieving data correctly and displaying correctly

I need to pull information from three tables which would generally not be a problem. I need to pull these fields from the Dealer_Profile table Dealer_Number Dealer_Name Address1 City State Zip Phone Fax Mfg_Id Manufacturer2 table -- joins dealer profile table on the mfg_id ManufactureID ManufactureName The data is kinda 'whack' to say it nicely as I don't believe the tables are fully relational. My issue is that the database structure was designed wrong in the first place over 10 years ago. I would like one row per Dealer if possible, even if they have more than one manufacturer (which most of them do) Possible Deed Layout which is my question at hand: Dealer Info....,Manf1, Manf2, Manf3... Make sense Any thoughts
VIEWS ON THIS POST

115

Posted on:

Tuesday 20th November 2012
View Replies!

Sql subselect..

hi i have a string in my code which is select fields from two different tables which are in my sqlserver database tables... my string is below as follows Code: dim sqlstr as string=SELECT survey96.STU_CODE,survey96.TITLE, survey96.FIRSTNAME,survey96.SURNAME,survey96.ADDRESS1,survey96.ADDRESS2,survey96.ADDRESS3,survey96.ADDRESS4, survey96.POSTCODE, survey96.STU_CAEM,survey96.FACULTY,survey96.DIVISION, survey96.COURSENAME,survey96.COURSECODE,survey96.FEESTATUS, survey96.YEAROFSTUDY,survey96.MODE,survey96.AWARD,survey96.GENDER, survey96.ETHNICITY,survey96.DOB,survey96.JACS2,survey96.JACS3, survey96.vYEAR,survey96.vETHNICITY,survey96.vFEESTATUS,survey96.vLEVEL, survey96.vAGE,survey96.VSTU_DOB,survey96.vGENDER,survey96.AGEGENDER,survey96.MODEvLEVELvYEAR,survey96.JACS1, tblQuestions.Question, tblStudentSurveyDetails.surveycode, tblStudentSurveyDetails.stu_code AS stu_code, tblStudentSurveyDetails.questionid, tblStudentSurveyDetails.ansnumeric, tblStudentSurveyDetails.anstext FROM tblStudentSurveyDetails INNER JOIN tblQuestions ON tblStudentSurveyDetails.questionid = tblQuestions.QuestionId INNER JOIN survey96 ON tblStudentSurveyDetails.stu_code = survey96.STU_CODE now i have another string which in its syntax is using the select from .. the select from it is using is from the sql syntax which i have above.. so basically when i end up with Code: Dim strSelect As String = "select Question FROM " & sqlstr & " GROUP BY Question" when i run this is sql query analyzer i keep on getting this error message Server: Msg 156, Level 15, State 1, Line 1 Incorrect syntax near the keyword 'SELECT'. can someone plz tell me how is can solve this.. all i want to do is select a field from a query by passing the parameter name of the query..
VIEWS ON THIS POST

84

Posted on:

Tuesday 20th November 2012
View Replies!

Dynamic SQL Question

I have to get a count of records using dynamic sql. I have the following and I'm getting errors (Syntax error converting the varchar value 'SELECT ' to a column of data type int.): Code: DECLARE @sCalldate varchar(10) DECLARE @SQL varchar(8000) DECLARE @RC int SELECT @sCalldate ='20050712' SELECT @sEmployeeNameId = '0' SELECT @sAgentid ='0' SELECT @SQL = 'SELECT ' + @RC + ' = Count(*) FROM dbo.tmPunchTime WHERE scalldate = ' + @sCallDate + ' AND ISNULL(sRawLogout, ''x'') = ''x''' EXEC (@SQL) If I put the @RC inside the quotes I get error: Must declare the variable '@RC'. I tested by printing the statement and it prints properly just errors when executing. How can I make this work
VIEWS ON THIS POST

83

Posted on:

Tuesday 20th November 2012
View Replies!

how can select distinct records within loop..??

Originally posted by : dev (devb0505@yahoo.com)i wanna select distinct records within loop..i have got normalize database which carry one user table(which carryes unique records) and another user's skills(repeatetion of records) i made a search on user's skill table and stored in a record set(repeatation is there in the record set) query is1.got search result in record set called rs:userid = rs("userid")while not rs.eof userid = rs("userid")set rss = con.execute(select distinct(user) from user where userid = '"& userid &"')")rs.movenextwendwhen i execute this query it's does'nt give me distinct record i believe we cann't use distinct function in while loop bec'z every record is unique it self within loop. is there any way to select distinct records in loop \t\t\t
VIEWS ON THIS POST

62

Posted on:

Tuesday 20th November 2012
View Replies!

How to concat from single column/multiple records

I created a sql statement that based on several things, sums a field. W.orks great. Now they want the names from each of the selected records to be brought in too. So how do I concat the names associated with the records without creating a whole new statement As you can see, it is long, complicated due to fool-proofing. Since it picks out selected records, I need to make sure the names pulled in is for those records only. so, how do I get : Harry Joe Sam for the result of rec(1) when there are 3 records totaling 46 for rec(0) If Size "Slurry" And typez "Transition" Then If IsNull(Size) = False Then sqlstatement = "SELECT sum([ShiftTonnes]) FROM dbo.cur_Train1_Bagging " & _ "WHERE [Date] BETWEEN '" & startdate & "' " & _ " AND '" & enddate & "' AND [grade]= '" & Gradez & "' and [BagSize_lbs]= '" & Size & "'" & _ "and [lotlocation]not like 'slurry'" record.Open sqlstatement, , adOpenStatic Form_Tr1_WR.Act_10_Tonnes_1 = Round(record(0), 1) record.Close End If End If If Size "Slurry" And typez = "Transition" Then If IsNull(Size) = False Then sqlstatement = "SELECT sum([ShiftTonnes]) FROM dbo.cur_Train1_Bagging " & _ "WHERE [Date] BETWEEN '" & startdate & "' " & _ " AND '" & enddate & "' AND [grade]= '" & gradez2 & "' and [BagSize_lbs]= '" & Size & "'" & _ "and [lotlocation]not like 'slurry'" record.Open sqlstatement, , adOpenStatic Form_Tr1_WR.Act_10_Tonnes_1 = Round(record(0), 1) record.Close End If End If
VIEWS ON THIS POST

84

Posted on:

Tuesday 20th November 2012
View Replies!

Exclude records that do not meet CASE WHEN condition

- I am very very new to SQL and am hoping you can assist me with my SQL dilemma. Code: (CASE WHEN r.orig_cntry_cdr.dest_cntry_cd and r.shp_pay_ind IN ('P') and r.cnsgn_flg'Y' THEN 'B3P' END) as cat, My results are returning records with cat "B3P" as well as cat \t. Total 100K records. I assume the records with "cat" \t do not meet the condition above. What I would like to do is reduce the # of records returned by EXCLUDING all records with cat \t - those that do not meet the CASE WHEN condition. Would appreciate any assistance you can provide!
VIEWS ON THIS POST

81

Posted on:

Tuesday 20th November 2012
View Replies!

SQL Subquery Problem

I am trying to extract data from two tables in the same database and simply display a COUNT total. The code is as follows: SELECT tbl_personnel.Assignment, COUNT(tbl_personnel.User_Name) AS Troopers_Number, (SELECT COUNT(*) FROM tbl_twelve_hours GROUP BY tbl_personnel.assignment) AS Completed_Surveys FROM tbl_personnel WHERE tbl_personnel.Region='II' AND (tbl_personnel.Rank='Trooper' OR tbl_personnel.Rank='TFC' OR tbl_personnel.Rank='Corporal' OR tbl_personnel.Rank='Sergeant') GROUP BY tbl_personnel.Assignment I am using an ASP movenext loop to display the results. The problem is that when I run it, the subquery does not loop, it displays the same results (shown below in the "Completed Surveys" column) for all the groups. If I change the subquery GROUP BY statement to tbl_twelve_hours.assignment, I receive an error message that it can only return one record. I have been fooling with this for a few hours and am out of ideas. Any suggestions
VIEWS ON THIS POST

125

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

79

Posted on:

Tuesday 20th November 2012
View Replies!

CASE Statement Problem

Attempting to do this query in Access and getting a syntax error in regard to the CASE SELECT [Print Production Table].[Project Name],[Print Production Table].[Print File Name], CASE WHEN [Shipped Date]-[File Create Date] > 0 then 'Y' else 'N' end AS [SLA MISSED] FROM [Print Production Input Table];
VIEWS ON THIS POST

73

Posted on:

Monday 26th November 2012
View Replies!

Max of Sums query....

Need help with a query. Imagine the following table: tblExample ID...Comp...Date.......Value 1....A......1/1/08.....17 2....A......1/1/08.....22 3....A......1/1/08.....19 4....A......1/2/08.....22 5....A......1/2/08.....25 6....A......1/3/08.....15 7....B......1/1/08.....31 8....B......1/1/08.....30 9....B......1/2/08.....28 10...B......1/3/08.....33 What I need is a way to sum each company for each day and then take the max of those sums. For instance, taking the sums of the above companies for each day would yield: Comp...Date.....Value A......1/1/08...58 A......1/2/08...47 A......1/3/08...15 B......1/1/08...61 B......1/2/08...28 B......1/3/08...33 Then, I would need to take the max of these values. So, the overall result of the query should look like this: Comp...Date.....max(value) A......1/1/08...58 B......1/1/08...61 The range is going to specified by the user, but for that given range, I need to sum the values for each day and then take the max and return the day(s) the max occurred and what the max was for each company.
VIEWS ON THIS POST

88

Posted on:

Monday 26th November 2012
View Replies!

How is SQL / ODB used?

Now, I hate to ask such a basic question here but I have googled and found no explanation / tutorial that makes sense to my brain. So here's some questions: I've learned a little about accessing SQL databases via ASP as well as MySQL via Telnet. And though everythings still a little fuzzy, one of my main concerns is this: 1. How is a database created No - I'm not looking for anyone to post code. But I'm wondering if an SQL db is something created manually - laid out by the developer/administrator; and then accessed, modified, and appended via code. Or, can you create a database via code at run time 2. Until now I've used ASP filestreams to store user inputted data across simple text files. I'm now moving into writing a new forum/BBS. (something I've done in the past using these simple textfile structures) I'm wondering if an SQL database is a better method of storage Especially since I've noted a tendency of the server to corrupt my textstream created files every now and then. 3. Can anyone suggest some quick and simple tutorials that A.) show how to set up a simple db using MySQL via Telnet, and B.) give basic examples of ODB object access methods in vbScript
VIEWS ON THIS POST

70

Posted on:

Monday 26th November 2012
View Replies!

Search date excluding records with same day

MS SQL 2k I hae a simple search SELECT *FROM dbo.tbljobs WHERE dtdateexpires >= GETDATE() however its not including the records where dtdateexpires is the same as the current date.. any ideas
VIEWS ON THIS POST

146

Posted on:

Monday 26th November 2012
View Replies!

Probably a simple problem

I have two tables, one has professors names in them (only 3 records) (table name: Name) and the other table (results) has survey records which include the name of the professor as one of the fields. What I would to have is a query that would show me the number of surveys taken for all 3 professors. The problem is that 2 of these professors have no records in the Results table so that when i run a count of ID (primary key in the Results table) it only shows 1 professor with the count of 60 but it doesn't show the other professors with 0. My query only returns 1 record, but I need all 3 with two of them with a zero next to them This is what i have: SELECT Name.Name, Count(Results.ID) AS CountOfID FROM Name INNER JOIN Results ON Name.Name = Results.InsName GROUP BY Name.Name; HELP =)) Thank you for your time.
VIEWS ON THIS POST

56

Posted on:

Monday 26th 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

110

Posted on:

Monday 26th November 2012
View Replies!

[SQL Server 2005] Between syntax

Hi all, I return after time because I have problem with this sql query in my DB Sql Server 2005. I need this output: Code: MANO 32 MANE 17 MACO 69 MASO 45 Tot 163 Instead i have this other and wrong output: Code: MANO 29 MANO 3 Tot 32 MANE 15 MANE 2 Tot 17 MACO 15 MACO 2 MACO 52 Tot 69 MASO 33 MASO 12 Tot 45 I don't understand in the output I have one row for each date range included in the between syntax. Can you help me Thank you. Code: SELECT COALESCE ([MAT], 'Tot') AS [MAT], [A] FROM ( SELECT DISTINCT [myDate], CASE WHEN LEFT (TZZ, 2) = 'XD' OR LEFT (TZZ, 2) = 'XF' THEN 'MANO' WHEN LEFT (TZZ, 2) = 'XG' OR LEFT (TZZ, 2) = 'XH' THEN 'MANE' WHEN LEFT (TZZ, 2) = 'XI' OR LEFT (TZZ, 2) = 'XM' OR LEFT (TZZ, 2) = 'XS' OR LEFT (TZZ, 2) = 'XO' THEN 'MACO' WHEN LEFT (TZZ, 2) = 'XP' OR LEFT (TZZ, 2) = 'XQ' OR LEFT (TZZ, 2) = 'XR' THEN 'MASO' ELSE 'Tot' END AS [MAT], SUM ( CASE WHEN ( [R-M] = '1' OR [R-M] = '2' OR [R-M] = '3' OR [R-M] = '4' ) AND [oldDate] IS NOT NULL THEN 1 ELSE 0 END ) AS [A] FROM dbo_40 WHERE [TZZ] NOT LIKE 'LG%' GROUP BY [myDate], CASE WHEN LEFT (TZZ, 2) = 'XD' OR LEFT (TZZ, 2) = 'XF' THEN 'MANO' WHEN LEFT (TZZ, 2) = 'XG' OR LEFT (TZZ, 2) = 'XH' THEN 'MANE' WHEN LEFT (TZZ, 2) = 'XI' OR LEFT (TZZ, 2) = 'XM' OR LEFT (TZZ, 2) = 'XS' OR LEFT (TZZ, 2) = 'XO' THEN 'MACO' WHEN LEFT (TZZ, 2) = 'XP' OR LEFT (TZZ, 2) = 'XQ' OR LEFT (TZZ, 2) = 'XR' THEN 'MASO' ELSE 'Tot' END WITH ROLLUP ) AS SubQs WHERE [myDate] BETWEEN '2012-07-01' AND '2012-10-08' ORDER BY CASE MAT WHEN 'MANO' THEN 1 WHEN 'MANE' THEN 2 WHEN 'MACO' THEN 3 WHEN 'MASO' THEN 4 ELSE 5 END;
VIEWS ON THIS POST

107

Posted on:

Friday 28th December 2012
View Replies!

Whats wrong with this query?

Hai folks Table candiates cid | agency_code +++++++++ 1 | 3 2 | 3 3 | 2 4 | 1 5 | 3 6 | 4 7 | 4 8 | 1 Table approvals cid ++++++++++ 4 5 8 i want to list * all the candiates belongs to agency code 3 and not found in approval tables. Code: $$agency_code='3' $query="SELECT candidates.* from candidates, approvals" . " WHERE (candidates.cid approvals.cid) AND candidates.agency_code='$agency_code'"; the result should be 1| 3 2| 3 but i get strange result. 1|3 1|3 1|3 2|3 2|3 2|3 5|3 5|3 5|3 somthing like that
VIEWS ON THIS POST

109

Posted on:

Friday 28th December 2012
View Replies!

[MySQL 5.0.45-community-nt-log] Sort the ?rollup? in group by

! I found that the "with rollup" option used with group by is very useful. But it does not behave with "order by" clause. Is there any way to order by my way Code: SELECT COALESCE (name, 'GRAND TOTAL') AS name, tot FROM ( SELECT name, COUNT(*) AS tot FROM tbl_p ) GROUP BY name WITH ROLLUP ) Q ORDER BY CASE WHEN name = 'GRAND TOTAL' THEN 1 ELSE 0 END; Output: Code: +------------------------------+--------+ | name | tot | +------------------------------+--------+ | SEAN DUBOIS | 3 | | SEAN MAXWELL | 2 | | GRAND TOTAL | 39 | | DILLINGER BOY | 12 | | DILLINGER GIRL | 22 | +------------------------------+--------+ I need this output: Code: +------------------------------+--------+ | name | tot | +------------------------------+--------+ | GRAND TOTAL | 39 | | DILLINGER BOY | 12 | | DILLINGER GIRL | 22 | | SEAN DUBOIS | 3 | | SEAN MAXWELL | 2 | +------------------------------+--------+ Could you please help
VIEWS ON THIS POST

75

Posted on:

Sunday 30th December 2012
View Replies!