sql

Difference in rounding in sql and clr

I have searched and can not find the answer. I double checked the data types between SQL and CLR and I appear to have that correct. But I am getting a different result between using CLR and SQL. Not much, but enough to be off a penny. And that is not acceptable. Example in VB.NET Dim dLoanAmount As Decimal = 169500 Dim dNetDisc...

PHP MySQL SELECT text truncated

I have a MySQL table with a column 'full_description' of type 'text'. In that column is a standard lorem ipsum (plus the word END): Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris n...

SQL 2008 stored proc returns nothing if CROSS APPLY is nothing. How can I fix?

The below stored proc returns data from a number of related tables and also does a cross apply on the votes table. This cross apply returns an average of all truthfulness values in the truthid column associated with a particular articleid and the same for the relevanceid column. This works great except for when there are no votes yet cas...

MySQL: Creating a list of conversations (last msgs to/from user_id)

Hi, I have a mysql table containing messages: id (INT) sender_id (INT) recipient_id (INT) sent_time (INT) read_time (INT) body (TEXT) I need to retrieve a list of most recent messages user X received from or sent to other (distinct) users. In other words I'd like to create a full list of conversations with other users, sorted by sen...

Mysql Order By Calculated Value - User Defined Weightings of Categories

Hi All, I have a small problem with some late additions to a project, mysql and PHP. We have a table in mysql with 200,000 rows, each with an assigned category. Originally the select statement would just pull out the required items based on a where clause and order by an indexed id. Very quick. However we had some users request the ab...

Are foreign key constraints needed?

In a perfect world, are foreign key constraints ever really needed? ...

JavaScript library for dynamically generating SELECT statements

Is there a good JavaScript library that dynamically generates SQL SELECT statements? Squiggle-Sql does this in Java. ...

Select random rows but without duplicates of values from one column

This is a simple select from a single table. The purpose is to select four random products, one from each of x number of categories, with a couple of 'where' limitations. I've tried this: SELECT pName, pID from products WHERE pDisplay=1 AND pFeatured=1 GROUP BY pCategory ORDER BY RAND() LIMIT 0,4 This...

Mocking an Active Directory for off site development

I received a client application, using SQL Server 2005, which contains many views with joins to their active directory. They do this to secure what people can see in the database. I need to be able to run these view from my remote development environment and I am looking for suggestion on how to do this. I cannot duplicate their AD ...

Sql insert if doesnt exist, otherwise (completely different) update

Hi I want to INSERT a row but if only if it doesnt exist, otherwise i need to increase one column, rewrite the other, etc. How do i do this? Do i need a select? I am hoping there is either an INSERT or UPDATE statement that will let me know if it fails so i can do the other I am using sqlite ATM and i MAY switch to mysql. Using C#.NET....

How to ensure your sql transaction are safe?

Maybe this question is misleading but if you can answer it then do so. I remember posting many SQL questions and once in a while someone would say my transaction isnt safe. I can never remember the case. What are they? IIRC its when i do reads at the beginning of a table then write later and apparently other transactions can write even...

Sql Query Optimization

I am not so proficient in TSql as of now (writing since last 4/5 months) but I have written many queries. Although I have given the outputs, sometimes I feel that the queries are not so optimized. I searched in google and found lot of stuffs about query optimization, and they ask to look into the query plan(actual & estimated) for the ...

database design for unread meassages in mysql?

What would be the best way to check unread messages from mysql table... I have two tables one for users and the other for messages... In the users table I am saving the last-login-date column & in messages table the date-added column, but what would be the best way to check whether the user has accessed the messages or not.... I am think...

database index and memory usage

suppose I have a table that stores 100 million records of strings of varying sizes up to 20 characters in a column field. I need to index this column, I only have a 2GB-Ram machine, is this sufficient to perform such task? Is mysql recommended db engine for storage? ...

update multiple rows using limit in mysql?

UPDATE messages set test_read =1 WHERE userid='xyz' ORDER BY date_added DESC LIMIT 5, 5 ; I am trying to use this query to update a set of 5 rows using limit but mysql is showing an error..The one below is working UPDATE messages set test_read =1 WHERE userid='xyz' ORDER BY date_ad...

How do I return a value to a sqldatareader if value is null?

I am currently using a sql data reader (in vb.net) to extract an article object via a stored proc from a SQL Server 2008 database. Part of this object includes the two properties shown below: theArticle.Truthfulness = ((myReader.GetInt32(myReader.GetOrdinal("Truthfulness")))) theArticle.Relevance = ((myReader.GetInt32(myReader.GetOrdina...

Join characters using SET BASED APPROACH (Sql Server 2005)

I have a table where the data are like Data a b c I need to write a SQL Query to bring the following output Data abc Note:~ The number of characters are not constrained to 3. I have solved using while loop. But I have to do it using set based approach.So how can I join those characters? I tried using COALESCE but no luck Pl...

Distinct on 2 different fields at same time

Is it possible to get unique results of 2 different fields in 1 query? Ex. Table Criminal Criminal name Id No No of Crimes in Past father name state is it posible to get Criminal having distinct value (No of Crimes) in Past and distinct name in a single query? means crime No also should unique and name is also uniqe. ...

Picking info using junction table(SQL SERVER 2005) [ SET BASED]

I have 3 tables 1) tblPurchaser having 2 columns: PurchaserId PurchaserName 1 A1 2 A2 3 A3 2) tblCar having 2 columns: CarId Carname 11 C1 12 C2 13 C3 14 C4 And the last is a junction table tblInformation where the information about those persons are given who has purchas...

How do I insert information in a SQL table if the table has a foreign key in it?

Say I have a user table with the fields: Name, Email, Telephone, IDCareer In this table IDCareer is the foreign key. I have another table called Career with the fields: IDCareer, CareerName My plan it to have a ComboBox on my form and pulls a list of available careers directly from my DB. My problem is this is my first time using SQL ...