This is a simplified version of a query we are running where we need to find all rows in the main parent table where the child rows match. The query below returns no results when one of the child tables is empty. The main table has two child tables: CREATE TABLE main (id INT PRIMARY KEY, name VARCHAR(8)); CREATE TABLE child1(id INT P...
Say I have this query: SELECT bugs.id, bug_color.name FROM bugs, bug_color WHERE bugs.id = 1 AND bugs.id = bug_color.id Why would I use a join? And what would it look like? ...
Hi, I'm considering writing some unit tests for my Tsql stored procedures, I have two concerns: 1) I will have to write a lot of SQL to create test fixtures (test data prepared in _setup procedures) 2) I will have to "re-write" my query in the test procedure to obtain the results to compare against the results from the stored procedure...
I'm trying to move a primary key constraint to a different column in oracle. I tried this: ALTER TABLE MY_TABLE DROP CONSTRAINT c_name; ALTER TABLE MY_TABLE ADD CONSTRAINT c_name PRIMARY KEY ( "COLUMN_NAME" ) ENABLE; This fails on the add constraint with an error saying the the constraint already exists even though i just dropped i...
I have the following tables: tags id tag_name examples id category heading examples_tags id tag_id example_id How can I retrieve the number of examples under each tag? (a bit like stackoverflow actually :)) I also want an additional condition of the type: examples.category = "english examples" This is how I started ... SELECT t...
I am using the nested set model to store a large hierarchy of data in a local SQLite database on an iPhone. I read the MySQL tech article from their web site on how to do this, but one of the queries they suggest (and that I need) doesn't appear to work with SQLite and I'm not sure how to get around it. SELECT node.name, (COUNT(parent.n...
I have three SELECT statements that each return a total, 'New Cases', 'Closes Cases', 'Existing Cases'. How do I combine these so they are returned in one resultset. ie I need a table returned with 3 fields, 'New Cases', 'Closes Cases' and 'Existing Cases' each with one total SELECT COUNT(CaseID) AS 'New Cases' FROM dbo.Cl...
Originally posted on the WordPress forums but no-one answered... so I'll try my luck here... Hi all, I'm trying to learn to write a WordPress plugin by setting myself a goal of witting a user generated glossary plugin after I asked people on Twitter what would be useful (well I may as well use my learning experience to be useful for mo...
I have an SQL 2008 server running three instances. Each instance is assigned a unique IP address and listens on port 1433. Only TCPIP is enabled. All of my ASP.Net applications connect successfully using the IP address, with a connection string similar to: User ID=SQLUser;Password=userpass;Database=TestDB;Data Source=sqlserver My AS...
I have some relatively complex sqlite queries running in my iPhone app, and some are taking way too much time (upwards of 15 seconds). There are only 430 or so records in the database. One thing I've noticed is that opening a new database connection (which I only do once) and stepping through query results (with sqlite3_step()) causes sq...
I'm working on a data warehouse project and would like to know how to (preferably in a Derived Column component in a Data flow) strip the date piece off of a SQL datetime record. Once I have the datetime converted to just a time I am going to do a lookup on the time to find the related time record in a time dimension table. Can someone...
When inserting a guid as a primary key does the db server go through every single row to make sure there's no duplicate or does it just assume that a duplicate is not possible or very unlikely? with int the db server can simply increment the key but not the case with GUIDs ...
How do I populate a int column, currently empty, with random numbers with no duplicates? ...
i have a few columns that i know won't be used initially. is it a good practice to add columns only when needed or just leave those extra columns there? is it just a little more space in the header or every row? ...
If I have a joining table with two columns, TeamA and TeamB, how can I ensure that each pair is unique? Obviously I can put a unique composite index on these columns but that will only ensure uniqueness in the order A,B but not B,A correct? TeamA | TeamB ------------- red | blue pink | blue blue | red As you can see, Red vs. Blue...
Hello everyone, I have two tables, and they are using different collations. It is not allowed to concatenate columns from tables with different collations, for example the following SQL is not allowed, select table1column1 + table2column2 from ... My question is, how to change the collation of a table without destroying the data of t...
I want to make a geographical search using US city and state or zip-code and corresponding results will be viewed. In the search we have to mention the radius distance. For example: i would like to search between 25 miles around California. To do this, what should i do? is there any database containing us city,state,zip, latitude, long...
I'm currently running some SQL which uses an IN expression to match against multiple IDs. However, I would ideally like to be able to match up certain IDs with others so they must appear together to return a result. Here's an example: Edit: The IDs I'm matching are a part of a many-to-many relationship. The structure is like this: Arti...
In my User model, I want to search for whether a user has multiple accounts (aka 'multis'). Users have sessions; sessions are customized to set creator_id = ID of first user the session was logged in as, and updater_id = ID of last user the session was logged in as (Both columns are indexed.) If I detect that the two are different...
Is there a SQL command that will list all the tables in a database and which is provider independent (works on MSSQLServer, Oracle, MySQL)? ...