query

Filter SQL query by a unique set of column values, regardless of their order

I have a table in Oracle containing two columns that I'd like to query for records containing a unique combination of values, regardless of the order of those values. For example, if I have the following table: create table RELATIONSHIPS ( PERSON_1 number not null, PERSON_2 number not null, RELATIONSHIP number not null, ...

SQL WHERE excluding records using AND and NOT

Here's a query I'm working on: SELECT TBL_SUB_KEY AS port , poe.[TBL_COMPANY] , poe.[TBL_DIVISION_1] FROM dbo.TMVKTAB AS poe WHERE ( TBL_NUMBER = '8A' ) AND ( TBL_SUB_KEY <> '' ) AND ( poe.[TBL_COMPANY] <> '011' AND poe.[TBL_DIVISION_1] <> '11' ) What I want returned are all ...

solr query with ruby - need to compare date time

My Lucene/Solr database contains a date column (created_at) that I need to use as a condition in a query. I'm new to RoR and assume that RoR automatically uses its own date object upon anyObject.save, and that Solr in turn reindexes that column in its own way. Regardless, the date is in this format: "2008-06-03 11:15:20" I can write ...

Delphi & shared datasources

In my app I have different forms that use the same datasource (so the queries are the same too), defined in a common datamodule. Question is, is there a way to know how many times did I open a specific query? By being able to do this, I could avoid close that query without closing it "every where else". Edit: It's important to mention t...

the multi-part identifier could not be bound

select (case when t.freeplayabandoned != f.freeplayabandoned then 'freeplayabandoned' when t.freeplaydownloaded != f.freeplaydownloaded then 'freeplaydownloaded' end) from testtable t where not exists (select * from freeplay.egmfreeplay f where f.freeplaydownloaded = t.freeplaydownloaded) I was trying to get the col...

how do i get the column name and or value?

select t.slotmachinebk, t.gamingdate, t.freeplaydownloaded, t.freeplayadjusted, t.freeplayplayed, t.freeplayabandoned, t.freeplaybalance from (select * from freeplay.egmfreeplay union all select * from Change.EgmFreePlay) t where not exists (select * from testtable where slotmachinebk = t.slotmachinebk and auditdate = t.gamingda...

Tool to convert T-SQL to graphical model?

Sometimes I am looking at complex SQL Server SQL statements and wondered if there's a tool which can represent the query into a graphical model. For example: You have a select query which joins (could be inner + left and right joins) with 10 tables. Is there a tool to take this query, represent the 10 tables graphically and draw the di...

SQL query to return one single record for each unique value in a column

I have a table in SQL Server 2000 that I am trying to query in a specific way. The best way to show this is with example data. Behold, [Addresses]: Name Street City State -------------------------------------------------------- Bob 123 Fake Street Peoria IL Bob 234 Other...

What would cause my MySQL query to hang after two results?

Hi, I have a simple query that I can't for the life of my get to print out on my page: $results = mysql_query("SELECT * FROM andyasks ORDER BY date"); $test = mysql_fetch_array($results, MYSQL_BOTH); foreach ($test as $row){ print($row[questions]); } What this outputs is (unpredictably, to my eyes) just the first letter of each ta...

Safely normalizing data via SQL query

Suppose I have a table of customers: CREATE TABLE customers ( customer_number INTEGER, customer_name VARCHAR(...), customer_address VARCHAR(...) ) This table does not have a primary key. However, customer_name and customer_address should be unique for any given customer_number. It is not uncommon for this table to con...

How to construct a hibernate query that uses a single element of all elements in a collection?

Here's the situation: I have a datatype C that has a one-to-many reference to Datatype P. Datatype P has an optional R reference (one to one). My attempted query was as follows (it was a count, but it will also be used to pull data) FROM C WHERE ... AND P.R.rProperty LIKE 'BLAH%'; I get a org.hibernate.QueryException: illegal atte...

Standards for queries over SOAP

Is there a standards-sanctioned XML format for describing entity queries? Background: I plan to build a library for writing queries over WCF services. On the client I want to be able to write (C#): var customers = from c in service.Customers where c.Name.StartsWith("P") order by c.Name ...

Why is my Update command updating all of the fields that have the same ID?

Using the update command, I want to change the type_name for a specific entry in the database, but it is changing the type_name for all the entries with the same type_id. I need it to only change that individual entry's type_name, and not the type_name that is associated with all the entries with the same type_id. I have an update quer...

MySQL multi-level parent selection/join question

Hi, I have a hopefully simple MySQL query question which is eluding me at late at night. I'm trying to do a SELECT which counts the number of instances of a set of data (orders) and groups those instances by a value which exists in a parent a couple levels above the order itself. For example: CREATE TABLE `so_test`.`categories` ( ...

How to select an item, the one below and the one above in MYSQL.

I have a database with ID's that are non-integers like this: b01 b02 b03 d01 d02 d03 d04 s01 s02 s03 s04 s05 etc. The letters represent the type of product, the numbers the next one in that group. I'd like to be able to select an ID, say d01, and get b05, d01, d02 back. How do I do this in MYSQL? ...

SQL select: two-dimensional select with variable column count

CREATE TABLE activities(activityid, name); CREATE TABLE activity_scores(activityid, studentid, score); CREATE TABLE students (id, name); Is there any way to write a single SELECT query that will produce one result for each student, in this format? studentid | studentname | activity1_score | activity2_score | activity3_score [...] ...

Django: Retrieve active threads by the time the last Post was created

Hallo people, I have two Models, Thread and Post, where one Thread can have many Posts. I want to retrieve the active threads by sorting by 'post_set.createtime'. In the end, I want to get exactly ten Threads that had the most recent activity. Is this possible using no own SQL? Thanks a lot in advance. [Copying the model definitions f...

Complex(?) SQL join query

I have 2 tables: 1) table Masterdates which contains all dates since Jan 1, 1900 2) table Stockdata which contains stock data in the form date, symbol, open, high, low, close, volume (primary key = date, symbol) This is what I'm looking to retrieve (presented here in CSV format) MDate,SDate,Symbol,Open,High,... 6/4/2001,6/4/2001,...

Complex DataMapper query association

Hello, im a beginner with DataMapper ORM, so i have question about complex querying. First, here is simplified data objects: class User property :id, Serial property :login, String has n, :actions end class Item property :id, Serial property :title has n, :actions has n, :users, :through => :actions end ...

mysql query with like %..% in the where clause returning different results

I have the following problem (using mysql 5.0.70): In one table I have a varchar field containing some kind of a number - "0303A342", "21534463", "35663CE3", etc. Collation is set to utf8_general_ci. The problem shows up when a user of the system is trying to search for a record containing part of this number. SQL query looks like "...W...