sql

mysql count(*) left join group by - the number of files in a folder

Hi I have the following tables CREATE TABLE `files` ( `fileid` int(11) NOT NULL AUTO_INCREMENT, `filename` varchar(255) NOT NULL, `filesize` int(11) NOT NULL, `folder` int(11) NOT NULL, PRIMARY KEY (`fileid`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; CREATE TABLE `folders` ( `directoryid` int(11) NOT NULL AUTO_INCREMENT, `d...

mysql update enums

I have a field with enums: 'preview','active','closed' When I query like this: $query = "UPDATE albums SET album_active = preview WHERE album_id = 3"; $result = mysql_query($query); if (!$result) die('Invalid query: ' . mysql_error()); I get : Invalid query: Unknown column 'p...

how to enforce multiple unique fields in database table

i have a table called Cars and the primary key of the table is 'id'. I also have a field called 'name'. I would like to make sure no one enters the same name twice even though it wont break my db integrity. what is the best way of doing this? ...

Why can’t I create a database in an empty ASP MVC 2 project using Project->Add->New Item->SQL Server Database?

I'm diving head first into ASP MVC and am playing around with creating and manipulating a database. I did a search and found this tutorial for creating a database, however when I follow it, I get this error right at the start when trying to add a new database to my fresh, empty ASP MVC 2 project... A network-related or instance-speci...

Primary Key Identity Value Increments On Unique Key Constraint Violation

I have a SqlServer 2008 table which has a Primary Key (IsIdentity=Yes) and three other fields that make up a Unique Key constraint. In addition I have a store procedure that inserts a record into the table and I call the sproc via C# using a SqlConnection object. The C# sproc call works fine, however I have noticed interesting results ...

Check For Duplicate Records VS try/catch Unique Key Constraint

I have a database table that has a Unique Key constraint defined to avoid duplicate records from occurring. I'm curious if it is bad practice to NOT manually check for duplicate records prior to running an INSERT statement on the table. In other words, should I run a SELECT statement using a WHERE clause that checks for duplicate value...

rails active record - Advanced find

I have the following model setup - a User is interested in projects in many project Categories. Each Project has many categories. Like so: class User has_many :projects has_and_belongs_to_many :project_categories class Project belongs_to :user has_and_belongs_to_many :project_categories class ProjectCategory has_and_belongs_...

Create database triggers with sql-injection without stacked queries

Currently im working on a research paper about sql-injection with RFID tags and Im curious if it is possible to create a database trigger with an sql injections if stacked queries are disabled. If stacked queries are enabled, of course it is easy (assuming you know the table layout), but what if they're disabled for security reasons. ed...

INNER JOIN Returns Too Many Results

I have the following SQL: SELECT * FROM [Database].dbo.[TagsPerItem] INNER JOIN [Database].dbo.[Tag] ON [Tag].Id = [TagsPerItem].TagId WHERE [Tag].Name IN ('home', 'car') and it returns: Id TagId ItemId ItemTable Id Name SiteId ------------------------------------------ 1 1 1 Content 1 home 1 2 1 2 Cont...

tricky SQL when joining

I've two tables, shows and objects. I want to print out the latest objects, and the shownames for them. Right now I'm doing it this way: SELECT MAX(objects.id) as max_id, shows.name, shows.id FROM shows, objects WHERE shows.id = objects.showId GROUP BY shows.name however, if I also want to fetch the episode of the object I can't pu...

Convert a SQL Server database to MYSQL database

Alright so I want to convert an already exist SQL Server database (2005) to a MYSQL database. There is nothing extraordinary to be done The only things I need to achieve is Recreate the tables Transfer data Relationships would be nice but not necessary No views, no sprocs, no functions. Any easy way to do this. Also do you know o...

All symbols after "&" stripped

My query: mysql::getInstance()->update('requests', array('response' => mysql_real_escape_string($_POST['status'])), array('secret' => $_POST['secret'])); ?> If i wand to add string with "&" symbol, all symbols after "&" stripped. Example: string: !"№;%:?*()_+!@#$%^&*()_+ in database i see only: !"№;%:?*()_+!@#$%^ How to fix this? ...

Is there a way to make Identity Specification show up in the table designer next to the Allow Nulls column?

I would like to be able to see the Identity Specification (not as worried about seed/increment) and hate having to scroll around on the column properties panel. Is it possible to add a column next to Allow Nulls in the table designer for Identity Specification? Specifically I am concerned with MS SQL Management Studio 2008. ...

Set primary key with two integers

I have a table with primary key (ColumnA, ColumnB). I want to make a function or procedure that when passed two integers will insert a row into the table but make sure the largest integer always goes into ColumnA and the smaller one into ColumnB. So if we have SetKeysWithTheseNumbers(17, 19) would return |-----------------| |ColumnA | ...

How can I round money values to the nearest $5.00 interval?

I have an Informix-SQL based Pawnshop app which calculates an estimate of how much money should be loaned to a customer, based on the weight and purity of gold. The minimum the pawnshop lends is $5.00. The pawnshop employee will typically lend amounts which either ends with a 5 or 0. examples: 10, 15, 20, 100, 110, 125, etc. They do thi...

The multi-part identifier could not be bound

I have this very simple sql statement: SELECT max_dose FROM psychotropes WHERE (patient_meds.psychotrope = psychotrope_name) AND (patient_meds.patient_id = 12) when I try to run it in Visual Studio 2008, it tells me "The multi-part 'patient_meds.psychotrope' identifier could not be bound" it's weird, because I did set...

getschema("foreignkeys") against SqlClient doesn't yield enough information

I need two tables and two sets of fields, not the name of the foreign key and one of the table names. Does anyone know how to query SQL Server for complete foreign key information? Thanks! ...

SQL Server: how to optimize "like" queries?

I have a query that searches for clients using "like" with wildcard. For example: SELECT TOP (10) [t0].[CLIENTNUMBER], [t0].[FIRSTNAME], [t0].[LASTNAME], [t0].[MI], [t0].[MDOCNUMBER] FROM [dbo].[CLIENT] AS [t0] WHERE (LTRIM(RTRIM([t0].[DOCREVNO])) = '0') AND ([t0].[FIRSTNAME] LIKE '%John%')...

Query to add a column depending of outcome of there columns

I have a user table 'users' that has fields like: id first_name last_name ... and have another table that determines relationships: user_id friend_id user_accepted friend_accepted .... I would like to generate a query that selects all the users but also add another field/column say 'network_status' that depends on the values of use...

sql question regarding sum() and tax (MySQL 5.0.89)

Select id, sum(amount), vat From transactions WHERE id=1; Each record in this table has a vat percentage, I need to get the total amount in all records, however each amount has to be multiplied by its vat %. Is there away to do this without looping through all records? ...