query

Write a SQL Query to replace values and include all the Dates.

Well I have this - Table DimDate- Date Table Employee- Id,Name,Points,Date Now the Employee table has points for everyday unless they did not come...so the Date does not have all the Dates entries... I mean for e.g in a week he did not come for 2 days the Employee table has only 5 rows...so I have this dimdate table which has all t...

Mysql query in drupal database - groupwise maximum with duplicate data

I'm working on a mysql query in a Drupal database that pulls together users and two different cck content types. I know people ask for help with groupwise maximum queries all the time... I've done my best but I need help. This is what I have so far: # the artists SELECT users.uid, users.name AS username, n1.title AS artist_...

MySQL comparisons between multiple rows

I have a MySQL table with the following columns: id(int), date (timestamp), starttime(varchar), endtime(varchar), ... I need to find time slots that are occupied by two or more rows. Here is an example table id| date |starttime|endtime | __|_____________________|_________|________| 1 | 2010-02-16 17:37:36 |14:35:00 |17:...

Hierarchical SQL query not returning level

I have a typical SQL Server hierarchical query: WITH bhp AS ( SELECT name, 0 AS level FROM dbo.BhpNode WHERE parent_id IS NULL UNION ALL SELECT a.name, level + 1 FROM dbo.BhpNode a INNER JOIN dbo.BhpNode b ON b.bhp_node_id = a.parent_id ) SELECT * FROM bhp This seems to match the various examples of hierar...

Get consolidated results with following tables

I have a scenario. Here's my table structure is: ID LoginDate RemovalDate ---------------------------------------- 1 2009/08/01 NULL 2 2009/09/12 2010/01/02 3 2009/08/31 2009/10/29 4 2010/02/17 NULL 5 2009/10/18 ...

Trouble with CTE

I am learning CTE, and tried out the following query WITH fooCTE AS ( SELECT TOP 5 f.bar FROM foobar f ) But it's displaying an error which is quite incomprehensible. Msg 102, Level 15, State 1, Line 5 Incorrect syntax near ')'. Infact when I run the query below, I get the top five values being displayed. SELECT TOP 5 f.bar FROM...

Best way to store data in database when you don't know the type

I have a table in my database that represents datafields in a custom form. The DataField gives some representation of what kind of control it should be represented with, and what value type it should take. Simplified you can say that I have 2 entities in this table - Textbox taking any string and Textbox only taking numbers. Now I have...

How to avoid Cartesian product in an INNER JOIN query?

I have 6 tables, let's call them a,b,c,d,e,f. Now I want to search all the colums (except the ID columns) of all tables for a certain word, let's say 'Joe'. What I did was, I made INNER JOINS over all the tables and then used LIKE to search the columns. INNER JOIN ... ON INNER JOIN ... ON.......etc. WHERE a.firstname ~* 'Joe' OR a.las...

adding count( ) column on each row

I'm not sure if this is even a good question or not. I have a complex query with lot's of unions that searches multiple tables for a certain keyword (user input). All tables in which there is searched are related to the table book. There is paging on the resultset using LIMIT, so there's always a maximum of 10 results that get withdraw...

comparying 2 array's with NSPredicate (cocoa)

i have an NSObject with 2 property's @interface Entity : NSObject { NSNumber *nid; NSString *title; } i have 2 array's with Entity's objects in it and I want to compare those two on the nid with a predicate array1: ({nid=1,title="test"},{nid=2,title="test2"}) array2: ({nid=2,title="test2"},{nid=3,title="test3"}) the 2...

Oracle Query for getting CURRENT CTC (Salary) of Each Employee

i want current CTC of each employee following is the design of my table Ecode Implemented Date Salary 7654323 2010-05-20 350000 7654322 2010-05-17 250000 7654321 2003-04-01 350000 7654321 2004-04-01 450000 7654321 2005-04-01 ...

Display PHP Query Array in the table

Hi all I would like to display my Query from Mysql on the table. However, my data is like this: Array([0]=>data1, [1]=>data2, [2]=>data3,[3]=>data4,[4]=>pic1,[5]=>pic2,[6]=>pic3,[7]=>pic4); I want to display my table as |data1 | data2 |data3 |data4 |pic1 | pic2 |pic3 |pic4 I know how to display the data in the single ling like...

complex Subtract in sql server query

Hello, I have table like following PK num1 num2 numsdiff 1 10 15 ? 2 20 25 ? 3 30 35 ? 4 40 45 ? i need to get Subtract of 20 - 15 and 30 - 25 and 40 - 35 and so on by select query from this table. any ideas?. thanks ...

PHP SQL: Matching corresponding Data between 2 tables, without Filtering out Data in 1st Table

I have 2 tables. For simplicities sake 'u' has the following columns userid divisionid 'd' has the following: divisionid name I did not create this table, otherwise I would not have this problem. u.DIVISION can be NULL. d.DIVISION cannot. Running the following creates the appropriate data, but it also filters out every single userid t...

SELECT product from subclass: How many queries do I need?

I am building a database similar to the one described here where I have products of different type, each type with its own attributes. I report a short version for convenience product_type ============ product_type_id INT product_type_name VARCHAR product ======= product_id INT product_name VARCHAR product_type_id INT -> Foreign key t...

MySQLi String comparisons using keys

I have a table with lets say 2 columns. id number, and value. Value is a string (var char). Lets say i have a number x, and a list of numbers a1, a2, a3, a4, a5..... where x is not in the list. All of these numbers correspond to a unique row in the table. I want to know if the string value for x in the table is contained in one of the s...

SQLite table creation date

Is there a way to query the creation date of a table in SQLite? I am new to SQL, overall. I just found this http://stackoverflow.com/questions/1171019/sql-server-table-creation-date-query. I am assuming that sqlite_master is the equivalent to sys.tables in SQLite. Is that correct? But then my sqlite_master table only has the columns "typ...

Select distinct users with referrals

I have a bunch of Users. Since Django doesn't really let me extend the default User model, they each have Profiles. The Profiles have a referred_by field (a FK to User). I'm trying to get a list of Users with >= 1 referral. Here's what I've got so far Profile.objects.filter(referred_by__isnull=False).values_list('referred_by', flat=Tr...

Fetch posts with attachments in a certain category?

I need to retreive a list of posts that have (at least) one attachment that belongs to a category in WordPress. The relation between attachments and categories I made by myself using the WordPress default method. Here's the query that i'm running right now: SELECT post.* FROM `bma_posts` AS post WHERE EXISTS ( SELECT 1 ...

Is there a way to simplify this Django query?

accepted_bids = Bid.objects.filter(shipment__user=u, status='acc').select_related('shipment') completed_shipments = [] for b in accepted_bids: completed_shipments.append(b.shipment) vehicles_shipped = [] for s in completed_shipments: vehicles_shipped.extend(s.items.all()) In the end, I want a list of shipped vehicles. A vehicle...