sql

mysql prepared statement parameters and ordering queries

I have a form, the purpose of which is to place the currently displayed record into a category. I am using the following html code via php to do so: <form name="categoryForm"> <input name="radiobutton" type="radio" value="fakeapproved" />Fake (Approved)<p> <input name="radiobutton" type="radio" value="fakesuspected" />Fake (Suspecte...

Left side of a floating point number in Sql Server

I have a query returning a column of floating point numbers but I am only interested in the number before the decimal place. I don't want to round the number so I am looking for something like: 1.95 = 1 1.45678 = 1 12.00 = 12 12.9999 = 12 Is there an easy way to achieve this in SqlServer other than doing a substring? ...

Flatten relational data for summary/export

Say we have two tables in an MS Access db: Service Users: | ID | Name | Other details... | | 1 | Joe | Blah... | | 2 | Fred | Qwerty... | | 3 | Bob | Something else...| Teams providing services: | ID | TeamID | UserID | | 1 | T1 | 1 | | 2 | T2 | 1 | | 3 | T2 | 2 | | 4 | T3 | 2 ...

A messy SQL statement

I have a case where I wanna choose any database entry that have an invalid Country, Region, or Area ID, by invalid, I mean an ID for a country or region or area that no longer exists in my tables, I have four tables: Properties, Countries, Regions, Areas. I was thinking to do it like this: SELECT * FROM Properties WHERE Country_ID NOT ...

Turn very simple Expression<Func<T, bool>> into SQL where clause

I have a number of different data sources that I need to query and have been able to do confine all my queries to very simple expressions with no more than 2 conditions. An example of typical complexity of my lamba Expressions is: b => b.user == "joe" && b.domain == "bloggs.com" On my non-SQL data sources I'm ok as I can convert them ...

Refer to other SQL SELECT statements aliased as tables in FROM clause

I have a very large query that follows the format below: select ... from ( select field1, field2 from some_table ) table1, ( select field1, field3 from other_table ) table2 where ..... Is is possible for me to refer to one of the tables "defined" in the from clause, lets...

Is a GROUP BY on UNIQUE key calculates all the groups before applying LIMIT clause?

If I GROUP BY on a unique key, and apply a LIMIT clause to the query, will all the groups be calculated before the limit is applied? If I have hundred records in the table (each has a unique key), Will I have 100 records in the temporary table created (for the GROUP BY) before a LIMIT is applied? A case study why I need this: Take ...

SQL - Getting more data with a GROUP BY

Hoping someone can help me out with this. Lets say I have a table called incoming_data with 4 columns: primary_key_id serial_number counter selected_color I get data from a source that fills this table. The primary key has its identity turned on so it is not repeating. I will get duplicate serial numbers with different selected colo...

Preventing Sql column overflows

If I have a parametrized stored procedure which takes a varchar(10) value and converts it to an int, I currently have to ensure that the value is not greater than the varchar equivalent of the maximum int value. IF @Criteria <= '2147483647' SET @Id = CONVERT(int, @Criteria) My question: Is there a better way to prevent overflowing an...

Using SQL to determine word count stats of a text field

I've recently been working on some database search functionality and wanted to get some information like the average words per document (e.g. text field in the database). The only thing I have found so far (without processing in language of choice outside the DB) is: SELECT AVG(LENGTH(content) - LENGTH(REPLACE(content, ' ', '')) + 1) FR...

"CSS-like" fallback cascading data in SQL Server 2005

I'm trying to implement a price fallback system in SQL server. I'd like to have a set of increasingly specific prices (eg: by region, store, warehouse, etc.) for a product, that may or may not be defined, and be able to select the most specific prices (ie: the one with most parameters defined) in a report. For example, I might have the ...

How to Replace Multiple Characters in Access SQL?

I'm a novice at SQL, so hopefully someone can spell this out for me. I tried following the "Replace Multiple Strings in SQL Query" posting, but I got stuck. I'm trying to do the same thing as the originator of the above posting but with a different table and different fields. Let's say that the following field "ShiptoPlant" in table "...

Oracle in-line method to produce CSV for relation

I have the following tables: ALERT (ID,Name) 1 | Alert A 2 | Alert B ALERT_BRAND_XREF (ALERT_ID, BRAND_ID) 1 | 1 1 | 2 2 | 1 BRAND (ID, NAME) 1 | Brand A 2 | Brand B I am trying to write one statement to return a list of alerts with the applicable brands as a CSV list in one field. Desired results: Al...

What is the best way to handle this constraint in SQL Server 2005?

I have SMS based survey application which takes in a survey domain, and a answer. I've gotten requests for detailed DDL, so.... The database looks like this SurveyAnswer.Answer must be unique within all active Surveys for that SurveyDomain. In SQL terms, this should always return 0..1 rows: select * from survey s, surveyanswer sa wh...

Is there a good way to check rules against n columns?

Suppose you have a table RULES with 3 columns A, B, and C. As data enters the system, I want to know if any row of the RULES table matches my data with the condition that if the corresponding column in the RULES table is null, all data matches. The obvious SQL is: SELECT * FROM RULES WHERE (A = :a OR A IS NULL) AND (B = :b OR B IS NUL...

Asp.Net binding specific fields of sql query to Listview

I have a listview that has a few different controls. I need to bind specific fields of a query to certain parts of a control. The table contains a friendid and a firstname. I want to put the friendid at the end of a URL. I want to put the firstname in the text of a label. There will be multiple friends returned on the query. The listview...

sql and structs/class

Is there a way i can create a struct or class, fill it with data and dump/read it to and from my DBs? ...

using bulk insert to automatically populate sql table, then export to xml

Hi there, I'm doing a project at the moment which involves using bulk insert to fill an sql table with weather data. BULK INSERT TableWeather FROM 'C:\Program Files\EasyWeather\EasyWeather.dat' WITH (FIELDTERMINATOR = ',') This seems to work fine but I need to do this every fifteen minutes and also actually...

Does MS SQL Server's "between" include the range boundaries?

For instance can select foo from bar where foo between 5 and 10 select 5 and 10 or they are excluded from the range? ...

ADO.NET: How to get Return Value of a Stored Procedure

Probably an easy-to-answer question. I have this procedure: CREATE PROCEDURE [dbo].[AccountExists] @UserName nvarchar(16) AS IF EXISTS (SELECT Id FROM Account WHERE UserName=@UserName) SELECT 1 ELSE SELECT 0 When I have ADO.NET code that calls this procedure and does this: return Convert.ToBoolean(sproc.ExecuteScalar()); Either...