sql

SQL Convert column to row

My table has the following columns: A | B | C | D | E | F I want to displays this as follow: MyColumn | MyColumn2 A | B C | D E | F As you can see i want to display the columns as pairs with a custom column name. The pairs are Column A and B, column C and D and column C and D. ...

adding a tags field to an asp.net web page

I want to add a text field to a web page that lets users add space delimited tags. much like del.icio.us. I am more interested in how I get them off the page and into the database using VB.NET and SQL. Can anyone point me at any articles or code snippets on how I can achieve this using vb.net and sql 2005? ...

SQL query performance question (multiple sub-queries)

Hi, I have this query: SELECT p.id, r.status, r.title FROM page AS p INNER JOIN page_revision as r ON r.pageId = p.id AND ( r.id = (SELECT MAX(r2.id) from page_revision as r2 WHERE r2.pageId = r.pageId AND r2.status = 'active') OR r.id = (SELECT MAX(r2.id) from page_revision as r2 WHERE r2.pageId = r.pageId) ) Which...

SQL query filtering

Hey, Using SQL Server 2005, I have a table where certain events are being logged, and I need to create a query that returns only very specific results. There's an example below: Log: Log_ID | FB_ID | Date | Log_Name | Log_Type 7 | 4 | 2007/11/8 | Nina | Critical 6 | 4 | 2007/11/6 | John | Critical ...

How to convert an Access database to SQL?

Hi. I want to convert my Access database to SQL because my host only supports importing a database through SQL in their phpmyadmin. ...

Update Table By Pulling Information from Multiple Tables

I tried the statement as it is below and it didn't work. Query Analyzer returned a "(0) rows affected". The good news...it didn't blow up....the bad news....I don't know what is wrong with the statement. I think I am using SQL Server 2000. I haven't tried the statement yet on my demo database. The second statement is to update the...

Optimize query selecting a period

Given the following table: Table events id start_time end_time Is there a way to quickly search for a constant? E.g. SELECT * FROM events WHERE start_time<='2009-02-18 16:27:12' AND end_time>='2009-02-18 16:27:12' I am using MySQL. Having an index on either field still has to check a range. Moreover an index on both fields wi...

How do I join a select distinct to a select sum?

I am trying to join a select disinct statement to a select sum statement. Here is an example of the data CD STDATE ENDDATE PR F1 01/02/09 01/04/09 $10 F1 01/02/09 01/04/09 $40 F1 01/02/09 01/04/09 $20 F1 01/02/09 01/04/09 $30 F1 01/22/09 01/26/09 $10 F1 01/22/09 01/26/09 $50 F1 01/22/09 01/26/09 $20 My desi...

Polymorphism in SQL database tables?

I currently have multiple tables in my database which consist of the same 'basic fields' like: name character varying(100), description text, url character varying(255) But I have multiple specializations of that basic table, which is for example that tv_series has the fields season, episode, airing, while the movies table has release_...

PHP Date-Dependent Pagination

Hi Again, I have a site that stores a bunch of records in an sql database that i want to pull onto a page based on the date, which is stored as Y-m-d in sql. But, I want to paginate the results based on day. So for the index, i want to show all the results from the current day, for which i was going to use the php date() function as th...

many-to-many into summary column

I've got a db like this: Accounts -------- id BankName AcctNumber Balance AccountGroups ------------- id GroupName JoinAccountsGroups ------------------ aid gid I'm trying to generate data like this: Bank AcctNum Balance Groups --------|--------------|----------|---------------- Citi 930938 400 P...

Dealing with dates in dd/mm/yyyy format

Hi there. I have a VB6 application which works with datetime values in SQL Server (which are obviously storing dates as mm/dd/yyyy). I need to represent these dates to the user as dd/mm/yyyy, read them in as dd/mm/yyyy, and then store them back into the database as the standard mm/dd/yyyy. This is the current code snippets I have whic...

Owner ID type database fields

Suppose you have these tables: RestaurantChains, Restaurants, MenuItems - with the obvious relations between them. Now, you have tables Comments and Ratings, which store the customer comments/ratings about chains, restaurants and menu items. What would be the best way to link these tables? The obvious solutions could be: Use columns O...

How can I change SQL data order from rows to columns?

I have a snippet of code that writes the data alphabetically from a database ACROSS 3 columns on a web page. Example: a result b result c result d result e result f result g result h result i result I need instead to display it alphabetically DOWN the columns, like this: a result d result g result b result e result ...

SQL Server 2005 LENfunction oddity

The following code: if (right(@eqlist,2) = ', ') set @eqlist = left(@eqlist,len(@eqlist)-2) produces different results (with the same data) on my production server than on my development server. The problem is that the LEN function is 'supposed' to trim off the trailing spaces before calculating the length of a string, but it doesn...

missing keyword error

Hi I am getting this error when trying to create this table. Can you please tell me how I can use on update cascade and on delete set null constraints. SQL> create table emp3 2 ( 3 Fname varchar2(15)not null, 4 Minit char, 5 Lname varchar(15) NOT NULL, 6 Ssn char(...

Pagination help in SQL

Hi, The below inner SELECT returns huge amount of rows (1000000+) and the outer SELECTs(alpha BETWEEN #startRec# AND #endRec#) is used for PAGINATION to display data with 25 in each page. Issue is:-This PAGINATION done below is very slow and slows the entire display of data.So could all please help me on doing this below pagination in ...

Aggregation with two Joins (MySQL)

Hi! I have one table called gallery. For each row in gallery there are several rows in the table picture. One picture belongs to one gallery. Then there is the table vote. There each row is an upvote or a downvote for a certain gallery. Here is the (simplified) structure: gallery ( gallery_id ) picture ( picture_id, picture_gallery_re...

Best practices for multithreaded processing of database records

I have a single process that queries a table for records where PROCESS_IND = 'N', does some processing, and then updates the PROCESS_IND to 'Y'. I'd like to allow for multiple instances of this process to run, but don't know what the best practices are for avoiding concurrency problems. Where should I start? ...

PHP way to execute SQL LIKE matching without a database query?

I want to match an input string to my PHP page the same way a match done by the LIKE command in SQL (MySQL) for consistency of other searches. Since (I have seen but don't comprehend) some of the PHP syntax includes SQL commands I am wondering if this is possible? The reason for this is I am now implementing a search of a keyword versu...