I'm having an issue with OPENXML in SQL Server 2005 where I'll get no results back if the XML doesn't have every tier available. An example would clear up my question: So we have XML like this: <Connection Name="DEFAULT" />' <Branch Name="A_Branch"> <Leaf Name="A_Leaf.OP" > <Property Name="A_Property" /> </Leaf> ...
I have a table with (among other things) a name and a rank. I'd like to return the set of all unique names, but for each name returned, I'd like to pick the row with the highest rank. This is simple with two nested SELECT statements: SELECT * FROM (SELECT * FROM foo ORDER BY rank DESC) AS ordered GROUP BY name MySQL takes the first ...
Greetings, I have 3 tables in my SQL database as follows Table Users2 UserID, EID, Name, Email, Department, Enabled Table Sites SiteID, SiteCode, SiteName Table UserSites2 UsersSitesID, UserID, SiteID What I need to do is, given EID and SiteID, get a full row from the Users2 table AND the SiteID, SiteCode and SiteName from the ...
Hi guys, I need a quick look at my SQL query. It's already given me a headache. Comparing some advice I have gotten, I could not figure out the proper format for my SQL. This is my code: $query = "INSERT INTO `[my_db_name]`.`members` (`id` ,`first` ,`last` ,`add1` ,`add2` ,`phone_home` ,`phone_cell` ,`phone_dad` ,`phone_mom` ,`email1` ...
Say I have a database table T with 4 fields, A, B, C, and D. A, B, and C are the primary key. For any combination of [A, B], there is always a row where C == spaces. There may or may not be other rows where C != spaces. I have a query that gets all rows where [A, B] == [in_a, in_b], and also where C == in_c if such a row exists, or C...
This is probably a really stupid question, but is there going to be much benefit in indexing a boolean field in a database table? Given a common situation, like "soft-delete" records which are flagged as inactive, and hence most queries include WHERE deleted = 0, would it help to have that field indexed on its own, or should it be combi...
Hi, Does anyone know how I would go about concatenating a string in SQL Server 2005. What I mean is something like the following scenario. I have a nvarchar(MAX) column in a SQL Server 2005 database. Lets say the column has a value of "A" and I want to add "B" making "AB", what is the simplest way to go about this. Will I need to do a...
I have created a table like this: CREATE TABLE A ( ID BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY ( START WITH +1 INCREMENT BY +1 NO MINVALUE NO MAXVALUE NO CYCLE CACHE 20 NO ORDER ) , ID_MIRROR CHAR(20)) I would like to do an insert such that ID would be automatically set, and ID_MIRROR would be what is in ID, but prefixed with ...
I'm trying to find unique User Id's with and associated Media Id. Here is what I have: Select UserId, (Select Top(1) MediaId From Media Where UserId = M.UserId ORder By NewId()) as MediaId From Media as M Group By UserId I tried different group by combinations, but none of them worked. Is there a better way to do this? Edit The ...
I've added a dataset , and a table adapter to my C# project.(The dataset and tableadapter was added via DataSources box) In this query , I get table data , then filter , then I will iterate and fill a listbox. listBox1.Items.Clear(); ETPDataset.t_USR_UsersDataTable tbl = (new ETPDataset.t_USR_UsersDataTable()); ETPDatasetTableAdapters.t...
We are trying to build a SQL query builder that would allow users to make queries using visual aid . We have got the front end all worked up with the user being able to select tables add condition etc . However our backend is a mess as we are trying to make these queries as string taking the conditions from the user , and then making app...
Hi, I am trying to extract a column value from the "first" row that satisfies some criteria, where "first" is defined by a hard-coded list of column values. Unfortunately I'm a SQL amateur. I'm using DB2. I can do it inefficiently like this: SELECT COALESCE( (SELECT COL FROM SOMETABLE WHERE X = "txt1" AND Y = "txt2" AND COL = 'A'), ...
I have database with few thousends jobs that have a column with value "active" or "inactive". About 5% of all jobs are "inactive". Jobs are added on daily basis (in numbers of hundrends). When existing job is marked as "inactive" we maintain the ratio to accurate (by deleting "inactive" jobs if there are too many of them). I need to so...
hi I have problem with a counting column in my view. SELECT ColumnC, ColumnA % ColumnB AS ModuloColAColB, COUNT_BIG(*) AS cBig FROM dbo.T1 GROUP BY ColumnC, ModuloColAColB Query is similar to this MSDN example: http://msdn.microsoft.com/en-us/library/ms191432.aspx When I try to compile view I received error message like: "invalid ...
If I execute a stored procedure with passing parameter it's executing fine. If I didn't pass a parameter, sometimes it is giving an error. null dataset. Same sp if I pass with dummy parameter it is returning dataset. Somebody help me please. Priya ...
I have a stored procedure that has this select statement in it: select (select Count(FaqId) from faq_read_stats where faqid = Faq.Id) as ReadCount, Faq.* from Faq where Faq.Show = 1 and Faq.Active = 1 This gives me the results I want but it seems like having the ad-hoc select count in there might not be optimized for SQL Ser...
I have a rgb value and if it doesn't exist in the color table in my database I need to find the closest color. I was thinking of comparing all values and finding the difference(in red,green,and blue) then take the average. The lowest average deviation should be the closest color. There seems to me like there should be a better way. A...
I have a linq to sql query var items = from p in ctx.bam_Zending_AllInstances join q in ctx.bam_Zending_CompletedRelationships on p.ActivityID equals q.ActivityID join r in ctx.bam_Prestatie_AllInstances on q.ReferenceData equals r.ActivityID where q.ReferenceType == "Activity" && p.Zendingnummer =...
Hi I am fairly new to SQL - so be gentle with me! I basically have two tables that I am trying to join where the primary key in the first table is used multiple times in the second table (and forms the primary key in the second table along with another column) It's probably best if I provide an example: Table 1 : TravelRecords Travel...
I have a stored proc that I use to create an xls file and then write data from queries to it. I can't take credit for this, here's the source http://www.simple-talk.com/sql/t-sql-programming/sql-server-excel-workbench/ . I can write varchar, txt, etc without issue. I can't seem to get any datetime fields to write and I have guessed at al...