I want to maintain a list of global messages that will be displayed to all users of a web app. I want each user to be able to mark these messages as read individually. I've created 2 tables; messages (id, body) and messages_read (user_id, message_id). Can you provide an sql statement that selects the unread messages for a single user? O...
I need to return a list of record id's from a table that may/may not have multiple entries with that record id on the same date. The same date criteria is key - if a record has three entries on 09/10/2008, then I need all three returned. If the record only has one entry on 09/12/2008, then I don't need it. Thanks in advance. ...
How cheap can MySQL be compared to MS SQL when you have tons of data (and joins/search)? Consider a site like stackoverflow full of Q&As already and after getting dugg. My ASP.NET sites are currently on SQL Server Express so I don't have any idea how cost compares in the long run. Although after a quick research, I'm starting to envy ...
I know that that is not a question... erm anyway HERE is the question. I have inherited a database that has 1(one) table in that looks much like this. Its aim is to record what species are found in the various (200 odd) countries. ID Species Afghanistan Albania Algeria American Samoa Andorra Angola .... Western Sahara Yemen Zambia Zim...
Hi all, I have recently written an application(vb.net) that stores and allows searching for old council plans. Now while the application works well, the other day I was having a look at the routine that I use to generate the SQL string to pass the database and frankly, it was bad. I was just posting a question here to see if anyone els...
I am looking for a best practice for End to End Authentication for internal Web Applications to the Database layer. The most common scenario I have seen is to use a single SQL account with the permissions set to what is required by the application. This account is used by all application calls. Then when people require access over the...
When attempting to understand how a SQL statement is executing, it is sometimes recommended to look at the explain plan. What is the process one should go through in interpreting (making sense) of an explain plan? What should stand out as, "Oh, this is working splendidly?" versus "Oh no, that's not right." ...
Can databases (MySQL in particular, any SQL--MS, Oracle, Postgres--in general) do mass updates, and figure out on their own what the new value should be? Say for example I've got a database with information about a bunch of computers, and all of these computers have drives of various sizes--anywhere from 20 to 250 GB. Then one day we upg...
I have a query: SELECT * FROM Items WHERE column LIKE '%foo%' OR column LIKE '%bar%' how do I order the results? lets say I have rows that match 'foo' and rows that match 'bar' but I also have a row with 'foobar' how do I order the returned rows so that the first results are the ones that matched more LIKEs? Thanks ...
problem how to best parse/access/extract "excel file" data stored as binary data in an SQL 2005 field? (so all the data can ultimately be stored in other fields of other tables.) background basically, our customer is requiring a large volume of verbose data from their users. unfortunately, our customer cannot require any kind of db e...
What's the best way to calculate percentile rankings (e.g. the 90th percentile or the median score) in MSSQL 2005? I'd like to be able to select the 25th, median, and 75th percentiles for a single column of scores (preferably in a single record so I can combine with average, max, and min). So for example, table output of the results mi...
I have a list of timesheet entries that show a start and stop time. This is sitting in a MySQL database. I need to create bar charts based on this data with the 24 hours of the day along the bottom and the amount of man-hours worked for each hour of the day. For example, if Alice worked a job from 15:30 to 19:30 and Bob worked from 12:1...
What are the potential pros and cons of each of these queries given different databases, configurations, etc? Is there ever a time when one would be more efficient than the other? Vice versa? Is there an even better way to do it? Can you explain why? Query 1: SELECT * FROM table_a, table_b, table_c WHERE table_a.id = table_b...
I'm a bit frustrated with my MySQL database at the moment, so I've been thinking about all the things I'd like to see in the database of the future. But I thought it would be fun to hear other people's thoughts too--I'm not a pro by any means. ...
Is there any good tool for working on Dabtabase apart from Toad which requires license and DBVisualiser not supportive ...
I want to find 2nd,3rd..nth maximum value of a column ...
I'm currently working on a web business application that has many entities (people,organizations) with lots of contact information ie. multiple postal addresses, email addresses, phone numbers etc. At the moment the database schema is such that persons table has postal address columns, phone number columns as does organizations table. ...
In Oracle, what is the difference between : CREATE TABLE CLIENT ( NAME VARCHAR2(11 BYTE), ID_CLIENT NUMBER ) and CREATE TABLE CLIENT ( NAME VARCHAR2(11 CHAR), -- or even VARCHAR2(11) ID_CLIENT NUMBER ) Thank you. ...
I feel like I have a verry basic/stupid question, yet I never saw/read/heard anything in this direction. Say I have a table users(userId, name) and a table preferences(id, userId, language). The example is trivial, but could be extended to a situation with multi-level relations and way more tables.. When my UI requests to delete a user ...
My workplace has sales people using a 3rd party desktop application that connects directly the a Sql Server and the software is leaving hundreds of sleeping connections for each user. Is there anyway to clear these connection programmatically? ...