sql

INNER JOINs with where on the joined table

Hi, Let's say we have SELECT * FROM A INNER JOIN B ON [....] Assuming A has 2 rows and B contains 1M rows including 2 rows linked to A: B will be scanned only once with "actual # of rows" of 2 right? If I add a WHERE on table B: SELECT * FROM A INNER JOIN B ON [....] WHERE B.Xyz > 10 The WHERE will actually be executed before the...

do i need a separate table for nvarchar(max) descriptions

In one of my very previous company we used to have a separate table that we stored long descriptions on a text type column. I think this was done because of the limitations that come with text type. Im now designing the tables for the existing application that I am working on and this question comes to my mind. I am resonating towards s...

Optional Parameters In Stored Procs - CASE vs. OR

Is there any difference Performance wise between these filter methods? Method 1: WHERE (@Col1 IS NULL OR t.column = @Col1) Method 2: WHERE 1 = case when @col1 is null then 1 else case when col1 = @col1 then 1 else 0 end end ...

phpMyAdmin and latin1_swedish_ci

I create new database with utf8_general_ci collation, create test table also with utf8_general_ci collation, and then i click on my database(structure) and this is what i get: Why is that "latin1_swedish_ci" showing up? Edit: Ok how to change this, it is in phpmyadmin in "Variables" tab, under "Server variables and settings": Thi...

Determine Which Part(s) of WHERE Statement Failed

Let's say I have a SQL statement like this that checks a user login: SELECT * FROM users WHERE username='[email protected]', password='abc123', expire_date>=NOW(); Is there a way in SQL to determine specifically which WHERE conditions fail, without having to separate each condition into its own query and test individually? In this sp...

SQL: COUNT() with items appearing multiple times in one-to-many relationship? Alternative to GROUP BY?

I have a query listing product categories, by supercategory > category. The query counts the number of products in each category (COUNT(ptc.catid)). Howewer: the query doesn't let a category appear twice (a category can be linked to multiple supercategories). How can this be re-written so categories (c.title) can be listed under multip...

MS ACCESS: How can i count distinct value using access query?

here is the current complex query given below. SELECT DISTINCT Evaluation.ETCode, Training.TTitle, Training.Tcomponent, Training.TImpliment_Partner, Training.TVenue, Training.TStartDate, Training.TEndDate, Evaluation.EDate, Answer.QCode, Answer.Answer, Count(Answer.Answer) AS [Count], Questions.SL, Questions.Question FROM ((Evaluation I...

Selecting a single row in MySQL

I have a table that has nine columns in it. one of them is the primary key. How can I select a single row, and all of the columns in it, by the primary key or column 8 or 4? I'm using PHP and MySQL ...

Find what table a specific index belongs to

If I have the name of an index, what query can I use to find what table the index belongs to? ...

Performing search on SQL Server 2008 database

We have built an ASP.NET application (with C#.net language) and hosted on Windows Server 2003 Operating System. The database is SQL Server 2008. Now we need to do a search involving 4 tables and one of the column of table is varchar (2400) and the other columns are of normal lengths (E.g. Varchar(50) etc.). This search gets fired when...

Is there an open source .Net SQL Editor component available?

I would like a text editor component that color codes SQL Statements like you would see in SQL Server Management Studio. I want to drop it on a form so I can write sql statements and send the queries to my database. It is not necessary to have intellisense but it would be a plus. Is there something like that out there? ...

Tricky SQL statement over 3 tables

I have 3 different transaction tables, which look very similar, but have slight differences. This comes from the fact that there are 3 different transaction types; depending on the transaction types the columns change, so to get them in 3NF I need to have them in separate tables (right?). As an example: t1: date,user,amount t2: date,us...

Oracle regex to list unique characters difference between two strings

In Oracle 10g, I'd like to create a regular expression to list the characters that are different between two strings. Here is the reason : I have a table with a field that contains sometimes Unicode characters that are not in the french language. I am able to list the rows containing these non standards characters to make a future c...

How do I create a simple table in SQL Server 2005?

I need to "use a database" and create a table with the following attributes in SQL Server 2005: Table name is "quiz_mailing_list" Fields are: id (typical auto-increment primary key) email (varchar size 256) optIn (boolean or tinyint size 1) referringEmail (varchar size 256) Can someone give me the command to "use the database" and the ...

Basic SQL Injections?

I was told in a previous question that my query is prone to SQL injections. get_stats = mysql_query("SELECT * FROM visitors WHERE site='$_GET[site]' AND date BETWEEN '$start_date' AND '$end_date' "); What would be the easiest way to approac...

Wordpress and MySQL Collations

Hi Folks. I'm just having this trouble with Wordpress: I use to have a Blog with and older version of it and of MySQL, and when I export the SQL DB Creation Scripts, they exported with the latin1_swedish_ci Collation. Know I'm trying to rebuild the blog but it shows a lot of strange characters like: Imaginaré Creativitá When It mu...

What is the WITH statement doing in this example? I'm trying to randomly generate data.

INSERT INTO files (fileUID, filename) WITH fileUIDS(fileUID) AS ( VALUES(1) UNION ALL SELECT fileUID+1 FROM fileUIDS WHERE fileUID < 1000 ) SELECT fileUID, TRANSLATE ( CHAR(BIGINT(RAND() * 10000000000 )), 'abcdefgHij', '1234567890' ) FROM fileUIDS; ...

Can you create nested WITH clauses for Common Table Expressions?

WITH y AS ( WITH x AS ( SELECT * FROM MyTable ) SELECT * FROM x ) SELECT * FROM y Does something like this work? I tried it earlier but I couldn't get it to work. ...

how to save current local time in database

I want to save my countries local time(new zealand) in my database. I am using php but i dont know how to do it. Please guide me out. ...

Creating a modest Auto Generating SQL change script ?

I'm aware that auto generating SQL change tools exist, however I would like to create a very lightweight tool. Currently I manually log any stored procedures or table changes (or new stored proc or tables), while this is very effective during publish /release its time consuming (hence while these tools exist) I just want to automate th...