LINQ exclusion
Is there a direct LINQ syntax for finding the members of set A that are absent from set B? In SQL I would write this SELECT A.* FROM A LEFT JOIN B ON A.ID = B.ID WHERE B.ID IS NULL ...
sql
Select max 5 integers from set of 20
I'm trying to select the 5 most viewed articles out of a list of the 20 most recent entries in a table. My table structure is essentially this: id | date | title | content | views My first thought was just to use an inner select to get the 20 most recent articles, then select from that, but I have yet to have any luck. //doesn't wor...
Dealing with "hypernormalized" data
My employer, a small office supply company, is switching suppliers and I am looking through their electronic content to come up with a robust database schema; our previous schema was pretty much just thrown together without any thought at all, and it's pretty much led to an unbearable data model with corrupt, inconsistent information. T...
Size of varbinary field in SQL server 2005
Hi! I am trying to determine the size in bytes of the contents in a varbinary(max) field in sql server 2005, using sql. As I doubt there is native support for this, could it be done using CLR integration? Any ideas would be greatly appreciated. ...
SQL server version 10 is not supported Error
Does anybody know where I can find the SQL Database Publishing Wizard for SQL Server 2008? When I try and run version 1.1 it says that SQL server version 10 is not supported. I found a MSDN article that states there is a SQL Database publishing Wizard for SQL Server 2008 in Visual studio 2008 but I have no idea where to find it. We are...
SQL query to apply multiple conditions against grouped data
Given the tables: Exam (ExamId, SubjectId) Result (ExamId, StudentId, GradeId) What's the best way of retrieving a list of Students who received GradeId='A' in SubjectId='Maths' AND GradeId = 'B' in SubjectId='English' for their most recent exams in each subject? We can either assume ExamIds increase over time, or add an ExamDate col...
Conditional SELECT of a column
Heyall! Maybe you can help me with a SQL Query: I have a conversion value in a secondary table and the following structure: ID PRICE_BRL PRICE_USD -- --------- --------- 1 10 5 2 12 NULL 3 NULL 3 4 14 NULL 5 NULL 4 6 NULL NULL I need a Result Set Like that prioritizes the first column, in c...
Elegant syntax for multi-column "cascading" constraint in SQL?
I am constructing a query that will naturally return a result that has values for several columns, as well as some integer-valued grouping columns (indicating whether a particular value column was subtotalled but that's not in itself relevant to my question). I want to put a constraint on this query's grouping columns, that only accepts...
Getting Distinct records with date field?
Hello, I have a query where i want to get a distinct description by the latest date entered and the descriptions ID. I can get the disctinct part but i run into trouble with trying to get the ID since im using MAX on the date. Here is the query: SELECT DISTINCT Resource.Description, MAX(arq.DateReferred) AS DateReferred, arq.Assessment...
How to implement NOT LIKE as the search condition for containstable(Full-Text Query)?
What would be the search condition for the query below if we wanted to retrieve all the keys for which the columnName does not contain "thisWord" ? SELECT [key] AS someValue FROM CONTAINSTABLE ( table ,columnName, ??what goes here?? ) Note: This is a full-text based search. ...
Finding unmatched records with SQL
I'm trying write a query to find records which don't have a matching record in another table. For example, I have a two tables whose structures looks something like this: Table1 State | Product | Distributor | other fields CA | P1 | A | xxxx OR | P1 | A | xxxx OR | P1 | B ...
MySQL query to return only duplicate entries with counts
I have a legacy MySQL table called lnk_lists_addresses with columns list_id and address_id. I'd like to write a query that reports all the cases where the same list_id-address_id combination appears more than once in the table with a count. I tried this... SELECT count(*), list_id, address_id FROM lnk_lists_addresses GROUP BY list_id, ...
MySQL Query GROUP BY day / month / year
Heyall, Is it possible I make a simple query to count how many records I have in a determined period of time like a Year, month or day, having a TIMESTAMP field, like: SELECT COUNT(id) FROM stats WHERE record_date.YEAR = 2009 GROUP BY record_date.YEAR Or even: SELECT COUNT(id) FROM stats GROUP BY record_date.YEAR, record_date.MONTH...
Multi-tenancy with SQL/WCF/Silverlight
We're building a Silverlight application which will be offered as SaaS. The end product is a Silverlight client that connects to a WCF service. As the number of clients is potentially large, updating needs to be easy, preferably so that all instances can be updated in one go. Not having implemented multi tenancy before, I'm looking for ...
SQL Delete Where Not In
Hi I have a relation mapping table like this: attributeid bigint productid bigint To clean relations that are not used any more, I want to delete all recors where productid = x and attributeid not in (@includedIds), like the following example: @attributetypeid bigint, @productid bigint, @includedids varchar(MAX) DELETE FROM relt...
Get a list of dates between two dates
Using standard mysql functions is there a way to write a query that will return a list of days between two dates. eg given 2009-01-01 and 2009-01-13 it would return a one column table with the values: 2009-01-01 2009-01-02 2009-01-03 2009-01-04 2009-01-05 2009-01-06 2009-01-07 2009-01-08 2009-01-09 2009-01-10 2009-01-11 2009-01-12...
Best method for Populating DataSet from a SQLDataReader
I am working on a DAL that is getting a DataReader Asynchronously. I would like to write a single method for transforming the DataReader into a DataSet. It needs to handle different schema so that this one method will handle all of my fetch needs. P.S. I am populating the SQLDataReader Asynchronously, please don't give answers that ...
Define Generic Data Model for Custom Product Types
I want to create a product catalog that allows for intricate details on each of the product types in the catalog. The product types have vastly different data associated with them; some with only generic data, some with a few extra fields of data, some with many fields that are specific to that product type. I need to easily add new pr...
error updating row in postgres table
My table is: CREATE TABLE A( id serial NOT NULL, date timestamp without time zone, type text, sub_type text, filename text, filepath text, filesize integer, ); I have an update routine: $Query = "UPDATE A SET type=\"" . $strType . "\" where id=" . intval($ID); Problem: When $strType is a string, like "...
validating data in database: sql vs code
My database schema has a 'locked' setting meaning that the entry can not be changed once it is set. Before the locked flag is set we can update other attributes. So: Would you check the locked flag in code and then update the entry or would it be better to combine that into a SQL query, if so, any examples? EDIT: how would you...