sql

Does [My]SQL have a Pre-Processor-like Facility?

I'm writing a small deployment SQL script for my first database-driven app. In the process, I find that I repeat myself a lot, for instance: GRANT USAGE ON *.* TO 'foo'@'localhost'; DROP USER 'foo'@'localhost'; CREATE USER 'foo'@'localhost' IDENTIFIED BY 'password'; It would be fantastic if I could use a variable or a macro to replac...

loop categories with assigned forums

forumcats: id, name forums: id, name, cat_id How can i join these together and print the forums assigned to the categories under them Would really appriciate if someone could give me a hand This is what i mean: A category A forumA forum A 2nd category Another forum Another forum Another forum A 3rd catagory Another forum A...

ASP.NET Profiles - Add a profile to an existing user

How do you add a profile entry (consisting of a guid, two strings, and an integer) to an existing user in the default SQL profile and membership providers? I can do this either via a sql query or C# code. I need to add a bunch of these profiles to already existing users. ...

Is there a better way to structure a SQL table handling User Questionaires?

I have a disclosure form that requires a user to enter yes/no answers for a series of around 30 questions. Is there a better way to create the table than having 30 columns that correspond the questions? Below is my MS-SQL script, but the question relates more to structure than syntax. CREATE TABLE Questionaire( QuestionaireID int ...

Selecting a specific row from a table

How can I select a particular row from a table? I want to do achieve something like the following: select * from (select * from tablename1 where type=1 order by id desc) where rownum = 5 select * from (select * from tablename1 where type=1 order by id desc) where rownum = $variable ...

Selecting Random Rows in MySQL

I am developing a quiz website, and I have a database which stores all of the questions. There are different types of quizzes, like math, science, history, etc. All of the questions are stored in one table. My questions table looks like this: questions ( qno(int) ,type(int), question, .... ,... ) qno is the primary key, and type is ...

.NET SqlCommand - finding Parameters in inline querys (Regex?)

I've got a method that uses sp_sproc_columns to find all parameters that need to be send for a stored procedure. My question is how can I do something similar for inline SQL querys? I want to get a list of the parameters the query is expecting. Would the only way achieving this will be to use Regular expression? Any examples? Exam...

connecting three tables in one query

I have the following tables mixes mid | date | info 1 | 2009-07-01 | no info yet music-review mid | song | buy 1 | Example - Example | http://example.com 2 | Exam - Exam | http://example.com tracklist tid | mid | mrid 1 | 1 | 1 2 | 1 | 2 is it possible to have an SQL query where you can li...

Reorder nodes in SQL Hierarchy data type

Using Hierarchy data type on SQL 2008. Nodes in my hierarchy go like this: value node 36 /8/1/ 38 /8/2/ 34 /8/3/ 40 /8/4/ 42 /8/5/ 44 /8/6/ 46 /8/7/ 48 /8/8/ I'd like to rearrange nodes so that /8/3/ and /8/1/ switch places. Any idea on how to do this? Only idea that I have so far is that I load all nodes o...

Determine varchar content in nvarchar columns

I have a bunch of NVARCHAR columns which I suspect contain perfectly storable data in VARCHAR columns. However I can't just go and change the columns' type into VARCHAR and hope for the best, I need to do some sort of check. I want to do the conversion because the data is static (it won't change in the future) and the columns are indexe...

Best tool for assisting writing complex SQL-queries?

Hello everyone, I often find myself spending a lot of time figuring out why certain SUM()-aggregates sum up wrongly in SQL-queries. This problem often occurs if I do not take care when adding a JOIN, resulting in duplicate values being summed up etc. If I work with a big query with lots of JOINs, nested subqueries, GROUP BYs etc. things...

C# SQL parent child table select query help

Hello, I have two tables that I want to fetch data from. Lets call them "Parent" and "Children". The "Parent" table has many records in the "Children" table. (One to Many) My problem is I want to find an efficient method for displaying the data the two tables contain to my ASP.NET MVC application. My approach is to select all records ...

Can this SQL be translated to LINQ?

Hi, I am working with a quite tricky SQL-Query that I would like to translate to LINQ. Do you think it is possible? WITH ConditionalChecks AS ( SELECT c.ItemId FROM ConditionalProperties c, Properties p WHERE c.PropertyId = p.Id AND c.IsChecked = 1 AND ( (p.SystemName = 'eatable') OR (p.SystemName = 'diy') ) ), ...

PHP/MySQL: Retrieving the last Inserted Ids for multiple rows

Hi When inserting data into a table which has an auto-incremented PK, I need to obtain that key for use in another statement. As many questions show on SO this can be done in php using mysql_insert_id(). However, I have been grouping my inserts together so I insert more than one row at a time. I did this as I guessed there would pro...

Update table with a multithreaded process and update certain rows based on priority

I have a table of customers. This table can be big, millions of rows. Every hour, I have a console app that goes through all the customers and updates another table with customer changes that occured in the last hour. What I want to do is two things: (1) Have the console app (or SSIS package) be multi-threaded so that I can have a coup...

What is wrong with my stored procedure?

I need to insert the selected data from tblA to tblB only if data selected does not exist in tblB. I created a button that will execute this stored procedure. I cannot figure out what I am doing wrong in my stored procedure, it is not inserting/showing non existing data into tblB. I am using SQL Server 2008 and ASP.NET. CREATE PROCEDU...

Good online resource for common databases / open databases?

I am trying to find the best area to download SQL tables of common subjects like states, countries, zip codes. Maybe even public/government information like FDA or FCC information. Does such a resource exist and is it mature? ...

How do I use php to create a database with table and info using a backup file?

How do I use php to create a database with table and info using a backup file? so far my code is <? header("content-type:text/plain"); $serv= mysql_connect('localhost', 'root', 'xxxx'); if($serv){ wText("Server connection created"); } else{ wText("Server connection failed"); } $sql ="CREATE DATABASE tf2faq"; if(@mysql_qu...

top X results per day with sql in access

I am trying to get the top 2 results per day, where the "top" results are the rows with the highest "MPR". My table looks like this: date symbol MPR 8/7/2008 AA 0.98 8/7/2008 AB 0.97 8/7/2008 AC 0.96 ... 8/7/2008 AZ 0.50 8/8/2008 AA 0.88 8/8/2008 AB 0.87 8/8/2008 AC 0.86 ... 8/8/20...

Copy blob data from one table to another on MySQL

I need to copy a set of data from one table to another that includes a BLOB column. I'm using an INSERT query with a subquery SELECT: INSERT INTO dest_table(field1,field2,field3,blobfield,field4) (SELECT t.myfield1,t.myfield2,t.id,t.blobfield,'SomeConstant' FROM tablename t) All fields get copied correct, except the BLOB. I know I'm m...