sql

Which is faster, horizontal or vertical counting?

I need to get summary data from many many rows. The summary fields are counts of how many entries have each value for different fields. For example, a table with people's age, city, job, etc, the summary data would include fields like "countManager", "countCodeMonkey" for each job, and then "countChicago", "countNewYork" etc for cities. ...

Generating Integer Identity Primary Key for Entity Object in Entity Framework and SQL Server CE

Hello I heard that this issue is fixed in SQL Server Compact Edition 4.0 CTP As recently I just stepped into SQL Server CE and Entity Framework, and VS2010 not yet supporting SQL Server CE 4.0 I think I would need a work around for this issue Can I know how to generate an Integer type Identity Primary Key inside the constructor of th...

how to display employee names starting with a and then b in sql

i want to display the employee names which having names start with a and b ,it should be like list will display employees with 'a' as a first letter and then the 'b' as a first letter... so any body tell me what is the command to display these... ...

Is there a tool that can transform a static SQL string into valid Drupal db query code?

I am wondering if there is some tool available, that will take as input a SQL command, and as output will return valid Drupal code that can be used in the Drupal Api? EDIT The idea is I have large, 25 lines of SQL commands ready to be used on the database. And they are somehow complicated, so I am wondering how could I rewrite them wit...

sp_addsrvrolemember not granting login

According to MSDN documentation this proc is supposed to grant login to a windows user, but when i run the following script, I cannot login using the specified user even though the account shows up under security. exec sp_addsrvrolemember 'domain\user','dbcreator' also is there a way to query if a particular login can actually login t...

Multiple delete in a single query.

Hi, DELETE FROM Table1 WHERE ConditionID=?ConditionID; DELETE FROM Table2 WHERE ConditionID=?ConditionID; DELETE FROM Table3 WHERE ConditionID=?ConditionID; ConditionID is a column present in Table1,Table2,Table3, instead of running 3 times individually, is there a way to run all the three in a single query(in mysql)? Thanks. ...

Validating data before inserting into database

I want to validate the data contained by Value Objects before inserting into the database (e.g. check that php string is smaller than 255 chars before inserting into a column of type varchar(255) or that a string is not inserted in an int(11) table column, php date has the correct format etc.). Is there any framework or easy way to vali...

SQL posts top tags

I am using a MySQL database and looking to capture the top tags from my blog. The table looks like this: ++++++ post_tags ++++++ + id INT(10) + + post_id INT(10) + + tag_id INT(10) + +++++++++++++++++++++++ ++++++++ tags +++++++++ + id VARCHAR(10) + + title VARCHAR(50) + + uri VARCHAR(75) + +++++++++++++++...

NoSQL system to save relational data

Hey, If my data is relational (publishers-authors-books, associations-teams-players), can we use NoSQL system like HBase or MongoDB to store the data? (I know it may sound like a stupid question but I'm just learning :)) ...

MySQL duplicates -- how to specify when two records actually AREN'T duplicates?

I have an interesting problem, and my logic isn't up to the task. We have a table with that sometimes develops duplicate records (for process reasons, and this is unavoidable). Take the following example: id FirstName LastName PhoneNumber email -- --------- -------- ------------ -------------- 1 John Doe 123-555-...

How to alter the default value set to a column in a table in SQL?

How can you alter the default value set to a column in a table in SQL. I got an error from: ALTER TABLE tablename.tab ALTER COLUMN mess1 DEFAULT ('hi') What was the correct query? ...

functional-dependencies example

Can anyone plz give example how functional dependencies are being implemented in sql. I have read somewhere that functional dependencies can be implemented using assertions. But how these are implemented can anyone give an example for it. Moreover how to implement FD's in Oracle coz, assertions can't be created in oracle. ...

Avoiding duplicating SQL code?

Hi, I know many ways to avoid duplicating PHP code (in my case PHP). However, I am developing a rather big application that does some calculations on the database with the data it finds, and I have noticed the need to use the same code (parts of SQL) in other places. I don't like the idea of copying and pasting the same thing over and ...

Sql - struggling with count/having query based on several joins

Hi, I'm struggling to get my query working. Basically, what it should do is only return vacancies that haven't been filled. For example, a vacancy can have a pre-determined limit of 5. To fill this, it will need to have a mixture of 5 active or pending bookings or simply 5 active or 5 pending bookings. A pending booking is changed to a...

Please help me create sql statement for my tables?

I have 2 tables(1.Employee 2.Family). I want write a sql statement that produce the following output. Pictures Url : http://www.enterupload.com/8ryuew2anq0q/QUESTION1.jpg.html Mirror 1 : http://www.freeimagehosting.net/image.php?c9bea5b6b6.png Mirror 2 : http://0k.010.img98.com/out.php/i342669_QUESTION1.png ...

SQL MDF file size not changing

Hi All, In my Db initially i have one table with three columns and no data at that time the MDf file size was 5122 KB. Then i have inserted 500000 records in this table, MDF file size increased to 19456 KB Then i have updated my table and made all the values of one column as Null but the file size is still same i.e 19456 KB. Then i h...

Hibernate simple criteria query solving problem

I'm stuck with a very simple criteria query problem: sess .createCriteria(user.class, "user") .user_c.add(Restrictions.eq("user.status", 1)) .user_c.createAlias("user.userCategories","ucs") .add(Restrictions.eq("ucs.category_id",1)); .add(Restrictions.eq("ucs.category_id",'yes')); .add(Restrict...

How to optimize this query to get rid of subqueries?

I have a database with 2 tables: Table 1: CREATE TABLE IF NOT EXISTS `sales` ( `sale_id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) NOT NULL, `sale_total` int(11) NOT NULL, `sale_date` date NOT NULL, `sale_status` int(11) NOT NULL, PRIMARY KEY (`sale_id`) ) ; Table 2: CREATE TABLE IF NOT EXISTS `users` ( `user_...

Getting insert id with insert PDO MySQL

Im getting to grips with the basics of PDO. However Im trying to get the id of the inserted row, Im using: $query = $system->db->prepare("INSERT INTO {$this->_table} (name,description) VALUES (:name,:description)"); $query->execute(array('name'=>$name,'description'=>$description)); The tutorials I have come across are regarding trans...

Poor MySQL Join Performance

I've been trying to perform a join on two tables in MySQL, and the query will run for a minute or two before I run out of memory without getting results. I'm far from a database expert, so I'm not sure if I'm writing my queries poorly, if I have some MySQL settings poorly configured, or if I really should be doing something else entirel...