Is email address a bad candidate for primary when compared to auto incrementing numbers. Our web application needs the email address to be unique in the system. So, I thought of using email address as primary key. But, my colleague suggests that string comparison will be slower to integer comparison. Is it a valid reason to not use ema...
Hi guys, I am trying to make a photo album system in php and mysql. I have this code for list all photo albums: SELECT albums.*, count(pictures.id) AS countpic FROM albums LEFT JOIN pictures ON (albums.id=pictures.album_id) GROUP BY albums.id ORDER BY albums.date DESC"); I want to list title of photo albums with photo thumbnails ...
Hi, I want to have list of all SPs from my database that fulfill some conditions, say all those SPs that have StudentId in them as i want to update those SPs where StudentId would be a key column. How can i get the list of all such SPs? Thanks for your inputs. ...
Hi everyone. Following / copying computhomas's question, but adding some twists... I have the following table in MSSQL2008 id | business_key | result | date 1 | 1 | 0 | 9 2 | 1 | 1 | 8 3 | 2 | 1 | 7 4 | 3 | n | 6 5 | 4 | 1 | 5 6 | 4 | 0 | 4 And now i want to group based on the business_key returning the complete entry with the newest...
There is a need to build constraint on the column that guarantees that only one value in all rows is 1 and all the others are 0. Solution with triggers exists but I would like to have something built in. Is such thing possible at all? ...
I have a query in sql server 2008. That I want to either pass a value from the dropdownlist or IS NOT NULL (so it shows all of the values). What's the best way to handle this? I know you can't pass the string "IS NOT NULL" to the parameter. I'm a bit stuck on this one. ASP.NET 3.5 and SQL Server 2008. ...
I'm getting the error [ORA-01427: single-row subquery returns more than one row] when I execute a query. I have a query structured like so: SELECT LV.PRICE, (SELECT C.MODEL_NAME FROM CARS C WHERE C.MODEL_ID = LV.MODEL_ID) as MODEL_NAME FROM LEDGER_VIEW LV WHERE LV.PRICE < 500 It's breaking on the nested select. I know the logic bot...
Hi, I have some column EntityName, and I want to have users to be able to search names by entering words separated by space. The space is implicitly considered as an 'AND' operator, meaning that the returned rows must have all of the words specified, and not necessarily in the given order. For example, if we have rows like these: abb...
OdbcConnection odbc_conn = new OdbcConnection(_cs_odbc); SqlConnection sql_conn = new SqlConnection(_cs_sql); odbc_conn.Open(); sql_conn.Open(); using (OdbcCommand odbccmd = new OdbcCommand(odbc_cmd, odbc_conn)) { odbccmd.CommandTimeout = 30; using (OdbcDataReader dr = odbccmd.ExecuteReader()) { SqlBulkCopyOp...
How can I simplify multiple 'not in' queries? Is it efficient to use multiple subqueries: Not in (...) and Not in (..) and Not in (..) I am using count (sorry forgot about that) Select count (VisitorID) from Company where VisitorID not in (select VisitorID from UserLog where ActionID = 2 ) and VisitorID not in (select VisitorID...
Hey, I have a model Rails model called Orders that has a type_id, location, and price. Each type can have multiple orders at the same location with different prices. Check below for a idea of the table structure. id | type_id | location_id | price ----------------------------------- 1 | 1 | 1 | 12 2 | 1 | 1 ...
I have a recursive scalar function that needs to update a record in another table based on the value it is returning, however UPDATE statements are not allowed in the function. How can I update the table from within the function? ...
Everything's been working fine and haven't made any changes to our mail packages. But i just noticed that our unsent_emails table has been filling up, and when i tried to manually sent the unsent emails (a periodic job set up in user_jobs) i get the following errors; Error with main mail package: ORA-29279: SMTP permanent error: 501 5.5...
I have a MySQL table which looks like the following: user_id resource_id last_used -------------------------------- 1 1 2010-09-01 1 2 2010-09-02 1 3 2010-09-04 2 3 2010-09-08 2 2 2010-09-03 3 1 2010-09-01 3 1 ...
This is a follow-up from this question. Functions are not allowed to write to the database, but what if I wanted to update a record every time a function was called, specifically a recursive function? Currently, I have a function that takes an ID and returns a float. I'd like to update a table using the given ID and the returned float....
Hi, In Sql Server 2000 and 2005 I am running a select statement in a while loop. JFYI, this select statement is connecting to many linked servers and getting some values. IF there is any error, i want it should still execute next statement in the loop (similar to continue statement in c#) Example:- while @rowcount < 10 begin set @sq...
Hi! I'm a beginner in MySql. But I am creating a calendar application, where I have a database of future event posts. The event post have the attributes: id, title, startTime, endTime, startDate, repeat. Where the last attribute repeat, is if the event post shall be repeated weekly, monthly or none start by startDate. I want a sql-quer...
I want to write the following (pseudo) SQL statement in MS Access: Select C from MyTable where (A, B) IN (select distinct A,B from MyTable); I tried that but got the complaint "You have written a subquery that can return more than one field without using the EXISTS reserved word in the main query's FROM clause." I appreciate any fee...
My thinking is that if I put my ANDs that filter out a greater number of rows before those that filter out just a few, my query should run quicker since that selection set is much smaller between And statements. But does the order of AND in the WHERE clause of an SQL Statement really effect the performance of the SQL that much or are t...
I have many tables that have a [DateCreated] column that also needs a [DateModified] column. The [DateModified] column will need an Update trigger that inserts the current date (getdate()) into this new [DateModified] column. Each table uses custom schema, not [dbo]. Whats the easiest/best way to do this? ...