sql

Memcached/Microsoft Velocity Performance Question

Just a random query regarding Microsoft Velocity. Scenario: Say I want ALL Orders from my database. In SQL, this is fine, I can do SELECT OrderId,TotalCost... from Orders. This is one round trip to my database, and everyone is happy. Now, if I'm using Memcached or (as I'm using now) Microsoft Velocity (CTP3), there is no easy way to do...

sql query tree like structure

I have a tree like structure of Categories, the leaf Nodes in the tree have Products and the products have Cods I need to select all the top level Categories (parent=null) that have leafs (Cods) that match some critaria... SELECT Category.Id AS Id0_, Category.Name AS Name0_, Category.COrder AS COrder0_, Category.Des...

PL/SQL to Select a Partition Window of Data

Working with Oracle 11g here. I'm trying to figure out how to write a specific query against a sample table below: ID TYPE PRIORITY STATUS DATE ---------------------------------------------------- 1 Q A NEW 01-OCT-2009 1 Q A NEW 01-OCT-2009 1 Q A NEW 01-OCT-2009...

Using "WITH AS" keywords when migrating data in SQL...

Is it possible to do this in SQL? If I remove the INSERT statement the SELECT works, if I have the insert Oracle complains that "missing SELECT keyword". WITH tmpdata AS ( //SOME arbitrary select statement ) INSERT INTO myTable (someId, somevalue, someothervalue) SELECT mysequence.nextval, tmpData.somevalue, tmpData.someothervalue,...

sql join query question (mysql)

I have the following query, its only returning two records when i have three in the database- where am I going wrong? SELECT items.id, items.link, items.title, items.image, lists.user FROM lists, items WHERE lists.user = '506161637' AND lists.item = items.id GROUP BY lists.dated DESC LIMIT 0, 10000; The data set of a join table is b...

How can I join in data from a key : value meta table in mysql?

Say I have three tables in my database: CREATE TABLE `users` ( `user_id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY , `username` VARCHAR(16) NOT NULL ); CREATE TABLE `users_meta` ( `meta_id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY , `user_id` INT NOT NULL , `key` VARCHAR(200) NOT NULL , `value` TEXT NOT NULL ); CREATE TABLE `posts` ( `po...

Swapping two DB rows without violating constraints

I have a table regionkey: areaid -- primary key, int region -- char(4) locale -- char(4) The entire rest of the database is foreign-keyed to areaid. In this table there is an index on (region, locale) with a unique constraint. The problem is that I have two records: 101 MICH DETR 102 ILLI CHIC And I need to swap the (r...

TSQL: How to get a list of groups that a user belongs to in Active Diretory

I have two queries that retrieve all groups and all users in a domain, Mydomain --; Get all groups in domain MyDomain select * from OpenQuery(ADSI, ' SELECT samaccountname,mail,sn,name, cn, objectCategory FROM ''LDAP://Mydomain/CN=users,DC=Mydomain,DC=com'' WHERE objectCategory=''group'' ORDER BY cn ') --; Get all users in dom...

SQL: Column count doesn't match row count

The following query: INSERT INTO term_node( nid, vid, tid ) VALUES ( ( SELECT ctb.nid, ctb.vid, 35 FROM content_type_bout AS ctb WHERE field_school_value_c = 'Lafayette' ) ) Is producing this error: #1136 - Column count doesn't match value count at row 1 term_node has only three columns in it. What am I doing...

SQL query problem with AND

I have a table that holds topic types another that holds materials. I then have another table which holds the keys of each table thus creating the many to many relation. However, when I try to search the topics to pick out two topics which have share the same material it doesn't work. Example tables: Material Table: MatID | Name ---...

Complicated SQL query - how do i do it ?

Hey Everyone I'm currently working on a small feature for a project and wondered how best to achieve the result, i have a table full of reviews with the score being out of 5 and being stored as score in the database, on one page i want to show how many of each score reviews there are, ie number of 5 star reviews, 4 star etc But i don't...

MAX on columns generated by SUM and GROUP BY

I'm trying to get the MAX on a column which is generated dynamically using the SUM statement. The SUM statement is used together with the 'GROUP by' syntax. This is the original query, however it needs to be modified to work with grouping, sums and of course MAX. SELECT SUM(video_plays) AS total_video_plays FROM `video_statistics`...

T-SQL Output Clause: How to access the old Identity ID

Hi There, I have a T-SQL statement that basically does an insert and OUTPUTs some of the inserted values to a table variable for later processing. Is there a way for me to store the old Identity ID of the selected records into my table variable. If I use the code below, I get "The multi-part identifier "a.ID" could not be bound." error...

SQL Server 2005- Add area code to records shorter than 10 characters

Can anyone come up with a SQL routine or ideas to systematically append an area code to the beginning of every field in our SQL Server 2005 database that does not have an area code in it? Perhaps someone could tell us how to return only rows that are less than 10 characters long and how to append the 3 digit area code to the beginning ...

Oracle: how to set user password unexpire?

There is some construction ALTER USER scott PASSWORD EXPIRE But how can I similair set password to unexpired state? ...

Search if a string word exists between two different tables in a comma-delimited field

I have two tables: EmployeeTypeA table Name varchar(2000) field contains - 'john,sam,doug' EmployeeTypeB table Name varchar(2000) field contains - 'eric,sam,allen,stephanie' What is the most efficient way to return a true or false when a name is found in both lists using MS SQL? This needs to be done within a stored procedure so I ca...

Change case of a column name

I have a table in my warehouse that has columns in all uppercase. It scripts out like this: CREATE TABLE [dbo].[Outlet]( [OUTLET_KEY] [varchar](10) NOT NULL, [OUTLET] [varchar](8) NULL, [CITY_CODE] [varchar](4) NULL, [OUTLET_NAME] [varchar](25) NULL, [COUNTRY_CODE] [varchar](3) NULL, [EXTENDED_CATEGORY_CODE] [var...

Can i force mysql to perform subquery first?

I have query like this: SELECT `table_1`.* from `table_1` INNER JOIN `table_2` [...] INNER JOIN `table_3` [...] WHERE `table_1`.`id` IN( SELECT `id` FROM [...] ) AND [more conditions] When I use EXPLAIN, there is 'DEPENDENT SUBQUERY' at the end, but I want this subquery to be performed first, before other conditions. Is is poss...

Method of simulating views on a SQL Server with read-only access?

I'm trying to do quite a lot of querying against a Microsoft SQL Server that I only have read only access to. My queries are needing to work with the data in very different structure from how the DB architecture is. Since I only have read access, I can't create views, so I'm looking for a solution. What I'm currently doing is using com...

How do I define a work week in SQL Server 2000?

I need to split a report by work week, and our work week is Saturday through Friday. How would I convert an ISO week from DATEPART(WW, ) into a work week? ...