sql

Getting individual counts of a tables column after joining other tables

I'm having problems getting an accurate count of a column after joining others. When a column is joined I would still like to have a DISTINCT count of the table that it is being joined on. A restaurant has multiple meals, meals have multiple food groups, food groups have multiple ingredients. Through the restaurants id I want to be ab...

SQL: Select records where ALL joined records satisfy some condition

How can I write an SQL query that returns a record only if ALL of the associated records in a joined table satisfy some condition. For example, if A has many B, I want to SELECT * FROM A WHERE all related B's for a given A have B.some_val > value I know this is probably a pretty basic question, so thanks for any help. Also, if it makes...

DateTime.ParseExact format error

Hi I have used a textbox to store a date in DD-MM-YYYY format and since i use SQL server i used to get the value from the textbox and then place it in a variable of type DateTime dt DateTime dt = DateTime.ParseExact(TextBox10.Text,"MM-DD-YYYY",CultureInfo.InvariantCulture); I am getting format specification error, I went through many ...

Not sure how to go about joining these two tables.

I had trouble coming up with a title for this question because I'm not really sure how to do what I want, so I'm not sure if "joining" is the best terminology in this instance. I have two tables in my database, Plans and Assumptions that look like the following: Plans Table Plankey ass1 ass2 ass3 ass4 aplan 0 6 5 7 bplan...

Get percent of columns that completed by calculating null values

I have a table with a column that allows nulls. If the value is null it is incomplete. I want to calculate the percentage complete. Can this be done in MySQL through SQL or should I get the total entries and the total null entries and calculate the percentage on the server? Either way, I'm very confused on how I need to go about separ...

SQL Cursor/Population Question

So here's the setup. I have two tables: CREATE TABLE dbo.TmpFeesToRules1(Name varchar, LookupId int) CREATE TABLE dbo.TempFeesToRules2(FeeId int, Name varchar) I have a third table called 'Fee' in the database that's already created. I want to populate dbo.TmpFeesToRules1 'Name' field with the DISTINCT 'Name' from 'Fee'. Would I do th...

automating decompile / recompile in ms-access

my front end from time to time is getting larger. i am building new reports and forms. i know that remou suggests to decompile and compile every so often to make sure nothing is corrupt. can i automate this decompile/ recompile process? ...

Optimize Linq to SQL query, Group By multiple fields

My LINQ query contains the following Group By statement: Group p By Key = New With { _ .Latitude = p.Address.GeoLocations.FirstOrDefault(Function(g) New String() {"ADDRESS", "POINT"}.Contains(g.Granularity)).Latitude, _ .Longitude = p.Address.GeoLocations.FirstOrDefault(Function(g) New String() {"ADDRESS", "POINT"}.Contains(g.Granularit...

SQL Question: select cities distance without duplicates

I have a table with the columns: city, x, y I need to select the distance between all the cities without duplications the results should look like this city1,city2,distance but I shouldn't get city2,city1,distance ( since I already got the distance between those cities... what is the correct SQL query for that... thanks! ...

Select a specific column based on another column's value

I have a table like this ID | Type | Val0 | Val1 1 | 0 | A | NULL 2 | 1 | NULL | B I need to select Val0 when the type is 0, and Val1 when the type is 1, and ValN when type is N... How can I do that? ...

how to exclude rows that have duplicates in one field

Hello I have a very simple task, but I cannot find any solution. I have two tables, 'articles' and 'categories' My article table look like this: id | cat_id | title | content 1 1 Blah Content 1 2 1 Blah2 Content 2 3 2 Blah3 Content 3 My categories table look like this: id | title 1 Catego...

Copy many tables in MySQL

I want to copy many tables with similar names but different prefixes. I want the tables with the wp_ prefix to go into their corresponding tables with the shop_ prefix. In other words, I want to do something like this: insert into shop_wpsc_* select * from wp_wpsc_* How would you do this? ...

c# regex replace

Hi, I have a database containing Page ojects with html content. A lot of the rows in the db contain this content <p style="float: left; margin-right: 20px; height: 300px;"> <img src="...">More html ... </p> So I created a super simple regex replace: foreach (var page in db.Pages) { str...

creation of pie and bar charts in php website

HI. I wanted to know if we could create pie and bar charts based on info from the database on a php website. I want to grab some information from the database and show it as an image so better analysis. Is there anyway to do this? Also, If i am using fpdf to generate pdfs from php and mysql, can i also design pie charts in that pdf and ...

Possible to simulate the mySQL functionality ON DUPLICATE KEY UPDATE with SQL Server

I am using SQL Server 2008, and would like to be able to take advantage of something like mySQL's ON DUPLICATE KEY UPDATE clause for INSERT statements Current legacy code does a delete and subsequent insert that is running into concurrency issues with duplicate key inserts from separate threads: Here is the error I see in my production...

#region functionality in SSMS 2008

Using Sql Server 2008, is there any functionality similar to #region in Visual Studio? I'm aware that nodes appear to allow collapsing of SQL segments, but as far as I know, this is based on the syntax of the SQL statement. While that is close to what I'm after, I'm wondering if there is a way to define a section of code, regardless ...

Multiple return types from NHibernate.

Earlier I asked this question: http://stackoverflow.com/questions/3339477/loop-in-mysql-or-alternative/3339494#3339494 I got a great answer and it does work. Now in my application I am using NHibernate(in C# .NET 3.5) as the DAL. I dont really know how to deal with multiple return types, so I tried the following: var session = NHibe...

Help me with this SQL Query...

I've got a SQL Server CE 3.5 table (Transactions) with the following Schema: ID Transaction_Date Category Description Amount Query: SELECT Transaction_Date, SUM(Amount) FROM Transactions GROUP BY Transaction_Date; I'm trying to do a SUM(Amount) and group by transaction_date just so I can get the total amount for each day b...

Updating table based on 2 tables with RowID is as commonality

Hi there I have a table like this: RowID; ListDescription1; ListNormalisedDescription1; 1 XXXX YYYY NULL 2 ZZZZZ NULL I made a complex transformation/normalisation (removing spaces, replacing space and split) and manage to make the same data turning into: RowID; NormalisedItemDescrption1; 1 XXXX 1 ...

MySQL Query - SELECT (average of a category) AS "CATEGORY AVERAGE"

Objective: When user browses to a particular seller, then display his average along with the average of sellers from similar category for easy comparison. Example Data: Seller | Category | Qty | Sales -------------------------------------------- Harry | Mango | 100 | 50000 John | Apple | 75 | 50500 Max | Ma...