I am writing a web application, that is US specific, so the format that other countries use for postal codes are not important. I have a list of us zip codes that i am trying to load into a database table that includes the 5 digit us zip code latitude longitude usps classification code state code city the zip code is the primary ke...
After watching, the latest RailsCasts with the include and joins information, I know I can do what I am doing in a much more efficient way. I have a very simple User model and a Status model. The User belongs to Status and the Status has many Users. I need to count how many users have a specific kind of status, this creates a new SQL cou...
I'm running queries that look something like this: INSERT INTO foo (...) VALUES (...) RETURNING ROWID INTO :bind_var SELECT ... FROM foo WHERE ROWID = :bind_var Essentially, I'm inserting a row and getting its ROWID, then doing a select against that ROWID to get data back from that record. Very occasionally though, the ROWID won't b...
How do I parse a dash-delimited value in a SQL table and use the different parsed-out values to do lookups in other tables? Say I have users.nationality, which is a varchar like "41-33-11". I have the country, city and state tables keyed to those ids, 41 ("Buffalo"), 33 ("NY") and 11 ("USA"). SELECT users.name, users.email, users.nati...
don't know if this is possible.. I'm using sqlite3 schema: CREATE TABLE docs (id integer primary key, name string); CREATE TABLE revs (id integer primary key, doc_id integer, number integer); I want to select every job joined with only one of its revisions, the one with the highest number. How can I achieve this? Right now I'm doing a ...
In my database there are four databases: Teachers, Students, Parents and Addresses. Teachers, students, parents can have any number of addresses (zero or more). I would like to use foreign keys to link between teachers and their addresses (and between students and their addresses etc). Since records in Addresses can originate in eith...
In the world of oracle, I have the impression that views based on other views are considered to be bad practice. I myself have complained about this when the trying to solve performance issues and the nesting seemed excessive and hid away unneeded complexity in the underlying views. Now I find myself in the situation of thinking that it ...
Provided that the tables could essentially be inner joined, since the where clause excludes all records that don't match, just exactly how bad is it to use the first of the following 2 query statement syntax styles: SELECT {COLUMN LIST} FROM TABLE1 t1, TABLE2 t2, TABLE3 t3, TABLE4 t4 (etc) WHERE t1.uid = t2.foreignid AND t2.uid = t3.for...
What's the best way to select only those rows from the table that have been created in last 7 days? There are dozens of time and date functions in MySQL and I'm a little bit confused about what's the easiest way to do this. For the sake of this question, assume that you have a table called "my_table" and it contains a row "created_at" ...
I Want to add Options to Select Box using jquery, but my options comes from MYSQL databse table here is method what i want: function add_inst(va) { alert(va); } <select id="test"> </select> I want to select values from MySQL table with match value of va, then add these result as options to select id="test". For example if i pas...
I have fairly long and complex SQL query that is run against PostgreSQL 8.3. Part of the query involves filtering on a range of dates ending with today, like this: where ... and sp1.price_date between current_date::date - '1 year'::interval and current_date::date and sp4.price_date between current_date::date - '2 weeks'::interval an...
In MySQL, I store categories this way: categories: - category_id - category_name - parent_category_id What would be the most efficient way to generate the trail / breadcrumb for a given category_id? For example breadcrumbs(category_id): General > Sub 1 > Sub 2 There could be in theories unlimited levels. I'm using php. UPDATE: I saw...
I'm trying to do something in a query that I've never done before. it probably requires variables, but i've never done that, and I'm not sure that it does. What I want is to get a list of sales, grouped first by affiliate, then by it's month. I can do that, but here's the twist... I don't want the month, but month 1, month 2, month ...
The INSERT statement conflicted with the CHECK constraint "ck_str_member_no". The conflict occurred in database "C:\DOCUMENTS AND SETTINGS\KARTHIKEYAN\DESKTOP\KOK\DB\INFT3009_ASS1_C3104855.MDF", table "dbo.Members", column 'str_member_no'. The statement has been terminated. I am using .MDF file in my visual studio 2008 Express. How do ...
Hi all, I've got a .net 3.5 website that calls thousands of different stored procs using Microsoft.Practices.EnterpriseLibrary. We have been getting alot of timeouts and after some playing and testing the use of (nolock) on the end of the table join in stored procs works real well and reduces the timeouts. I want to now do this to al...
Duplicate of: http://stackoverflow.com/questions/1496136/how-to-find-out-size-of-the-individual-databases-in-the-sql-server-2005 how to find out log size of each of the database in sql server 2005? ...
Say I have these tables with field names underneath. DailyLeaveLedger dldEmployeeID dldLeaveDate InvoiceHeader invEmployeeID invWeekEnding InvNumberWeeksCovered So I want a query selects leave days where an invoice covers that leave ie invWeekEnding 02 Oct and InvNumberWeeksCovered = 1 then any leave in the range 26 Sep to 02 Oct is ...
Using SQL Server 2000, Date Column Datatype is varchar... In my table date column values are like: 2009/01/31 2009/02/00 2009/02/01.... 2009/03/31 2009/04/00 2009/04/01.... so on..., I want to display 2009/01/31 instead of 2009/02/00, If date is 2009/02/00 it should display previous date. How to make a query for this condition? ...
I am using mysql5.1 and wrote trigger after update.am using same table for insert and updae. table contains field .id,itemid,value1 all are integer.. my trigger is: CREATE DEFINER = 'root'@'%' TRIGGER `trig_same` AFTER UPDATE ON `vote` FOR EACH ROW BEGIN insert into vote values(10,1,100); END| ...but when i excute update com...
I have a data source on a Windows machine that supports an ODBC connection. I want to programmatically copy that entire data source to a MySql database every hour. Is there something faster than doing a select * from and then doing an insert for each row? ...