When you CONCAT_WS(' ',field1,field2,field3) in MySQL and if one of the fields is empty, not null, you get multiple separators. An example can be: John[space][space][space]Doe[space]III. How can I make sure there is only one separator. ...
I'm trying to read the column names of a table "Streets" in an Access database by opening an OleDbConnection. I call GetOleDbSchemaTable but I can't seem to figure out how to get at my columns. I'd like to use .NET 3.5 framework if possible. ...
I've pasted a very simplified version of my SQL query below. The problem that I'm running into is that the ORDER BY statement is affecting the select results of my CTE. I haven't been able to understand why this is, my original thinking was that within the CTE, I execute some SELECT statement, then the ORDER BY should work on THOSE resul...
I have a daycode column that stores values like 1,2...7 then in a different table I have cols like field1,field2...field7 I can join them on a key, but how do I select the specific fieldX column based on values passed? Table 1 has the following columns ------------- id prodno field1 field2 field3 field4 field5 field6 field7 Where eac...
I am creating some reports for an application to be used by various states. The database has the potential to be very large. I would like to know which way is the best way to get column totals. Currently I have SQL similar to the following: SELECT count(case when prg.prefix_id = 1 then iss.id end) + count(case when prg.prefix_...
Normally, I'd set a "Contains" filter on my string field, and enter my value in the modal box, but I want to audit the process. UPDATE Data SET Data.[Assumed Expires]=Data.[Assumed Expires]+365 WHERE Data.[Description]=strSearch; Thanks a pile! ...
I have a SQL Server 2000 box that houses several databases, some of which are probably no longer in use. I'd like to clean things up by first taking them offline, and then later removing them all together. The problem is that I don't know how to tell which of these are still being actively used (outside sources may or may not be connec...
Given following table: rowId AccountId Organization1 Organization2 ----------------------------------------------- 1 1 20 10 2 1 10 20 3 1 40 30 4 2 15 10 5 2 20 15 6 2 10 20 Ho...
Basically I have got two things i am passing from a command argument <asp:LinkButton ID="lbEditDetails" Text="Edit..." runat="server" CommandName="EditDetails" CssClass="EditAdults" CommandArgument=<%# DataBinder.Eval(Container.DataItem, "number_slept") & "-" & DataBinder.Eval(Container.DataItem, "booking_ref") %>></asp:LinkButton> I...
I have a classifieds website, and I am thinking about redesigning the database a bit. Currently I have 7 tables in the db. One table for each "MAIN CATEGORY". For example, I have a "VEHICLES" table which holds all information about the following categories of classifieds: cars mc mopeds/scooters trucks boats etc etc ...
I have customer registration table in SQL Server 2000, in this table have password column which is encrypted ... now I want to migrate that table to SQL Server 2008 Plz help me .... ...
CREATE TABLE `surfkid-db`.`Channels` (`name` VARCHAR( 30 ) NOT NULL ,`commercial` BOOL( 1 ) NOT NULL DEFAULT '0' AUTO_INCREMENT ,`usrid` INT( 5 ) NOT NULL DEFAULT '0' AUTO_INCREMENT ,`id` INT( 5 ) NOT NULL AUTO_INCREMENT PRIMARY KEY) ENGINE = MYISAM ; Error: #1064 - You have an error in your SQL syntax; check the manual that corres...
Below is a sample query of what I am trying to do and it gets the job done but I feel like the sub queries are not the best way to go here. Any pointers? SELECT DISTINCT u.UserID, (SELECT COUNT(LoginID) FROM Logins WHERE Success = 1 AND UserID = u.UserID) AS Successful, (SELECT COUNT(LoginID) FROM Logins WHERE Success = ...
Hi, this is my first post, so I appologize if some of the formatting is not as it is usually expected. Please let me know if I've done something wrong. So, my company finally upgraded to MS Office 2010. Up untill now I've been working in 2003. I am a sql programmer, and I constantly create reports in excel that pull data from our dat...
I am getting different result set for these two queries and second result set seems to be correct. What is the difference in these queries. What type of inner join query second is? 1) FROM TABLE1 t1 INNER JOIN TABLE2 t2 ON t1.Id = t2.Id WHERE t1.StatusId = 12 2) FROM TABLE1 t1 INNER JOIN TABLE2 t2 ON t1.Id = t2.Id AND t1.Sta...
Just as I can check if a column does not equal one of the strings given in a set. SELECT * FROM table1 WHERE column1 NOT IN ('string1','string2','string3'); Is there a single function that I can make sure that multiple columns does not equal a single string? Maybe like this. SELECT * FROM table1 WHERE EACH(column1,column2,column3) <>...
I have the following sql database that grails set up for me automatically. see picture for diagram http://yfrog.com/ngskillsdbj Whenever I try to delete all projects from an employee, I am getting a cascade re-save exception on the role_skill. Is that because of the way this is set up where role_skill is keyed into skill and role? I s...
I'm experimenting with Liquibase, trying to get it to copy one database to another. Unfortunately, I keep getting this error: Implicit conversion from data type varchar to varbinary is not allowed. Use the CONVERT function to run this query. The SQL it's generating is here: CREATE TABLE [dbo].[Attachment] ( [Applicantid] u...
Let's assume I have 3 tables in a MySQL table with the following fields. product product_id product_name category category_id category_name product_in_category product_in_category_id product_id category_id What query would you recommend to get a list of all products that are not currently assigned to a category in t...
I have trouble with using Arel to aggregate 2 columns in the same query. When I run this, the whole server freezes for a minute, before the rails dev-server crashes. I suspect an infinite loop :). Maybe I have misunderstood the whole concept of Arel, and I would be grateful if anybody could have a look at it. The expected result of th...