query

Make a query Count() return 0 instead of empty

I have a report that tracks how long certain items have been in the database, and does so by tracking it over a series of age ranges (20-44, 45-60, 61-90, 91-180, 180+). I have the following query as the data source of the report: SELECT DISTINCT Source.ItemName, Count(SELECT Source.DateAdded FROM Source WHERE Int(Date()-Source.DateAd...

Update Multiple Rows Mysql from php -- Slight Twist

Hi all, I asked a similar question yesterday, but now I have a slight twist. Since I already gave credit to an answer, I've decided to create a new question. Here's what I'm trying to do: Select an arbitrary number of rows, but placing a limit on the number of selected rows of one field. $query = "SELECT test_id, field1, field2 FROM...

SQL Design - Accounting for unknown values

Ok, this gets a little tricky. Here goes. Say I have a table called HoursCharged ChrgNum(varchar(10)) CategoryID(uniqueidentifier) Month(datetime) Hours(int) CategoryID is a foreign key reference to another table in my database, which is just a name/ID pairing. ChrgNum is guaranteed to be unique outside of this database, a...

Mysql Case returns wrong value

On one server I use mysql version 5.0.45 and when running the following query it returns 1 if boolvalue='True' AND 2.2 else, just as it should. SELECT Q.value FROM ( SELECT (CASE table.boolvalue='True' WHEN 1 THEN 1 ELSE 2.2 END) AS value FROM table ) Q; On another server (webhosting) I use mysql version 5.0.22 and when running the sa...

Is there a better way to write this SQL query?

I'm looking to return the row with biggest create_dt. This works fine, however I would like to know if there is a more proper way of doing this? select * from table1 where job_no='101047' and create_dt in (select max(create_dt) from table1 where job_no='101047') ...

SQL query for Top 5 of every category

Hi there, I have a table that has three columns: Category, Timestamp and Value. What I want is a SQL select that will give me the 5 most recent values of each category. How would I go about and do that? I tried this: select a."Category", b."Timestamp", b."Value" from (select "Category" from "Table" group by "Category" order by ...

Advanced SQL SELECT

Hi, I have basic tables one and two. Let`s call them tbl1 and tbl2. tbl1 --------- 1 - apple | 2 - orange | 3 - banana | 4 - pineapple tbl2 --------- 1 - table | 2 - chair | 3 - sofa | Then there is tbl3 that has foreign keys that link it to both tables above. Table 3 form has two select fields: one that queries tbl1 and another tha...

Recursive Query using HQL

Hi every one, I have this Table CREATE TABLE IF NOT EXISTS `branch` ( `id` int(11) NOT NULL AUTO_INCREMENT, `studcount` int(11) DEFAULT NULL, `username` varchar(64) NOT NULL, `branch_fk` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `FKADAF25A2A445F1AF` (`branch_fk`), ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=14...

Storing data and searching by metadata?

Let's say I have a set of data where each row is a pair of coordinates: (X, Y). Associated with each point I have arbitrary metadata, such as {color: yellow} or {age: 2 years}. I'd like to be able to store the data and metadata in such a way that I can query the metadata (eg: [rows where {age: 2 years, color: yellow}]) and in return rec...

MS Access 03 - Query Expression to Add like ID Numbers

So I have a query that is a Top Nth aggregate query, and I have another query built from that one that returns all the offices/locations grouped for each of the top sales. I want to make a report that counts the number of offices associated with each of these top Nth ID values that are returned in this query. I want to use a domain aggre...

SPARQL Query and distinct count

hi all, i have the following query: SELECT ?tag WHERE { ?r ns9:taggedWithTag ?tagresource. ?tagresource ns9:name ?tag } LIMIT 5000 and the results are: abc abc abc abc abc abc abc abd ads anb I want to get somthing like tag | count ----------------- abc 7 abd 1 ads 1 anb 1 I have tried it with count(*) and count...

help with oracle sql rollup

Hi folks, I've written a query to collect some data to display in an auto-updating box & whisker graph in excel. I'd like to use rollup to create summary rows for each type of train_line (PF and MJ) to include in the excel chart. Can I do that using Rollup? I've tried to get my head around Rollup, but I'm not getting far. I've tried...

Filtering SPARQL Results

I have following example triples r1 -> property -> resourceA r1 -> property -> resourceB r1 -> property -> resourceC resourceA -> name -> word1 resourceB -> name -> word2 resourceC -> name -> word4 r2 -> property -> resourceD r2 -> property -> resourceE r2 -> property -> resourceF resourceD -> name -> word1 resourceE -> name -> word2 r...

SQL - Combine two tables for one output

Say I have two tables: KnownHours: ChargeNum CategoryID Month Hours 111111 1 2/1/09 10 111111 1 3/1/09 30 111111 1 4/1/09 50 222222 1 3/1/09 40 111111 2 4/1/09 50 UnknownHours: ChargeNum Month Hours 111111 2/1/09 ...

How do I get the parent category name in wordpress template? And can I query post by the parent category?

I tried getting help on the wordpress forums but no luck. Anyways, heres my question... Lets say i am creating 10 parent categories and 2 sub categories to each parent. My wordpress post belongs to one sub category of a particular parent category How do i get the parent category name ONLY? i don't want subcategories names? what wordpre...

Sql statement teaser...

ok so this is what I am trying to achieve is to have two SELECT statements and then join results of each so I have something like this SELECT table.ID, tst.Value FROM blah AS table JOIN results AS tst ON tst.RUNID = table.RUNID WHERE table.RUNID IN ( ...// nothing important but in the end i get my tst.Value ) second statement is ...

Mysql "order of operations" Question

Hi all, Here's my situation: I want to SELECT all entries from a database WHERE id = $id. But I want the results to be listed in a certain priority. If, criteria = $criteria, then I want those results displayed first. Otherwise, I just want to keep displaying the rest of the rows. My question is this: will this solve my problem?...

Recurring SQL Queries

What is considered best practice for executing recurring SQL queries? My understanding is to use a parameterized query and turn it into a prepared statement upon first execution. What if this query needs to be executed by multiple threads? Will I need to create a prepared statement for each type of query for each thread? Or is the par...

Using SQL statements to query in-memory objects

Suppose I have a collection of C++ objects in memory and would like to query them using an SQL statement. I’m willing to implement some type of interface to expose the objects’ properties like columns of a database row. Is there a library available to accomplish this? In essence, I’m trying to accomplish something like LINQ without usi...

SQL: selecting multiple rows per group

I have some records in my table tbl1. There is a column s, based on which I need to retrieve the rows where datateime1 is an old date. If I try the query shown below I seem to get correct results. Now I try to get the two oldest rows of the s1 group. I tried by adding 's1' twice to the IN clause, but that does not work, because the row...