query

Is there any reason not to join Foreign Key to Foreign Key?

I have the following tables: Financial: PK_FinancialID FK_SchoolID School: PK_SchoolID Class: PK_ClassID FK_SchoolID ClassName Both Class and Financial have Foreign Key relationships to School. I want to make a query that would show all classes that are related to Financial rows that meet certain criteria. Initially I think...

Querying Parent-child Relationships Efficiently

Assuming you have the following database table: create table Names ( Id INT IDENTITY NOT NULL, Name NVARCHAR(100) not null, ParentNameId INT null, primary key (Id) ) create index IX_Name on Names (Name) alter table Names add constraint FK_NameNames foreign key (ParentNameId) references Names This allows the ...

mysql - query three tables

I have a relational database with three tables. The first containts id's that relate to the second. The second contains id's that relate to the third. The third contains the results I am after. Is it possible with a single query to query an id in the first table which gives all results from the third table that relate to it? Sorry I am...

Subselects in Oracle Query

Hi, could anyone tell me if it makes a difference to Oracle 10g whether I use: SELECT col1 FROM myTable WHERE col2 = 'someval' AND col3 = "someotherval" or SELECT col1 FROM SELECT col1, col2, col3 FROM ( SELECT * FROM myTable ) WHERE col2 = 'someval' ) WHERE col3 = "someotherval" According to the explain plan, the...

SELECT (* - some_columns) FROM TABLE in SQL

Hi, I have a table with many columns among which I'd like to omit a few alone in my 'select' query. Something like select (* - columns_to_be_omitted) from myTable. Is there a way to do this, other than listing all the other columns in the query? This is a one-time manual query, so I'm not that concerned about performance. Ease of use ...

MySQL Join Optimization

Can you please help me optimize this query. I use this query to get a list of friends along with their details and their status. It takes about 0.08 secs to process this on a Athlon X2 6000 I cant use materizlized view as well because this is frequently changing. SELECT p.userid, p.firstname, p.lastname, p.gender, p.dob, x.relationship...

Mysql: Optimizing Selecting rows from multiple ranges (using indexes?)

My table (projects): id, lft, rgt 1, 1, 6 2, 2, 3 3, 4, 5 4, 7, 10 5, 8, 9 6, 11, 12 7, 13, 14 As you may have noticed, this is hierarchical data using the nested set model http://tr.im/EO3G. Tree pretty-printed: 1 2 3 4 5 6 7 I want to select all sub projects under project 1 and 4. I can do this with: SELECT p.id FROM projects...

Automatic quoting MySQL queries doubts in Zend Framework

I've got few doubts regarding quoting mysql queries in Zend framework. Though this question has helped me a bit but few things are still confusing: 1) $table is Zend_Db_Table. Trying to fetch a row from the table. $where[] = $db->quoteInto('id = ?', $id); $where[] = $db->quoteInto('user_id = ?', $user_id); $row = $table->fetchRow($wher...

Check whether two dates contain a given month

My problem is simple... or may be not. I've got a table that contains two dates: StartDate EndDate And I have a constant which is a month. For example: DECLARE @MonthCode AS INT SELECT @MonthCode = 11 /* NOVEMBER */ I need a SINGLE QUERY to find all records whose StartDate and EndDate includes the given month. For example: /* Ca...

Select Top 5 records of every employee in SQL Server

I have the following issue, I have this query that select the latest 5 records created for an employee: SELECT TOP 5 p.value, p.record_date AS FECHA FROM employee_loan_movements p WHERE p.employee_code = '1' AND p.record_date <= '2009-11-11' AND p.movement_type = 1 AND p.value > 0 ORDER BY p.record...

Help with insert trigger

hey i have a temp table (question_desc, ans1, ans2, ans3, ans4, correct_ans, marks) with say 10 entries from this table i have to insert values in two other tables questions (q_id(auto-generated), que_desc, marks) answers (ans_id(auto_generated), q_id(FK), ans_desc, istrue) for each insert in question table there should be four inse...

MySQL/PHP update query with dates not updating

Bit of a strange problem here... I've got an update query that isn't working, and I can't for the life of me work out why! My table has two three fields - 'id' (int, auto increment), 'date' (date), and 'amountraised' (decimal). Part of the app I'm developing calculates the fundraising total each week made by a charity bookstall. The 'd...

is it okay to store double and date in varchar on mysql

If it is how can i query on double and date, I mean there has to be a way to cast them back and them sort them for example. ...

Basic SQL join question. Can you help me improve my skillset?

Ok.. So I'm trying to improve my SQL skills and have a question. Here is a screen shot of the schema. (http://img509.imageshack.us/img509/97/screenhunter02nov121946.gif) Alright so I'm selecting a bunch of Report Bundles and other rows from a table you can see. I've got these two tables joining together correctly and displaying what s...

Linqy no matchy.

Hi All Maybe it's something I'm doing wrong. I'm just learning Linq because I'm bored. And so far so good. I made a little program and it basically just outputs all matches (foreach) into a label control. usage: enter text into textbox, click button. program lets you select a file to match the textbox value against and returns matches ...

SQL Query Shortcuts

What are some cool SQL shorthands that you know of? For example, something I learned today is you can specify to group by an index: SELECT col1, col2 FROM table GROUP BY 2 This will group by col2 ...

Using mysql_query and mysql_fetch_array

I am trying to find out which is the proper way to fetch data from my database. Either way works, but what's the difference; an in-depth explanation? $sql = mysql_query("SELECT * FROM _$setprofile"); while($row = mysql_fetch_array($sql)) { $username = $row['user']; $password = $row['pass']; echo "$username:$password"; } ve...

Removing duplicate field entries in SQL

Is there anyway I can erase all the duplicate entries from a certain table (users)? Here is a sample of the type of entries I have. I must say the table users consists of 3 fields, ID, user, and pass. mysql_query("DELETE FROM users WHERE ???") or die(mysql_error()); randomtest randomtest randomtest nextfile baby randomtest dog anothert...

Why would a query be faster in SQL Server 2005 just because it's in a view?

We have a (large) SELECT query, that can take ~30 seconds to run. I am told that when placed in a view, it takes less than 5 seconds to run. My assumption is that SQL Server caches query plans for queries that don't change, so why the massive improvement in performance here? Just to be clear, this really is just a case of taking somet...

SQL Table Size And Query Performance

We have a number of items coming in from a web service; each item containing an unknown number of properties. We are storing them in a database with the following Schema. Items - ItemID - ItemName Properties - PropertyID - PropertyName - PropertyValue - PropertyValueType - TransmitTime - ItemID [fk] The properties tabl...