sql

Update Subquery Question, with foreign key

I misremembered what the key was for this Templates table and therefore I added the wrong field as a foreign key. Now I need to add the foreign key and I want to populate its values based on this other field that is already populated. I started trying to do this with an update statement, but I'm not sure how to do it. Part of my schema:...

What are some best practices for optimizing multiple column LIKE SQL queries?

I have a search query that I'm inheriting and attempting to optimize. I am curious to hear if anyone has any best practices and recommendations for such. The production server is still SQL Server 2000 also. The query is an advanced customer search stored procedure that accepts 5 different search criteria parameters (i.e. first name, la...

Simple SQL statement builder for .Net

I've jumped into an ongoing .Net 2.0 web app project for a larger company as a freelancer. Their DAL has lots of functions that manually construct and execute SQL statements -- many of them are long, messy and as a result difficult to understand and debug. I wrote a simple "sql helper" that lets me write things like this: sqlh.addValue...

basic many-to-many sql select query

I think this should be easy, but it's evading me. I've got a many-to-many relationship between Accounts and Account Groups. An Account can be in zero or more Groups, so I'm using the standard join table. Accounts -------- ID BankName AcctNumber Balance AccountGroups ------------- ID GroupName JoinAccountsGroups ------------------ AID...

Export Excel range/sheet to formatted text file

Hello, I have been tasked with creating a reusable process for our Finance Dept to upload our payroll to the State(WI) for reporting. I need to create something that takes a sheet or range in Excel and creates a specifically formatted text file. THE FORMAT Column 1 - A Static Number, never changes, position 1-10 Column 2 - A Dynamic ...

SQL queries to test query building capabilties

I was thinking of building a small application to help fellow developers to learn advanced query concepts with a Q&A applications. I need some SQL expert to list a bunch of tough queries and also provide the sample table schemas used. ...

SQL 'Or' operator. How does it work in the following scenario?

I've been working on optimizing a query and have ran into a situation that's making me question how I've always used SQL's OR operator. (SQL Server 2000 also) I have a query where the conditional (WHERE) clause looks something like this: WHERE (Column1 = @Param1 or Column1 LIKE @Param1 + '%') AND (@Param2 = '' OR Column2 = @Param2 OR C...

Create "group function"

I want to create a group function on my site and I'm wondering what the best way to do this would be? would i be best to create two tables groups and group_members? im fairly new to sql so im not so sure. im thinking this: groups group_id int(11) NOT NULL auto_increment, group_name varchar(32) NOT NULL group_members id int(11...

What is the best way to represent a many-to-many relationship between records in a single SQL table?

I have a SQL table like so: Update: I'm changing the example table as the existing hierarchical nature of the original data (State, Cities, Schools) is overshadowing the fact that a simple relationship is needed between the items. entities id name 1 Apple 2 Orange 3 Banana ...

Which are the SQL improvements you are waiting for?

Dealing with SQL shows us some limitations and gives us an opportunity to imagine what could be. Which improvements to SQL are you waiting for? Which would you put on top of the wish list? I think it can be nice if you post in your answer the database your feature request lacks. ...

LINQ COUNT on multiple columns

If I have a table with a title column and 3 bit columns (f1, f2, f3) that contain either 1 or NULL, how would I write the LINQ to return the title with the count of each bit column that contains 1? I'm looking for the equivalent of this SQL query: SELECT title, COUNT(f1), COUNT(f2), COUNT(f3) FROM myTable GROUP BY title I'm looking fo...

SQL friend function

What would be the best way to structure a friend "function". This is what im thinking: Lets say I have a usertable with the following columns: user_id, username, password... and a table by the name of friends with columns: friend1, friend2, accepted. If user2 adds user1 I want user2 to be shown on user1's profile and user1 on user2's.....

Querying against LINQ to SQL relationships

Using LINQ to SQL db.Products.Where(c => c.ID == 1).Skip(1).Take(1).ToList(); executes SELECT [t1].[ID], [t1].[CategoryID], [t1].[Name], [t1].[Price], [t1].[Descripti n], [t1].[IsFeatured], [t1].[IsActive] FROM ( SELECT ROW_NUMBER() OVER (ORDER BY [t0].[ID], [t0].[CategoryID], [t0].[Name , [t0].[Price], [t0].[Description], [t0].[...

Disadvantage of choosing large MAX value for varchar or varbinary

What's the disadvantage of choosing a large value for max when creating a varchar or varbinary column? I'm using MS SQL but I assume this would be relevant to other dbs as well. Thanks ...

Create SQL query that orders results by which condition they meet

This is for MySQL and PHP I have a table that contains the following columns: navigation_id (unsigned int primary key) navigation_category (unsigned int) navigation_path (varchar (256)) navigation_is_active (bool) navigation_store_id (unsigned int index) Data will be filled like: 1, 32, "4/32/", 1, 32 2, 33, "4/32/33/", 1, 32 3, 34,...

List random products from category in osCommerce

I was asked to implement a random product listing for an osCommerce on-line store. I can do some basic PHP but the store uses a framework of its own and I'm clueless how to do it. First I try to make a random list just on the front page: I open index.php and find the include(DIR_WS_MODULES . FILENAME_UPCOMING_PRODUCTS); line after wh...

Let user sort records ?

Hello, i am trying to figure out a way to let the user sort records (etc a list of friends). I want to give the user the opportunity to move a record (friend) straight to the top or bottom of the list, or by entering a number (in between). First i thought of just adding a column called SortOrder (int) to the table with all the users f...

SQL questions

I have a table A ID Term 10 A 10 B 10 C 20 A 20 B 20 E what's the best way to write a sql to - get ID 10, if I try to find (A,B,C) - get NOTHING if I try to find (A,B) - get ID 20, if I try to find NOT in (C,D) Select distinct ID from TableA where Term in (A,B,C) will return both 10 and 20 Select distinct I...

SQL joining a few count(*) group by selections

I have the following table containing the winning numbers of 6/49 lottery. +-----+------------+----+----+----+----+----+----+-------+ | id | draw | n1 | n2 | n3 | n4 | n5 | n6 | bonus | +-----+------------+----+----+----+----+----+----+-------+ | 1 | 1982-06-12 | 3 | 11 | 12 | 14 | 41 | 43 | 13 | | 2 | 1982-06-19 | 8 |...

How can I update a small field in a big SQL table if another field in the same row is changed by an external process?

I'd like to call Update ... Set ... Where ... to update a field as soon as that evil ERP process is changing the value of another. I'm running MS SQL. ...