I am trying to port some code from MS-SQL to MySQL and there is this code that declares a variable and then executes some select statements - it looks like: USE MarketDB; GO DECLARE @Q0 VARCHAR(16); DECLARE @Q1 VARCHAR(16); SET @Q0 = '05/30/2008' SET @Q1 = '08/29/2008' Now I try to convert this to MySQL and fail totally. Why does the...
I've got a bit of a nasty query with several subselects that are really slowing it down. I'm already caching the query, but the results of it changes often and the query results are meant to be shown on a high traffic page. SELECT user_id, user_id AS uid, (SELECT correct_words FROM score ...
I have a varchar column in a table that is used to store xml data. Yeah I know there is an xml data type that I should be using, but I think this was set up before the xml data type was available so a varchar is what I have to use for now. :) The data stored looks similar to the following: <xml filename="100100_456_484351864768.zip" ...
I have a "history" table where I log each request into a Web Handler on our web site. Here is the table definition: /****** Object: Table [dbo].[HistoryRequest] Script Date: 10/09/2009 17:18:02 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[HistoryRequest]( [HistoryRequestID] [uniqueidentifier] N...
In SQL Server, I can copy tables or temporary tables to new table, using a SELECT* INTO.. syntax. Someone know how can I make this same action in MySQL? ...
Hi, I am trying to figure out how to use multiple left outer joins to calculate average scores and number of cards. I have the following schema and test data. Each deck has 0 or more scores and 0 or more cards. I need to calculate an average score and card count for each deck. I'm using mysql for convenience, I eventually want this to ...
If I have an update statement like this: UPDATE Message SET Subject = Subject ...will SQL Server actually perform an update or is it smart enough to recognize that no update is really needed? ...
Hi, I have a table: Message (MessageID int, Subject nvarchar(100), Body nvarchar(max)) After a message is being updated on UI, I call a stored proc to update that table. In some cases user might update just subject, in other cases just body. I want this stored proc to only update what has changed, so I'm also passing flags showing wh...
Lets say I have the following data in the Customers table: (nothing more) ID FirstName LastName ------------------------------- 20 John Mackenzie 21 Ted Green 22 Marcy Nate What sort of SELECT statement can get me the number 22, in the ID column? I need to do something like this to generate a unique ID....
suppose I have two columns of integers, A and B. Now I want distinct values from these, meaning if both A and B have 1, I want 1 only once. Note: I am NOT interested in getting distinct rows. I just want to get unique integer values from this table which could either be in A or B I could insert values of A and B in one column of some...
I currently have a website with three builds: development, staging and production. Each of those has it's own MySQL database instance. Each of the instances has different data in it which should not change (orders). My question is, if I made changes to the structure of the development database, is there an easy way to propogate those ch...
I have some servers. Some of them have ips assigned. I want to figure out how many do not. There are clearly more servers than have ips assigned, but my db tells me there are no servers that have no ips assigned... I'm at my wit's end here. Is my DB corrupted in some strange way? SELECT COUNT(*) FROM server ...returns: +--------...
I have the following table: memberid 2 2 3 4 3 ...and I want the following result: memberid count 2 2 3 1 ---Edit by gbn: do you mean 2? 4 1 I was attempting to use: SELECT MemberID, COUNT(MemberID) FROM YourTable GROUP BY MemberID ...but now I want find which record which ...
Hello, I am in a mission to devise an application database in MS ACCESS. In my database there are 5 tables: Master Cash Cheque Detail Month (displays month in a year) Here I have made Master as parent record and 3 others Cash, Cheque and Detail are children to Master table. Here are the fields in master table Lt no Name Re...
i want to come up with a generic (if possible) schema to use for a number of different events that i am managing. These events can be weddings, birthday parties, etc So for i have 3 main tables Contact Table - with the usual info like address, phone, etc Events Table - list of events with some info like date, location, etc Now i am t...
How to select only years from mysql database (but only those years which contain data)? The PHP time() function is used to populate the date in the database. There are more then 10,000 records stored since 2006... I would like to categorize data by date (firstly, user selects year, then month, than day...). I'd also like to be able to...
If we have a timer that starts from lets say CURRENT_TIMESTAMP - 1 Hour we can simply calculate the difference and return 3600 seconds have passed. But what if we want the timer to only count during certian times of the day and only lets say work during weekday or specified days. Look at the below code to see the Create statment to get...
I have 2 tables, Student and Supervisor: STUDENT(supervisorid(pk),name,email....) SUPERVISOR(supervisorid(pk),name,email....) Now I need to print supervisor name, email and the # of students under the supervisor (they will have same supervisor id). Something like: select supervisorname, supervisoremail, tot_stud as (s...
I'd like to select some Addresses from a MySQL DB. Addresses can have a Contact, an Organization, or both. (A Contact can have many Addresses. So can an Organization.) Let's say I want to get all of an Organization's Addresses. That's no big deal. But what if I want to ORDER them by Contact.last_name? Some of the Addresses don't have a ...
I am trying to create some hierarchical data using Oracles object relation features. I have defined a "Post" as follows: create type Post as object (title varchar(20), body varchar(2000), author varchar(200), parent REF Post, MEMBER FUNCTION numReplies RETURN NUMBER, PRAGMA RESTRICT_REFERENCES(numReplies, WNDS) ); / create tabl...