subquery

Is there a way to give a subquery an alias in Oracle 11g SQL?

Is there a way to give a subquery in Oracle 11g an alias like: select * from (select client_ref_id, request from some_table where message_type = 1) abc, (select client_ref_id, response from some_table where message_type = 2) defg where abc.client_ref_id = def.client_ref_id; Otherwise is there a way to join the two subque...

IP address numbers in MySQL subquery

I have a problem with a subquery involving IPV4 addresses stored in MySQL (MySQL 5.0). The IP addresses are stored in two tables, both in network number format - e.g. the format output by MySQL's INET_ATON(). The first table ('events') contains lots of rows with IP addresses associated with them, the second table ('network_providers') c...

How to efficiently filter a collection of child objects by a property with NHibernate

I've been trying unsuccessfully to filter a collection of child objects for a few hours how and have finally thrown my hands up! I'm new to NHibernate and was hoping for a couple of pointers on this. I've tried various ICriteria etc. with no luck. I'm just not getting it. I have a parent object 'Post' with a collection of child objects ...

Help me turn a SUBQUERY into a JOIN

Two tables. emails id (int10) | ownership (int10) messages emailid (int10) indexed | message (mediumtext) Subquery (which is terrible in mysql). SELECT COUNT(*) FROM messages WHERE message LIKE '%word%' AND emailid IN (SELECT id FROM emails WHERE ownership = 32) The usage here is that I run a search on emails (which is obviously...

query for what customers have bought together with the listed product

hi guys, i'm trying to get optimize an very old query that i can't wrap my head around. the result that i want to archive is that i want to recommend the visitor on a web shop what other customers have shown interest in, i.e. what else they have bought together with the product that the visitor is looking at. i have a subquery but it's ...

Which is faster: JOIN with GROUP BY or a Subquery?

Let's say we have two tables: 'Car' and 'Part', with a joining table in 'Car_Part'. Say I want to see all cars that have a part 123 in them. I could do this: SELECT Car.Col1, Car.Col2, Car.Col3 FROM Car INNER JOIN Car_Part ON Car_Part.Car_Id = Car.Car_Id WHERE Car_Part.Part_Id = @part_to_look_for GROUP BY Car.Col1, Car.Col2, Car.Col3 ...

Select items that are the top N results for a related table

Say I have a game where a question is asked, people post responses which are scored, and the top 10 responses win. I have a SQL database that stores all of this information, so I might have tables such as Users, Questions, and Responses. The Responses table has foreign_keys user_id and question_id, and attribute total_score. Obviously, ...

Criteria query whith FROM clause which contains only one query and does not contain any tables

Hi all. I have the following query and I don't know how can I write it using criteria. SELECT questions_cnt, COUNT(*) users_cnt FROM ( SELECT COUNT(DISTINCT question) questions_cnt FROM UserAnswer GROUP BY user ) t GROUP BY questions_cnt I have started to write criteria query. It looks like the following final DetachedC...

T-SQL Nested Subquery

Not being a SQL expert, and also only being semi-competent in CTE, how can I code this statement use the resultset from the following subquery within the main query, as our SQL Server is 2000. declare @subcategoryConcatenate varchar(3999) set @subcategoryConcatenate = '' select @subcategoryConcatenate = @subcategoryConcatenate + pumpCa...

How to improve SQL Server query containing nested sub query

I was given this SQL query by my boss and told to improve/optimize it DECLARE @pol_0 int, @pol_1 int, @pol_2 int, @pol_3 int, @pol_4 int, @pol_5plus int, @peril_0 int, @peril_1 int, @peril_2 int, @peril_3 int, @peril_4 int, @peril_5plus int, @loc_1 int, @loc_2_10 int, @loc_11_100 int, @loc_101_1000 int, @loc_1001_5000 int, @loc_...

T-SQL Nested Subquery

I want to place this working code within a SQL Statement, OR do I need to perform a UDF. The result set is a one line concatenation, and I want it to be place in every one of the overall result set lines. ---- MAIN QUERY SELECT H.CONNECTION_ID, H.SEQUENTIAL_NO, H.INVOICE_NUMBER, H.INVOICE_DATE, H.LAST_INVOICE_NUMBER, ...

Execution of subquery within table class gives class not found [doctrine]

Hi there! In table class Ouders I do the following query: $q = $this->createQuery('o') ->where('o.ouderid IN (SELECT DISTINCT k.parentid FROM Kinderen k WHERE k.schoolid = ?)', $schoolcode) ->orderBy('o.achternaam'); As you can see it has a subquery to the table class Kinderen. Why does it give the error?: An error occurred...

Zend_Db subquery

Hi Everyone, I've been trying to construct a sql query with ZendFW, but I cant seem to get it to function like I want to (or function at all). This is the query that works that I'm trying to build with zend_db select() SELECT tc.trip_title, td.ID, td.trip_id, (SELECT count(*) FROM 'trips_invites' ti WHERE ti.destination_id=td.ID ...

Unknown column 'building_requirements_count' in 'field list'

I'm make a little game in php with mysql. Now I have a problem with one of the sql query's I created. The idea is that the query checks if the user has enough materials. I have a query that if I use it like this it works: SELECT ( SELECT COUNT(*) FROM building_requirements WHERE building_id = '1' ) as building_requirements_count...

NHibernate Subquery with OrderBy

Hi, I have 2 entities... Audio Tag These are setup as a many-to-many, Audio has a reference to Tags, but Tags doesn't have a reference back to Audio. What i want to do is return all tags matching a search query with a count of how many Audios for each tag, they also need to be ordered by the Tags with the most Audio items. Just to m...

How do I return a table in LINQ that relies on a join/subquery?

I need the fields in 1 table contingent on 1 property matching rows in another table. I can write this query in SQL with a subquery as such: SELECT * FROM Table1 WHERE Property1 IN ( SELECT Property1 FROM Table2 WHERE Property0 = 1 ) But I read here that it's less complicated and just as easy to write with a join, which I ...

Can I reorder SQL selections after the limit has been applied?

I'd like to see the last ten rows from our mysql database in ID order. Naively, I'd expect to be able to do something like this: SELECT * FROM ( SELECT * FROM things ORDER BY id DESC LIMIT 10) ORDER BY id ASC but that isn't valid syntax. What's the correct way of expressing the query I'm trying to run? ...

TSQL - Max or Top Date on an unnormalized table

I have the following table: Table: UserName Userid User UserUpdate 1 Dan 1/1/2005 1 Dan 1/1/2007 1 Dan 1/1/2009 2 Pam 1/1/2005 2 Pam 1/1/2006 2 Pam 1/1/2008 3 Sam 1/1/2008 3 Sam 1/1/200...

Retreive one row from 1 to many relationship between three tables

I have the following three tables : ----PRODUCT---- PRODUCT_ID DESC 1 'Pencil' 2 'Paper' ----PRICE_BY_SUPPLIER---- PRODUCT_ID SUPPLIER_ID PRICE 1 1 10 1 2 9 1 3 9.5 2 1 5 ...

Using SQL Server 2005 I'm trying to do a HAVING in a WHERE clause sound crazy?

Ok, I have over a million records and I need to select all information from the database where the count of a column is greater or equal to 10. I know that using having I must use group by and using where I cannot use aggregate functions with operators so what I want in pseudo code is Select column1,column2,column3 From MYdatabase Wher...