sql

How to do the following in LINQ

I have the following SQL , how do I implement this in LINQ (c# please :)) SELECT a.Category, a.Description, a.Item, a.[Value], a.Loading, a.MaxDiscount, MIN(b.EffectiveDate) AS FromDate, a.EffectiveDate AS EndDate, a.SortOrder, a.ID FROM List a LEFT OUTER JOIN List b ON a.[Value] = b.[Value] AND a.Category = b.Category AND a.Des...

Can't I use "DECLARE" in table function ?

Thanks for your message I solved my problem like this ALTER FUNCTION [VEZNE].[fnMakbuzIslemGetir] ( @refNo as int ) RETURNS @tablename TABLE (kontrol1 char(1),key0 numeric(18,0) ,tarih datetime ,hizkod char(12),hizad char(75),ytlhizfiyat decimal(18,2) ,hizmiktar numeric(18,2),ytlhiztutar decim...

MySQL runtime-generated view

On redesigning a MySQL database I came up with an idea: creating a VIEW which can be altered when a TRIGGER (on UPDATE of other table) runs, using information selected from the INFORMATION_SCHEMA database. Hence we could have a view of whichever columns might be necessary at runtime. Since ALTER TABLE is illegal inside triggers, the set...

c# sql what to dispose

I have the code below to query records from a stored procedure but am concerned I may not be disposing what I need to or am disposing when the object would be cleared by the Garbage Collector shortly afterward anyway. Do I need to dispose the SqlDataReader since it is within the try catch block? Do I need to run both cmd.Dispose and cmd...

SQL To find difference between multiple rows

I have a table containing multiple records for different transactions i.e. ID Date REF 1 01/09/2008 A 1 11/09/2008 A 1 01/10/2008 A 2 01/09/2008 A 2 01/10/2008 A 2 01/11/2008 B 2 01/12/2008 B and I'm looking to summarise the data so that I have the average days for each id and ref... i.e. ID Ref ...

SP: Get 5 random records plus 1

Hi, I need to get 5 random records from a table plus a further record based on data from the users preferences as stored in a second table. Here are the two statements i have created so far: Gets favourite charity based on key set in TBL_MEMBERS: SELECT DISTINCT TBL_CHARITIES.* FROM TBL_CHARITIES JOIN TBL_MEMBERS ON TBL_CHARITIES.cha...

Handling database record deletions with several levels of offspring

We've got a database that generally has this structure: Master Record Table id (pk) MasterRecordId <-- constrained to be unique Children/Siblings (2nd Generation, if you will): Table1 ( table1ID (pk), MasterRecordID (fk)) Table2 ( Table2ID(pk), MasterRecordID (fk)) Grandkids (3rd Generation): Table3 ( Table3ID (pk) Table1I...

Bringing back only one row in a joined table using MS Access

Hi All I know there are similar postings out there, but when I have tried to use any of them in my own query I can not get it working. Basically, I have 3 tables that I want to query in ACCESS using the SQL view. Initially though and for this example I am trying to do it with just the 2. tb1 name: Tasks tb1 fields wanted: Task ID Ta...

Distinct Values

I have a table with State, City, Town, and Zip code. The zip code has duplicate data since some zip codes have multiple towns. Now I need a query to return all 4 columns, however I do not want to repeat the zip code in the results. I only want to return 1 record per zip code. Where there are multiple towns I would like to return "-------...

Difference between two queries when selecting a random sample from oracle

This question answers the question on how to select a random sample from oracle which is exactly what I need. I do not understand however the difference between that solution SELECT * FROM ( SELECT * FROM mytable ORDER BY dbms_random.value ) WHERE rownum <= 1000 and something li...

SQL Latest Date

I have an interesting bit of logic that I can't seem to get my head around. We have a Purchase table, where each Purchase is linked to a Customer and a Date. We want to pull out the Purchase that each customer made closest to the end of each month. For instance, CustomerID | Date 1 | 01/20/2009 2 | 01/26/200...

Nested Insert within SQL Server (To Encapsulate or not to Encapsulate)

I'm having a general discussion with one of my developers about philosophy of structuring TSQL stored procedures. Let's make a couple assumptions (to avoid responses along the line of "don't put business logic in SPs", etc): Business Logic can be in SPs There is no standard scripted layer of CRUD SPs Complex procedures are executed ...

SQL Elaborate Joins Query

I'm trying to solve the below problem. I feel like it is possible, but I can't seem to get it. Here's the scenario: Table 1 (Assets) 1 Asset-A 2 Asset-B 3 Asset-C 4 Asset-D Table 2 (Attributes) 1 Asset-A Red 2 Asset-A Hard 3 Asset-B Red 4 Asset-B Hard 5 Asset-B Heavy 6 Asset-C Blue 7 Asset-C Hard If I am looking for something havin...

How to create a PIVOT TABLE without summing a column

Here is my query CREATE view pivmehis WITH ENCRYPTION as SELECT APPROVED, [Not Approved] as REJECT, NULL FROM (SELECT MgtApproval from LeaveRequest) l PIVOT ( Sum (SchedId) --I DON'T WANT TO SUM THIS COLUMN FOR MgtApproval IN ([APPROVED], [REJECT], [NULL])) as pvt Original table results: MgtApproval SchedId Approved ...

How can I select the entire row when using max

I have a table with the following columns: SignatureID PatientID PatientVisitID TreatAuthDate HIPAADate DrugTestDate Now I have the following Select statement: SELECT * FROM tblSignature WHERE PatientID = 12345 This select statement returns 8 rows. What I need to accomplish is getting the MAX TreatAuthDate - and with that MAX Trea...

SQL: Limit on CASE (number of WHEN, THEN conditions)

Consider the query (it runs on both Oracle and MySQL) UPDATE table1 SET something_id = CASE WHEN table1_id = 1446 THEN 423 WHEN table1_id = 2372 THEN 426 WHEN table1_id = 2402 THEN 428 WHEN table1_id = 2637 THEN 429 WHEN table1_id = 2859 THEN 430 WHEN table1_id = 3659 THEN 433 END WHERE table1_id IN (1446,2372,2402,2...

Add results from several COUNT queries

I am trying to fetch the sum of several counts in one query: SELECT( SELECT COUNT( * ) FROM comments + SELECT COUNT( * ) FROM tags + SELECT COUNT( * ) FROM search ) I am missing something here. I get syntax error. ...

SQL return rows in a "round-robin" order

I have a bunch of URLs stored in a table waiting to be scraped by a script. However, many of those URLs are from the same site. I would like to return those URLs in a "site-friendly" order (that is, try to avoid two URLs from the same site in a row) so I won't be accidentally blocked by making too many http requests in a short time. The...

MySQL: Database Design that is scalable and flexible?

Soon I'm going to build an application that needs to be scalable and flexible. Since I'm not a "MySQL" Guru I'm wondering if someone with experience could give me a couple of recommendations for achieving this application I'm going to build. Which database model structure do you think is the best for scalable and flexible sites? ...

"Null" value in Queries

I'm trying to run code that will copy fields into a new table, moving them from a _New table to the original table. The VBA code that does this works as such: SQLStatement = CStr("INSERT INTO " & TName & " SELECT * FROM " & TName & "_New") Log.WriteLine "Running query with string """ & SQLStatement & """ " QueryTimer = Timer DoCmd.RunS...