sql

How do you handle a user notification popup "ribbon" - similar to SO's?

In my asp.net MVC app, I'm planning a user notification system similar to SO's. Here's the table structure I'm thinking of: Message table -------------------------------- PK MsgId BigInt FK UserId UniqueIdentifier Body nvarchar(200) IsRead TinyInt DateSent DateTime First, does this DB structure look ok? i...

What field type?

I know maybe this is a stupid question, But i suck at sql But what field type should you have on a field that can only have numbers 1 to 5? 5 is max and 1 is min. It's currently int(11) not unsigned I heard having the wrong type can hurt performace Edit: the database is InnoDB. RDBMS is MySQL ...

How does one pronounce COALESCE?

I've searched around the net. I have never heard anyone say this aloud and would feel foolish saying it funny. I'm thinking ko - al - es ...

How to sort in SQL, ignoring articles ('the", "a', "an" etc)

This comes up a lot, and I can see it's come up on StackOverflow for XSLT, Ruby and Drupal but I don't see it specifically for SQL. So the question is, how do you sort titles correctly when they begin with "The", "A", or "An"? One way is simply to TRIM() those strings: ORDER BY TRIM( LEADING 'a ' FROM TRIM( LEADING 'an ' FR...

Best practices for snapshotting SQL database rows.

I have two tables X and Y. This is back end of a web based CRUD application. Let's say that rows in Y have fields a, b and c. When a row in X is created, it has to link to a snapshot of the values in a specific row of Y. The row in Y may change later but the row in X should still have the values of a, b and c at the time of creation. ...

Performance of ON Clause over WHERE Clause

Can anyone please tell me Which of the following queries will have better performance? SELECT * FROM [TABLE1] T1 INNER JOIN [TABLE2] T2 ON T2.[FK_ID] = T1.[PK_ID] WHERE T2.[ACTIVE] = 1; SELECT * FROM [TABLE1] T1 INNER JOIN [TABLE2] T2 ON T2.[FK_ID] = T1.[PK_ID] AND ...

SQL Query Group By Help

Hi, I have the following data: cust subject date Cust1 Subject1 2010-12-31 21:59:59.000 Cust1 Subject1 2010-12-31 21:59:59.000 Cust4 Subject3 2011-02-27 21:59:59.000 Cust5 Subject1 2010-10-31 21:59:59.000 Cust5 Subject2 2010-10-31 21:59:59.000 Cust6 Subject2 2010-10-31 21:59:59.000 Cust6 Subject2 2010-12-31 21:59:59.000 I need to a...

Use of Stored procedure

Why to use stored procedure in .net and how to use it? ...

SQL Server Startup Parameters

Dear Friends How can i retrive the SQL Server startup parameter using SQL Query?? Plz help me Thanks in advance ...

Need SQL to get a record with two corresponding entries

Hi guys, I have a table which holds contact details against corresponding account numbers: Contacts_Accounts Table contact id | account ID ======================= 1 | 12 1 | 13 1 | 14 2 | 12 2 | 13 3 | 12 how can I make a sql query that returns all entries that have BOTH account ...

how to implement "contains" function in Mysql4.x ?

i have a keywords table,and i want to check a String contains keyword or not. String str="aaabbbccc"; TableName:keywordTable field:id keyword ------------------- 1 ee 2 bbb 3 xx .. ------------------ i run this sql (in mysql 4.1): select * from `keywordTable` where contains(`keyword`,"aaabbbccc"); return a syntax error how to imp...

Directed acyclic graph: find all paths from a specific node

How do I find all the paths from a specific node (node 36 in the example)? Let's say we have two tables: CATEGORIES CATEG_PARENTS id idc | idparent -- ---------------- 1 2 1 2 2 20 5 5 2 8 8 5 20 20 1 22 ...

SQL Server - Setting Multiple Values in a Case Statement?

Hi, I have a SQL Select statement where I need to return certain values depending on a condition. I need to return multiple values each time, but my understanding of the Case statement is that you can only return a single value for each case. I'm getting around this by using UNION statements at the moment, but it all looks a bit cum...

SQL query to get the bookmakers with the highest odds.

Hello, I try to solve an sql problem but without success and I thought that you could give me a good answer. My table looks like this: league_id|style|bookmaker|hometeam|awayteam|odd1|oddX|odd2| ----------------------------------------------------------- premier |1X2 |bet365 |arsenal |chelsea |2.50|3.20|2.30| -------------------...

sql: compare 2 strings without considering accents and other stuff îăţş = iats (<-should be equal)

I need to compare 2 strings in sql so that the word "iast" would be equal to "îăşţ" anybody knows how to do this ? ...

Help with SQL Query

Hello guys, consider the following example. I have to select all the records from table1 which exist in table2 also + all the records from table2 which don't exist in table1 but exist in table2 and have IsActive=1 and status not null. I initially tried it with a join but how to do the later part where I have to select the records whic...

i need to question my database about a word received from another aplication, my tables are related on a many to many relashionship

i have 2 tables one market, another brand , so when a client search for a brand i need to return all the markets where he can find that brand. ...

SQL Azure: What will happen if size of my SQL Azure get 5GB?

Hi all, I need to know what will happen when a database in SQL Azure exeeds its size. For example I defined a 1GB database and this is going over its size limit. Will it change to a 5GB database? ...

Stored procedure SQL SELECT statement issue

Hello everyone, I am using SQL Server 2008 Enterprise on Windows Server 2008 Enterprise. In a stored procedure, we can execute a SELECT statement directly. And it could also be executed in this new way, I am wondering which method is better, and why? New method, declare @teststatement varchar(500) set @teststatement = 'SELECT * from ...

How to get the time in hours between these two dates

Hi Guys, I have one table in that they are four coumns like startdate varchar(15), enddate varchar(15), starttime varchar(15), endtime varchar(15) Here I need to get the time in hours between these two dates (ex: 6/7/2010,6/12/2010) if i pass these days as inputs regards, ravi ...