sql

multiple innerjoins with linq

I've got the following sql statement and need to know how to write it in linq, but i can't figure out how to write the multiple inner joins SELECT Zugehörigkeiten.PK_Dept_ID FROM mitarbeiter INNER JOIN (abteilungen INNER JOIN Zugehoerigkeiten ON abteilungen.PK_Dept_ID = Zugehoerigkeiten.PK_Dept_ID) ON mitarbeiter...

How to construct this query in T-SQL

I have a table: Date ColumnA ColumnB 2009-12-29 "abc" "abc" 2009-12-29 "abc" "abc" 2009-12-29 "abc" "abc" 2009-12-28 "abc" "abc" 2009-12-28 "abc" "abc" 2009-12-28 "abc" "abc" ,,, ,,, I want to write a query, in Microsoft SQL, that returns all rows for the latest available date in the...

Suggestions for improving count performance on a SQL query?

I've got a query that is taking a very long time to run due to a select similar to: SELECT Count( Distinct t1.v1), Count (Distinct Case t2.v1 When 'blah' then t1.v1 Else null End ), .... FROM t1 LEFT JOIN t2 ON t1.v3 = t2.v3 WHERE t1.myDate BETWEEN @start and @end AND t2.v5 = @id Can anyone suggest any good methods for improving thi...

SQL - using a variable for an IN clause

I wish to do something like the following: declare @FrameNumber nvarchar(20) set @FrameNumber = '(p1, p2)' select from myTable where c1 in @FrameNumber What is the correct syntax for this? (for note: I need to pass the value of @FrameNumber in as a parameter to the stored procedure... so I have to at least use the string "p1, p2") ...

SQL Select Into Field

I want to accomplish something of the following: Select DISTINCT(tableA.column) INTO tableB.column FROM tableA The goal would be to select a distinct data set and then insert that data into a specific column of a new table. ...

Database "key/ID" design ideas, Surrogate Key, Primary Key, etc

So I've seen several mentions of a surrogate key lately, and I'm not really sure what it is and how it differs from a primary key. I always assumed that ID was my primary key in a table like this: Users ID, Guid FirstName, Text LastName, Text SSN, Int however, wikipedia defines a surrogate key as "A surrogate key in a...

mysql query tuning

Hello, I have a query that I have made into a MYSQL view. This particular view is central to our application so we are looking at tuning it. There is a primary key on Map_Id,User_No,X,Y. I am pretty comfortable tuning SQL server queries but not totally sure about how MySql works in this aspect. Would it help to put an index on it tha...

Which is the most common ID type in SQL Server databases, and which is better?

Is it better to use a Guid (UniqueIdentifier) as your Primary/Surrogate Key column, or a serialized "identity" integer column; and why is it better? ...

Query to retrieve Text data type column value based on MAX value in other column

Hi I have a table with following columns and data FldID | Rev | Words 10257       2        Some text is present here 10257        3        I changed this text 10258        2        Some more text for another item 10258        3   ...

Views or table functions or something else.

I designed 5 stored procedures which almost use same join condition but parameters or values in where clause change for each on different runs. Is it best solution to create a view with all join conditions without where clause and then query from view or work on view? Can views auto update itself if i create view? Can i do sub-queries o...

Inserting multiple rows to table - Access

Hi, I have an Access application in which I need to do a 'mass-update'. I have a form on which I have a dropdown, a listbox, and a checkbox. I also have a Update button, and when the user clicks it, I want to insert rows into my table with the same value for the dropdown and checkbox fields in all the rows, but I want different values f...

Lucene.NET Documents with Matching Field Values

I have an Object with 3 fields in it: ObjectId ParentId ProjectId I have my object mapped to a Lucene.NET index so each document contains the 3 fields and values for the above. I want to perform the following SQL query equivalent as a Lucene.NET search: SELECT * FROM Objects WHERE ProjectId = @ProjectId AND (ObjectId = ParentId OR Pare...

Can using isnull in a where statement cause problems with using indexes?

I have a query like the following: SELECT t1.v3, t2.v2 FROM t1 INNER JOIN t2 ON t1.v1 = t2.v1 WHERE ISNULL(t1.DeleteFlag,'N') = 'N' I have an index in place that I think should result in there being an Index Seek for the = 'N' part but instead I am seeing a very expensive Index Scan. Is it possible that the index is messing up the c...

use parameters in the SQL IN statement - ASP.NET

Hi, I have a query looks like this SELECT CompanyId FROM CompanyTable WHERE CompanyName IN ('Subway','A & W','Pizzahut'). Is there any way I can use sql parameters for the names list? Thanks Edit: Sorry, forget to mention this is not a stored proc ( which I prefer but can't use in this project ). When I say 'parameter', I mean par...

ms-access: rowsource question

i am a vb.net programmer, and i have done lots of VBA with excel. i am a beginner at access. someone has given me an access database to debug and i am lost. i succesfully made it connect to a local mysql database. i have a listbox on a form that has a rowsource attached to a query called listbox. here's a picture of the properties of...

How would I parse a filename from a location I grab from a table before it's bound to the dropdownlist?

So in one of my tables, there lies an image file location. I'm grabbing that information to be displayed in an asp:dropdownlist, however, I want just the name of the image to be displayed. How/Where would I parse the filename out of it. Also, is there a built in method for grabbing the filename? EDIT:: http://msdn.microsoft.com/en-us/l...

What is the most appropriate Coldfusion cfsqltype to use for MS SQL's uniqueidentifier field type?

When connecting from Coldfusion 8 to a MS SQL 2008 datasource, what Coldfusion cfsqltype should I use for a SQL column set to 'uniqueidentifier'. <cfquery name="user" datasource="#ds#"> SELECT id, username FROM users WHERE id = <cfqueryparam cfsqltype="WHAT_CF_SQL_TYPE_HERE?" value="#arguments.id#"> </cfquery> Thanks! ...

Django Generic Relations and ORM Queries

Say I have the following models: class Image(models.Model): image = models.ImageField(max_length=200, upload_to=file_home) content_type = models.ForeignKey(ContentType) object_id = models.PositiveIntegerField() content_object = generic.GenericForeignKey() class Article(models.Model): text = models.TextField() ...

Sql Server: Execute delete against updateable view with joins

In SQL it is possible to run inserts and updates against a view, as long as the view only selects data from one table. However, deletes don't seem to work quite so well. Can anyone help out? Take this view for example: CREATE VIEW v_MyUpdatableView AS SELECT x.* FROM MyPrimaryTable x LEFT OUTER JOIN AnotherTable y ON y.MyPrimar...

SQL Join Question

I have the following tables: Table: user_groups (many-to-many) user_id (int) group_id (varchar) Table: profile_groups (many-to-many) profile_id (int) group_id (varchar) So basically, I want to write a sql script to find out what profile is assigned to each user. So in the end there should be only 2 columns: user_id and profile_...