sql

SQL Join to only 1 row - SQL Server 2005

Hi I have an AllocatedStock table holding a case number (knows as a TPND) and a quantity. I need to select a list of product stock but present this with the product number (known as TPNB) rather than the case number. I also have a ProductLookup table which holds all TPNBs and TPNDs. AllocatedStock AllocatedStockID identity TPND int ...

How to SELECT a column held in a local variable in MySQL?

I have a variable holding the column name. I want to do a select with it like this way select `@x` from table; Is it possible? ...

Creating table from structure of another table with no data.

How can this be achieved in SQL (MS SQL) Thanks OK: let me bit clear what am trying to acheive is generating dynamic select statement with EXCEPT clause. eg: select col1,col2,col3 from @table except select col1,col2,col2 from @table so my resultset will be always different based on @table futher down i want to use this @ta...

Proper sql query in MySQL

I have table like this: Name Result T1 fail T2 pass T3 pass T2 fail T4 fail T1 pass T4 fail Now, I want to get a results like this: Name Result T1 pass T2 pass T3 pass T4 fail I tried using query like this, but it does not work. select (case when Result = "pass" then "p...

Is it good practice to have several foreign keys in one table?

Hello, Consider the following table structure: titles(titleID*, titleName) platforms(platformID*, platformName) products(platformID*, titleID*, releaseDate) publishers(publisherID*, publisherName) developers(developerID*, developerName) products_publishers(platformID*, titleID*,publisherID*) products_developers(platformID*,...

String formatting in T-SQL

I have added a column to a table that will store a formatted string based on concatenating a number of other columns so I can search over it more easily. In order to save loading the whole table into another app and updating the new column then persisting, I want to write an UPDATE SQL query. But I can't figure out how to take an integer...

Oracle SQL how to write a sql statement that verifies if user in my network( ie friends or friend of friends)

Hi I have this problem. Given a users table that consists of users' username in a social network and friends table that contain a user's name and a user's friendname like below... username friendname John Thomas Chris James ... I'm trying to write an SQL statement that will if a user is in my network. In other words i...

SQL converting nvchar to ntext

What I have is a staging table that is all nvarchar (so i can load it easily). In my live table i have a bunch of ntext items. I have the following: obviously this isnt the whole query: update SLTDS_C69_Stdtable set [AARIssue] = convert(ntext, st.[AARIssue]), [AttachmentIDs] = convert (ntext, st.[AttachmentIDs]) I get ...

SQL queries through VB.NET and JSON

I'm completely new to both VB.NET and JSON, and I'm trying to figure out how to do SQL queries towards an SQL 2005 Express server and format the return value to JSON. I got the queries working using this (perhaps very newbie-like) code; Imports System Imports System.Data Imports System.Data.SqlClient Imports System.Web.UI Imports System...

What goes into rolling your own wiki using c# and sql?

I'd like to understand how a wiki works, at least from a high level. When a user saves changes, does it always insert a new row in the database for that wiki article (10 revisions, 10 rows in the database). ...

SQL Server 2005 (Express) - Login vs User

I'm quite new to Microsoft SQL Server. I have some experience with MySQL, and there you have a user with priviliges, if I understand things right; these priviliges decide which databases you have access to on the MySQL server. However now I am in the situation where I have to restore a database on my SQL Server 2005 Express, and this da...

Change a Primary Key from Nonclustered to Clustered

Suppose I have an SQL Server 2005 table, TableX, with 2 indexes on it: PK_TableX = PRIMARY KEY NONCLUSTERED on FieldA IX_TableX_FieldB = CLUSTERED on FieldB I want to switch the PK to be CLUSTERED, and the other index to be NONCLUSTERED. I have to assume that the database will be in use at the moment I try to change the indexes round...

How to efficiently sql query child records from multiple tables?

We have a simple database. The User table holds users. The Accounts table holds multiple accounts for each user. The Topics table holds multiple topics for each account. So, a user would have multiple accounts and each account would have multiple topics. So, if I have a user with id=1 how do I efficiently query all 3 tables to get all t...

What are some "mental steps" a developer must take to begin moving from SQL to NO-SQL (CouchDB, FathomDB, MongoDB, etc)?

I have my mind firmly wrapped around relational databases and how to code efficiently against them. Most of my experience is with MySQL and SQL. I like many of the things I'm hearing about document-based databases, especially when someone in a recent podcast mentioned huge performance benefits. So, if I'm going to go down that road, what...

SQL caching strategy

I'm working on an interactive contact search page (contacts are returned via ajax as you type or select criteria). I want this page to be very responsive. There is a complex set of rules to determine which contact records a given contact can see; these rules are rolled up into a user-defined function, DirectoryContactsByContact(@Contac...

How to query: Select games in categories A, B and C

I'd like to know how to effectively run a query such as: select game from Game game inner join game.Categories cat where cat.Name in ('A', 'B') This gives me games with categories A or B. But I want games that the the A category and the B category. This is to be used in HQL (NHibernate Query Language), but I'd like also to know how t...

How can I SELECT the names of all groups a person belongs to using a JOIN instead of IN?

Consider the following simplified example: CREATE TABLE groups ( gid INTEGER PRIMARY KEY, name VARCHAR(100) ); CREATE TABLE people ( pid INTEGER PRIMARY KEY ); CREATE TABLE people_groups ( gid INTEGER NOT NULL CONSTRAINT fk_people_groups_group REFERENCES groups(gid), pid INTEGER NOT NULL CONSTRAINT fk_p...

SQL - how to count groups of rows and display the top/bottom 3

I realise this is likey to be an easy one, but my SQL is basic at best. Lets say I have a table containing a list of orders, with 'item_id' being one of the columns. I need to display the 3 least (or 3 most) popular orders of item. I know that I need to group the orders using item_id and then count them. Then I need to display the bott...

Built-in database role in SQL Server 2005 to permit execution of stored procedures?

In SQL Server 2005, there are built in roles: db_reader db_writer etc. Is there any role that lets a user execute an stored proc? I don't want to use db_owner, because that will permit deletion and updates, which I don't need. The only permissions I need are: SELECT EXECUTE ...

SELECT any FROM system

Can any of these queries be done in SQL? SELECT dates FROM system WHERE dates > 'January 5, 2010' AND dates < 'January 30, 2010' SELECT number FROM system WHERE number > 10 AND number < 20 I'd like to create a generate_series, and that's why I'm asking. ...