Can you all please help me with this? Presently, I have this SELECT which returns data ordered by this way SELECT DISTINCT gl.group_id, gl.group_name, gl.group_description, gl.status_code, gl.member_count, ( SELECT grpp.group_name FROM test_group_relationship grel JO...
Using .NET's DbConnection.GetSchema(), how do I find the owner of a given database? Alternatively, if you have another solution that is not coupled to a specific impelementation of SQL, I'd like to hear that as well. ...
Exact Duplicate Table Naming Dilemma: Singular vs. Plural Names Is it better to use singular or plural database table names ? Is there an accepted standard ? I've heard arguments for and against it, what do you guys think ? ...
Given these two queries: Select t1.id, t2.companyName from table1 t1 INNER JOIN table2 t2 on t2.id = t1.fkId WHERE t2.aField <> 'C' OR: Select t1.id, t2.companyName from table1 t1 INNER JOIN table2 t2 on t2.id = t1.fkId and t2.aField <> 'C' Is there a demonstrable difference between the two? Seems to me that the clause ...
Sorry for a long question and not a very descriptive title, but my problem is very difficult to explain briefly. I have three database tables: TABLE A: AID PK STATUS VARCHAR TABLE B: BID PK AID FK CID FK TABLE C: CID PK CREATIONTIME DATE For each STATUS = 'OK' row in table A I want to find the corresponding row in C w...
I have a statement similar to this that I need to run in Linq. I started down the road of using .Contains, and can get the first tier to work, but cannot seem to grasp what I need to do to get additional tiers. Here's the SQL statement: select * from aTable where aTableId in (select aTableId from bTable where bTableId in ...
I'm using nested sets (aka modified preorder tree traversal) to store a list of groups, and I'm trying to find a quick way to generate breadcrumbs (as a string, not a table) for ALL of the groups at once. My data is also stored using the adjacency list model (there are triggers to keep the two in sync). So for example: ID Name Par...
I am trying to delete the all but the most recent 3,000 items in a table. The table has 105,000 records. I am trying this, but an error is generated incorrect syntax. delete tRealtyTrac where creation in( select top 103000 from tRealtyTrac order by creation) ...
Is there any advantage of using int vs varbinary for storing bit masks in terms of performance or flexibility. For my purposes, I will always be doing reads on these bit masks (no writes or updates). ...
Writing Java in Eclipse, I've recently been bitten by a few trivial bugs where my SQL syntax has been off (e.g., not properly quoting a constant). Are there any tools I could use to help myself catch these as I write them? ...
I have a table of locations with latitude, longitude and the U.S. state fields. I would like to select the average latitude and longitude for each state. I am trying the following code but I get a syntax error on distinct. select avg(lat), avg(lon), distinct(state) from tRealtyTrac order by state group by state ...
Say for instance I have a table which has names of people and their ages. I want a generic query in MySQL which will pull out the n oldest people. I could use something like this: SELECT * FROM people ORDER BY age DESC LIMIT 1; Say Frank and Emily are both 99 years old. The limit 1 will only return one of their names. In psuedo-sql I...
I have a stored procedure in SQL Server 2000 that performs a search based on parameter values. For one of the parameters passed in, I need a different WHERE clause depending on its value - the problem is that the 3 values would be where MyColumn IS NULL IS NOT NULL ANY VALUE (NULL AND NOT NULL) (essentially no WHERE clause) I'm hav...
I need to display a grid on a webpage. The data will come from SQL Server 2008 through a stored procedure. As the sproc returns thousands of records, I decided to go for a paging option which works fine. In the stored procedure, I do something like this: declare @RowIdMin int=10 declare @RowIdMax int=25 select * from ( select C...
I'd like to run a query like the one below against an Oracle 9i database from Java (an example table structure and example data are below). SELECT deptno , SUBSTR(comma_list, 2) comma_list FROM (SELECT deptno , SYS_CONNECT_BY_PATH(ename, ',') comma_list , row_number , row_count ...
How many concurrent connections do the express editions allow? My front end uses standard ADO.Net code where I open the connection to the server, get my data, and then close the connection. Am I right in saying that as soon as the connection is closed, it then allows this connection to be opened by another user? ...
Although the actual schema is a bit more complex, the following should be a bit easier for me to get across. In one table I have a list of jobs: Job Status Open date Close date 1 Closed 04/29/2009 04/30/2009 2 Open 04/30/2009 3 Open 04/30/2009 ..and in another I have a list of notes associated wi...
I'm inserting a bunch of new rows into a table which is defined as follows: CREATE TABLE [sometable]( [id] [int] IDENTITY(1,1) NOT NULL, [someval] sometype NOT NULL ) using the following insert: insert into sometable select somefield as someval from othertable when I've finished, I'd like to know the IDs of all the newly in...
I have counter for unique column in my table. Now I have list where I have column name (which is unique) and counter (which contains some integer value). I know that it is possible to insert multiple lines, I just could make query containing all those items on the list, but problem is that if there is already data in unique field. In th...
I am creating as stored procedure that brings back a bunch of data that I need from multiple tables, however the tables share some duplicate column names. It works fine in SQL but I am wondering what will happen and how I will differentiate between them once I am accessing them as DataRows from a DataSet. Anyone know? ...