sql

What will be the LINQ query for the below SQL Query

SELECT t_PersonalInformation.personalInformation_Name, t_PersonalInformation.personalInformation_PresentAddress, t_Applicant.applicant_TotalExperience, t_Experience.experience_CompanyName, t_Experience.experience_Responsibilities, t_Training.training_TitleDetails FROM t_Applic...

Query to delete records

I have a table with the following fields. My requirement is to delete the records from the table when 1. The Flag is set to 'No' 2. For a title, in one record if the flag is set to 'No' and in another record if it is set to 'Yes'. Delete both the records. For example TH-123, In 1st record the flag is set to 'No' and in 4th record flag i...

Finding first search value across columns and retrieving column details it was found in

Hi, I was wondering if it was possible to determine from which column a coalesce value is drawn from? I have the following example data (actual years range from 1989 - 2010 in data, not shown for brevity) ID | 2000 | 2000 value | 2001 |2001 value | 2002 |2002 value | 2003 |2003 value | 2004 | 2004 value | 2005 | 2005 value...

SQL: select one record for each day nearest to a specific time

I have one table that stores values with a point in time: CREATE TABLE values ( value DECIMAL, datetime DATETIME ) There may be many values on each day, there may also be only one value for a given day. Now I want to get the value for each day in a given timespan (e.g. one month) which is nearest to a given time of day. I onl...

Anything faster than count(1) from tableA to find row counts ?

Is there any thing from which we can faster get the no. of rows in a table instead of using count(1). Further there are two cases : a) when we want to get the no. of rows in a table. b) when we just want to know if there is at-least one row. Thanks in advance. ...

Create T-SQL Constraint to prevent x amount of duplicate records in table?

In table A I have 2 columns: ID (int, PK) MaxUsers (int) In table B I have 2 columns: ItemID (int) UserID (int) The number of records in table A with matching ItemID's cannot exceed the MaxUsers value. Is it possible to write a T-SQL Table Constraint so that it's not physically possible for this to ever happen? Cheers! Curt ...

Associating Dimensions in SQL Server Analysis Services 2008

I have a SSAS project (simplified) with 3 dimensions: Employer, Location, Time, Department. I have applied security on the Employer dimension, so that users can only see the employers that they are associated with. But the requirement mandates that when the users browse Location dimension, they only see Locations associated with the empl...

Primary key on a column.

Hi All, I have a table with 2 columns and 2 records. Column1 will never change but the Column2 might have chances that it will change but table will have only 2 records. Column1 Missing Invalid Column2 \\sqlserver\destination\missing \\sqlserver\destination\invalid I am little confused here about the primary key that i wanna put...

Join -- NOT (A2007)

I have a classic two-listbox form construct (Move a row from this box to that box...) I want the box to the left to contain all rows that are NOT in the box on the right. The underlying data is in a many-to-many relationship. PartNbr -- XREF -- Source, where a part may have 0 or more sources...so I have three tables, Item Master, XREF, ...

Import Lotus Notes Database into Access/SQL - What about Documents?

I have a few Lotus Notes 'databases' that i'd like to import into Access or SQL. I think I have most of the steps down (install NotesSQL ODBC driver, setup ODBC connection to Lotus DB, import data into Access), but I can't figure out what to do with all the documents, eg: Word Files, PDF Docs, Excel Workbooks that were in the Lotus DB. ...

HQL Query to check if size of collection is 0 or empty

I try to generate a HQL query that include user with a empty appoinment collections(mapped by OneToMany): SELECT u FROM User u JOIN u.appointments uas WHERE u.status = 1 AND (uas.time.end < :date OR size(uas) = 0) I tries it on several manners (NOT EXIST ELEMENT(), IS NULL) also see: http://stackoverflow.com/questions/1105011/how-to-c...

Stumped on ServiceModel.FaultException forever...

So I'm still not sure if this is on my side, the service side, or both sides. I have NO idea where the main source of the issue is coming from, but I will try and do my best to explain the steps I have taken and what has happened so far so you guys have all the info you need to bang your head against this with me. So we have a Web Servi...

How do you say in SQL/ActiveRecord, "Find all records where attribute is nil or 'x'"?

I want to find records in ActiveRecord that have an attribute that is either nil or some value: class Model < ActiveRecord::Base end class CreateModels < ActiveRecord::Migration def self.up create_table :models do |t| t.string :some_property end end def self.down drop_table :models end end Model.all(:co...

Include table name in SQL query

What I need to do is get a table name in result from a union query with 4 selects from different tables. I need to get the id and the table name for further processing. For example i have table1, table2, table3 table4 and have a query: SELECT id from table1,blablabla UNION SELECT id from table2,blablabla UNION SELECT id from table3,bla...

How to count each value of a column in a table?

I have a table like this: UserID Customer ID status 1 1 1 1 2 1 1 3 1 1 4 2 1 5 1 1 6 3 1 7 2 2 8 1 2 9 2 ........ ...

Getting a wellformed sql-result with habtm relations

Hi there… I need to write an SQL-Query for a csv-export. I have a table "stores", "items_stores" and therefor "items". Stores HABTM Items. Now i want a result that has a column for the items. Like this: | Name | Lat | Lng | Items | | Petes shop | 123 | 123 | Snacks, Pizza | | Mama Pasta | 123 | 123 | Pasta, Pizza | Yo...

The name "X" is not permitted in this context. Valid expressions are constants, constant expressions, and variables. Column names are not permitted.

Hi All, I have a following table with primary key on TypeOfReport column and i am trying to insert records in it but i am getting an error : The name "InvalidAwps" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted. Colum...

SQL Server search for a column by name

I'm doing some recon work and having to dig through a few hundred SQL Server database tables to find columns. Is there a way to easily search for columns in the database and return just the table name that the column belongs to? I found this, but that also returns Stored procedures with that column name in it... ...

How to make a query which gets and lists data from the last entry ?

How do you make a query which gets data and lists it from the last entry in order of the ID adding it onto the code shown below, Example which needs add on, $data_table = "posts"; $per_page = 8; $start = $_GET['start']; $query = mysql_query("SELECT * FROM $data_table LIMIT $start,$per_pag...

Naming Boolean (bit) Values in a SQL db

The AdventureWorks Data Dictionary specifies that the [EmailPromotion] column in the [Contact] table is an int and that: 0 = Contact does not wish to receive e-mail promotions. 1 = Contact does wish to receive e-mail promotions. and [Employee].[CurrentFlag] uses bit as follows: 0 = Inactive 1 = Active My question has two parts: I...