sql

Does the order of fields in a WHERE clause affect performance in MySQL?

I have two indexed fields in a table - type and userid (individual indexes, not a composite). types field values are very limited (let's say it is only 0 or 1), so 50% of table records have the same type. userid values, on the other hand, come from a much larger set, so the amount of records with the same userid is small. Will any of ...

SQL DB Question

Question about SQL View. Trying to develop a view from two tables. The two tables have same Primary Keys, execpt the 1st table has all of them, the 2nd has some, but not all. When I INNER Join them, I get a recordset but its not complete, because the 2nd table doesnt have all the records in it. Is there a way in my view to write logic st...

SQL query question

Here is the table "Answers". I need to get count of QuestionNo that are "R", "W", "" for each section for given AcademicYear and TestNo. What is the best SQL query? AcademicYear StudentID TestNo Section QuestionNo Answer 2010-2011 1 1 2 1 R 2010-2011 1 1 2 2 W...

Foregin key constraint error but shouldn't be occuring

I have a list table with 5 entries in it, the first column in the identity column (PK), within a parent table is a column that is related to this list table by ID (FK). I am using C# in a web app to run an ExecuteScalar with a stored procedure to insert the items into the parent table. This is within a SQL database LIST TABLE: ID INT ...

sql select query name -value pair

My table looks like this: ID NAME VALUE 1 home San Jose 1 visitor New Jersey 2 home Chicago 2 visitor Los Angeles 3 home Tampa Bay 3 visitor Pittsburgh 4 home Montreal 4 visitor N.Y. Islanders 5 home Montreal 5 visitor N.Y. Islanders 6 home Carolina 6 vi...

Using OUTPUT Parameters within Dynamic SQL within Stored Procedures- Possible?

I have a SP that I have created to check for validations and return an OUTPUT Parameter of 0 (No Error) or 1 (Error). However, I have to run this SP within Dynamic SQL since it will be ran through a loop of different data. Can I pull the OUTPUT from the SP through the EXEC sp_executesql @SQL? I can not post the actual code, but I can g...

Using an if statment into a cursor declaration.

Hi. Im using a cursor to go over some data but I need to declare diferents sql statments according to a parameter. The problem seems to be that Im no able to use if statmets into this declaratios: DECLARE CURSOR c IS SELECT * FROM TRAFICO IF TipoConsulta = 'mes' then BEGIN WHERE TO_CHAR(FECHA...

RoR: Log SQL Queries in Production Log

Hi All, How can we enabled SQL query logging in production environment? Thanks, Imran ...

SQL Server 2005 backup and restore

Hi folks, I have two backup files 1) is named 'backup.sql' with a bunch of SQL defining TABLES 2) is named 'backup' with a bunch of encoded data, which I believe are the ROWS I need to restore these TABLES + ROWS, but all I am able to figure out is how to restore the tables. Any tips on dealing with these files? It's the first tim...

SQL query LEFT JOIN issue (MySQL)

I have 2 tables SCHOOLS (ID, SCHOOL_NAME, CITY_ID) STUDENTS (ID, STUDENT_NAME, SCHOOL_ID). I want to list schools in a particular city along with the student count (School Name| Student Count) One way to do this is to have co-related subquery - select sh.school_name, (select count(*) from student where s...

Many-to-Many self-referencing select sql query

I have a simple table (MySQL) set up for relationships between two users. [User1Id] [User2Id] 10 15 14 10 10 13 But, I can't figure out how to do a SELECT in such a way that I can grab the opposite UserId of the user being requested. So, if I wanted to get all relations for the user with an ID of 10, it wo...

Using INNER JOIN with MySQL on selecting multiple tables?

Hi all, I have a site where a specific set of data is collected. This is a travel agency website. It is neccesary to determine whether or not an accommodation is still bookable. When I don't select any searchfilters (like destination, classification, facilities etc) I get a working query. The looks like: SELECT `accommodation` . * FROM...

Using date of type char in where clause

I have a SQL Server database table with a char column named "DATE" (I know, really bad, but I didn't create the database) that has dates stored in this format as strings: YYMMDD. I need to return records between these dates, so treat them as actual dates and I've tried every combination I know but still get errors. Any help is much appre...

Get count of records affected by INSERT or UPDATE in Postgres

My database driver for Postgres 8/9 does not return a count of records affected when executing INSERT or UPDATE. Postgres offers the non-standard syntax "RETURNING" which seems like a good workaround. But what might be the syntax? The example returns the ID of a record, but I need a count. INSERT INTO distributors (did, dname) VALU...

Oracle Synonyms issue

My Scenario: Schema name: schema1 Package name: pkg_system procedure name: proc1 Now I am trying to create a synonyms for my proc1 as below CREATE PUBLIC SYNONYM call_proc FOR schema1.pkg_system.proc1; ...but it gave me syntax error. ORA-00933: SQL command not properly ended I changed the code as below: CREATE PUBLIC SYNONYM c...

sql help with association table update

update: This is more of a maintenance issue than the normal case. I have the following table: CourseId StudentId --------------------- 1 1 1 2 2 1 CourseId and StudentId are composite primary key. Let's say I wanted to update where courseid=1 to courseid=2 for some reason. This will cause a primary k...

SQL Search Problem With New SearchContent Function

Hi everyone, I have a question about searching in SQL. We have a piece of code below responsible for searching a set of content within our site. When we wrote the StaticContent algorithm, it worked fine. After adding ContribContent (2nd and 4th par) it does not appear to work. Do you have any idea why? Thanks so much! ALTER PROCEDURE [...

Is Join or Where clause approach more efficient in restricting result set?

I need to restrict a result set for a SELECT statement based upon Col1 have 1-to-many potential values. For example, I want to return all rows where Col1 equals a value of 1, 2, and 3. So far I have two different approaches to restricting the result set: Approach #1 Inner Join Table1 On (Table2.ColA=Table1.ColA) And (Col1=1 And Col1=2...

Many-to-many relations in database design

Hi Everyone. I'm building a database that makes use of lots of many-to-many relations. For example, I have part items and machine items where each part goes into many machines and each machine has many parts. The purpose of this database is to be able to input a list of parts and get back a list of machines composed of only parts in t...

Improving speed of queries/processing of RegExp function with two database tables

I have a program with a function that retrieves a relatively short text string from one SQL database table ("html"), runs a number of regular expression queries on it, and then stores the output in another database table ("profiles"). "Html" has about 8 million records and "profiles" stores only the results. It is currently very slow -...