sql

Best Practice to querying a Lookup table

I am trying to figure out a way to query a property feature lookup table. I have a property table that contains rental property information (address, rent, deposit, # of bedrooms, etc.) along with another table (Property_Feature) that represents the features of this property (pool, air conditioning, laundry on-site, etc.). The features ...

Database VIEW does not reflect the data in the underying TABLE

Input: The customer claims that the application (.NET) when querying for some data returns data different from when the customer looks into the data table directly. I understand there could be various reasons and in completely different places. My purpose is not to solve it here, but rather to ask experienced DBAs and DB developers if:...

ASP.NET web app can't use multiple impersonation for authenication

I have a asp.net app (uses windows authentication for access) which (stipulated by the security team) needs to connect to a remote SQL Server 2005 using integrated security.Because of the fact that it is remote SQL server I needed to impersonate a custom account (impersonating the original caller would not work) via : <identity imperso...

Is SQL IN bad for performance?

I have a query doing something like: SELECT FieldX, FieldY FROM A WHERE FieldW IN (108, 109, 113, 138, 146, 160, 307, 314, 370, 371, 441, 454 ,457, 458, 479, 480, 485, 488, 490, 492, 519, 523, 525, 534, 539, 543, 546, 547, 550, 564, 573, 629, 642, 643, 649, 650, 651, 694, 698, 699, 761, 762, 768, 772, 773, 774, 775, 778, 784, 843, 844, ...

multiple table select using many where clauses, is there a better way than self-join?

I've got a bunch of tables that I'm joining using a unique item id. The majority of the where clause conditions will be built programatically from a user sumbitted form (search box) and multiple conditions will often be tested against the same table, in this case item tags. My experience with SQL is minimal, but I understand the basics....

Null value returned on Count Distinct (pl sql)

Preemptive apologies for the nonsensical table/column names on these queries. If you've ever worked with the DB backend of Remedy, you'll understand. I'm having a problem where a Count Distinct is returning a null value, when I suspect the actual value should be somewhere in the 20's (23, I believe). Below is a series of queries and t...

Help with Correlated Queries in SQL

Please help!! I am brand new to SQL programming and am teaching myself everything as I go along. I’ve finally run into a problem I can’t seem to tackle. Hopefully someone can help. I have the following tables. What I need to do is use the RECDATE in FROISEDI and count all the distinct dates (I4C7DZ) prior to and including that date ...

DCount vs. SQL SELECT COUNT(*)?

I am trying to get a count of all items sent to a supplier based on the purchase order they are assigned to. But I can't seem to get the control to show a number of items based on the purchase order instance - it keeps throwing either a #name? or #error! message in the text box when the form is activated. Create statement for tblPODeta...

Powershell SQLCMD

We were experiencing problems with Powershell and SQLCMD, when there was sapces in the -v parameter variable powershell wouldn't run the command. e.g. sqlcmd ... -v VAR="Some space" Has anyone experienced this before or know how to fix the problem? Thanks, B ...

Query that works in SQL but not in PHP

Hello there, I am having trouble with an SQL query that I have inserted into a piece of PHP code to retrieve some data. The query itself works perfectly within SQL, but when I use it within my PHP script it says "Error in Query" then recites the entire SQL statement. If I copy and paste the SQL statement from the error message directl...

How to construct this query in oracle?

Got table ABC with one column. A date column "created". So sample values are like; created 2009-06-18 13:56:00 2009-06-18 12:56:00 2009-06-17 14:02:00 2009-06-17 13:12:23 2009-06-16 10:02:10 I want to write a query so that the results are: count created 2 2009-06-18 2 2009-06-17 1 2009-06-16 Basically ...

MS SQL Server 2005 - Stored Procedure "Spontaneously Breaks"

A client has reported repeated instances of Very strange behaviour when executing a stored procedure. They have code which runs off a cached transposition of a volatile dataset. A stored proc was written to reprocess the dataset on demand if: 1. The dataset had changed since the last reprocessing 2. The datset has been unchanged for 5 ...

Count the number of rows in each group

Using CodeIgniter's Active Record class and MySQL, I have a table of posts with user_id and various other fields, and I want to count how many posts each user has made. I could get rows where user_id = $whatever and count the number of results, but I'd have to loop through every user_id and use that count_all_results() query over and ove...

Build dynamic menu using Nested Sets

I am trying to build a dynamic menu in my PHP CMS; the pages/categories are organized using the nested sets model. Full tree: root A B B1 B1.1 B1.2 B2 B2.1 B2.1 C C1 C2 C3 D I want to convert this result set to an unordererd list, which only displays a part of the tree. For example: If I click on B, I want...

Is there a way to force PHP into waiting until MySQL is finished with a transaction?

I'll admit I'm not 100 % on the inner workings of PDO and MySQL, so I'll give an example to make my question clearer. I'm making a rather crude browser based strategy game, because I think it's a fun way to learn PHP and databases. I was bug-testing the battle script when I came across a rather unexpected bug. I use Cron Jobs to call a ...

SQL - SELECT MAX() and accompanying field

What I have is basically a problem which is easily solved with multiple tables, but I have only a single table to do it. Consider the following database table UserID UserName EmailAddress Source 3K3S9 Ben ben@myisp.com user SF13F Harry lharry_x@hotbail.com 3rd_party SF13F Harry reside@domain.com user 76D...

Counting non-null columns in a rather strange way

I have a table which has 32 columns. 2 of these columns are identity columns and the rest are values. I would like to get the average of all these 30 columns, excluding the null ones. If none of the columns were null, that would have been pretty easy as follows select (val0 + val1 + val2 + ...) / 30 Since I would like to exclude the nu...

How to specify which columns can be returned from linq to sql query

I'm trying to only return a few columns from a linq to sql query but if I do, it throws the exception: Explicit construction of entity type 'InVision.Data.Employee' in query is not allowed Here's the code: return db.Employees.Select(e => new Employee() { EmployeeID = e.EmployeeID, FirstName = e.FirstName, LastName = e....

How would one represent scheduled events in an RDBMS?

I have to store scheduled events, (like say class times, for example) that can be organized on a weekly, daily or monthly basis. Events can occur, say, every Monday and Wednesday, or every second Thursday of the month. Is there a way to store this information in an RDBMS that adheres to 3NF? EDIT: This is not homework; I'm building some...

Mysterious CPU Activity in MS SQL 2005

I am looking at the activity monitor in MS SQL Management Studio 2005, and I see an entry from a login that I created, and it's using the tempdb, and the last command is always "SELECT INTO". And everytime I hit "Refresh" in activity monitor, that entry's "CPU" column goes up by like 60. The "program" of that activity is "MS SQL Manageme...