sql

Single tenant, multiple application database design?

Most questions about tenancy are centered around multi-tenancy database design issues. I want to know about single tenancy but multiple applications. The software I'm developing allows for a single user to create, from a single code base, multiple applications(I call them "sections"): user could create a blog inside domain.com/applicat...

Select based on the number of appearances of an id in another table

I have a table B with cids and cities. I also have a table C that has these cids with extra information. I want to list all the cids in table C that are associated with ALL appearances of a given city in Table B. My current solution relies on counting the number of times the given city appears in Table B and selecting only the cids that...

How do I do this in Sql? Loop? Multi-Insert?

This is the DB Schema: PC - id (pri key) - model - name Laptop - id (pri key) - model - name How do I get for each unique(model) laptop, how do I insert it into PC with model number + 1? (+1 because I know insert into might work but the prob I need won't be solved with insert into) Any clue? Elaboration: For each unique (based o...

Default values for COUNT in MySQL

I have a table looking something like this: +-------+-----+ |country|prop1| +-------+-----+ |RO | 1 | |RO | 2 | |UK | 1 | |IT | 2 | +-------+-----+ I want to count the rows for which the prop1 is not null and I use the following select: SELECT `country`, COUNT(*) as number FROM table GROUP BY `country`; this...

How do I update n rows in a table?

I need to update the first N rows in a table meeting a condition. I know I can do an Update Top N... but the problem is that N is in a @variable. UPDATE TOP @N SET ... doesn't work. Is there a way to do this that I am just missing? No specific table definitions here because it doesn't matter what the columns are.. If I can do it for...

Make sure most recent SQL query result is used in autosuggest

Hi all, I'm using the PHP code below to get results to return to an autosuggest / autocomplete. The problem is that if an earlier SQL query takes longer to return from the database than the most recent SQL query then the results of the older query will be displayed in the autosuggest results box. So if you start searching for Thomas, it...

Memory effective way to read BLOB data in C#/SQL 2005

What's the most memory effective way to read an SQL 2005 image field using C# 3.5? Right now I have a (byte[])cm.ExecuteScalar("..."). If I could not read all field content into memory, that would be nice. ...

datetime in bigint field

I am trying to add a date time to a bigint field and then display it in a gridview using SQL query... First im not sure how to add the date time in big int field so im using long s= DateTime.Now.Ticks; which is storing a sample value like 633896886277130000 then i want get only the date from it using the SQl Select statement and di...

How do I get all records from tableA with a left outer join and a where condition on tableB?

Basically, what I want is if there is a record in tableB of type 'X' I want to see it, otherwise I don't, but I want all records from tableA. I know I could accomplish this by putting the tableB.type = 'X' in the LEFT OUTER JOIN ON clause, but I can't do that because I'm limited to using only the where condition because I'm using a res...

PHP MySQL INSERT return value with one query execution

Is there anything returned from MySQL/PHP on a INSERT query being executed? Here is my function which I have in a CLASS. function mysqlQuery($query) { // Gets the results from the query $results = mysql_query($query, $this->connection); // Loops through the queried results as an multi-dimensional array while($rows = mysql_f...

Finding MySQL errors from LOAD DATA INFILE

I have a very large set of files and one of them is showing errors when I import it into mysql from the commandline mysql> prompt. I'm not sure how to check the data so the only thing I have to go by is the fact that the prompt reports 65,535 warnings on import. mysql> use dbname; Database changed mysql> LOAD DATA LOCAL INFILE '/dump.tx...

IF EXISTS, THEN SELECT ELSE INSERT AND THEN SELECT

How do you say the following in Microsoft SQL Server 2005: IF EXISTS (SELECT * FROM Table WHERE FieldValue='') THEN SELECT TableID FROM Table WHERE FieldValue='' ELSE INSERT INTO TABLE(FieldValue) VALUES('') SELECT TableID FROM Table WHERE TableID=SCOPE_IDENTITY() END IF What I'm trying to do is to see if there is a blank fie...

SQL Server Database Restore Hourly

Hi I run a software platform and am thinking of setting up a demo site so that people can login into a pretend site and edit the data, etc... However I want then the database to 'reset' every 'x hours / days' is there a way sql server can do this itself? Otherwise I will have to code an application to restore all the table data and th...

reccomended database schema design books?

I have some good books on database theory (covering relational calculus, tuples, normal forms etc.) but I'd like a nuts-and-bolts text on appropriate database design: appropriate schema design choices and practical issues that come up in the field. Not so much theory oriented but from the perspective of being in the trenches. Could y...

.XDR to MySQL data migration

I have .xdr file and I need to store it's data into mysql database. How should I do this? Thank you! ...

Timeout expired. The timeout period elapsed prior to ...

I am trying to access SQL 2005 database.When I am trying to Login it is throwing the folllowing error. Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. I was connected to the DB and was working on the Queries/procs , when suddenly i am unable to access the DB. One of ...

Storing multiple choice values in database

Say I offer user to check off languages she speaks and store it in a db. Important side note, I will not search db for any of those values, as I will have some separate search engine for search. Now, the obvious way of storing these values is to create a table like UserLanguages ( UserID nvarchar(50), LookupLanguageID int ) but the s...

Simple SQL problem (mysql)

SELECT username, (SELECT follow FROM follow WHERE follow_user_id = user_id) AS following FROM user WHERE user_id = 1 I want to know how I can check if follow (sub-query (select follow ...)) returns a value. If it did, then replace it with 'yes'. If it didn't, then replace it with 'no'. ...

How to update DB structure when updating production system without doing a teardown / rebuild

If I'm working on a development server and have updates to the database structure for some of our releases, what is the best way to update the structure on the production server? Currently we create a new production database containing the structure only, do a SQL dump of the data on the 'old' production database, then run a SQL query t...

SQL IF ELSE BEGIN END

If there are no begin and end statements in sql, the next statement is the only one that gets executed if the if condition is true...in the case below, is there anyway the insert statement will also get executed if the if condition is true? IF (a > 1) SET @b = 1 + 2 INSERT INTO #F (a, b, c) VALUES (1, 2, 3) ...