subselect

MySQL subselect performance question?

I need advice regarding subselect performance in MySQL. For a reason that I can't change, I am not able to use JOIN to create quesry filter, I can only add another AND clause in WHERE. What is the peformance of: select tasks.* from tasks where some criteria and task.project_id not in (select id from project where project.is_templa...

Compare subselect value with value in master select

In MS Access, I have a query where I want to use a column in the outer query as a condition in the inner query: SELECT P.FirstName, P.LastName, Count(A.attendance_date) AS CountOfattendance_date, First(A.attendance_date) AS FirstOfattendance_date, (SELECT COUNT (*) FROM(SELECT DISTINCT attendance_date ...

What should be considered using subselects in queries with haevy loaded databases?

We are developing an application with a persistence layer using OpenJPA1.1 and an Oracle DB as backend storage. I will use queries with subselects (see my question at Solving JPA query finding the last entry in connected list). Now my colleagues at work remark, that such queries can lead to performance problems as the database is fille...

How do I get a count of associated rows in a left join in MySQL?

I have two tables, a vehicle table with columns: id stock year make model and an images table with columns: id vehicle_id name caption default tinyint(1) I am trying to list the vehicle's information, its default image, and a total count of images the vehicle has. Currently I am using the following SELECT statement: SELECT vehi...

Hibernate for getting a list of primitive integers for subselect

Isn't there a way with Hibernate to return a list of (primitive) values from one column in a table? I need this for a subselect where I only want rows where a particular field is not in a list of ids from another table. ...

Mysql subselect alternative

Hi, I have a query that I know can be done using a subselect, but due to large table sizes (100k+ rows per table) I would like to find an alternative using a join. This is not a homework question, but it's easier to share an example in such terms. Suppose there are two tables: Students :id :name 1   Tom 2   Sally 3   Ben Books :id  ...

SQL: selective subqueries

Hi, I'm having an SQL query (MSSQLSERVER) where I add columns to the resultset using subselects: SELECT P.name, (select count(*) from cars C where C.type = 'sports') AS sportscars, (select count(*) from cars C where C.type = 'family') AS familycars, (select count(*) from cars C where C.type = 'business') AS businesscars FROM people P ...

Using a Oracle subselect to replace a CASE statement

Hy guys, can anybody please help me with a subquery in Oracle database 10g? I need to extract the values for a column in the first table as value of another column in the second table. I currently use this statement: SELECT CASE WHEN A.column1 = 'A' THEN 'aaa' WHEN A.column1 = 'B' THEN 'bbb' ....... WHEN A.column1 = 'X' THEN 'xxx' ELS...

How can I create multiple columns from one DB Field in SQL Server?

I have a field called PropertyValue in the UserProfile table that can contain information for address, phone number, first name, last name, user name, city, etc... each record in this table is associated to a user by UserId, it is also associated to a ProfilePropertyDefinition which contains the definition for each of the properties (ie....

How do you pass parameters to Hibernate's subselect tag?

The example at the end of hibernate section 5.1.3 does not show an example on passing parameters. There is no difference between a view and a base table for a Hibernate mapping. This is transparent at the database level, although some DBMS do not support views properly, especially with updates. Sometimes you want to use ...

Using joins correctly

I am looking for a tip on how to optimize this.... SELECT u.uid, u.username, u.nid, wd.pay FROM (users_data AS u LEFT JOIN winners_data wd ON u.nid = wd.nid AND u.uid = wd.uid) LEFT JOIN dp_node dn ON u.nid = dn.nid WHERE u.uid = ".$val." ...

How do you programmatically turn off eager fetching with hibernate?

I have in my mapping an association to an eagerly loaded collection (lazy="false" fetch="subselect"). How can I turn that off programmatically with Hibernate when I do a query? Thanks ...

No idea how to write complex SQL query

Hi I have following db structure: File, User, FileRevision (has foreign key to File, and many-2-many connection through intermediate table to User). I want to fetch all FileRevision-s that: are newest/freshest in their corresponding File-s, have many-2-many link to User that performs search (permission checking). I found out that...

How to insert a foreign key using a Sub-SELECT in SQL Server

I've just started working with SQL Server for the first time and I'm having trouble populating test data. I have two tables where one has a foreign key to the other and I would like to be able to insert a new record using the following SQL: insert into Employee ( EmployeeName, DepartmentId ) values ( "John Doe", (select ...

MySql variables and php

I am getting an error with this in php. What is the correct way to format this string to pass to mysql_query() in php? SELECT count(*) FROM agents INTO @AgentCount; SELECT user_agent_parsed, user_agent_original, COUNT( user_agent_parsed ) AS thecount, COUNT( * ) / ( @AgentCount) AS percentage FROM agents GROUP BY user_agent_parse...

SQL Server statement with subselect is invalid

Hey, I am using iBATIS with SQL Server Compact Edition 3.5 and try to do a subselect INSERT INTO FORMINSTANCE (ID, ID_FORM) SELECT #ID#, f.ID FROM FORM f WHERE ID_PROCESS='10804' When I commit the transaction I get an SqlCeException (SSCE_M_QP_PARAMETERNOTALLOWED). That the Symbol '@' is on the wrong place. I think this is ...

PostgreSQL SELECT the last order per customer per date range

In PostgreSQL: I have a Table that has 3 columns: CustomerNum, OrderNum, OrderDate. There may(or may not) be many orders for each customer per date range. What I am needing is the last OrderNum for each Customer that lies in the date range that is supplied. What I have been doing is getting a ResultSet of the customers and querying ...

MySQL: Usage of indices in UNION subselects

In MySQL 5.0.75-0ubuntu10.2 I've got a fixed table layout like that: Table parent with an id Table parent2 with an id Table children1 with a parentId CREATE TABLE `Parent` ( `id` int(11) NOT NULL auto_increment, `name` varchar(200) default NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB CREATE TABLE `Parent2` ( `id` int(11) NOT NUL...

How do I select min/max dates from table 2 based on date in table 1?

I have a monthly table (only holds rows with first day of month, and unique constraint so only one of each) and a daily table with similar information for each day (same deal, only one per day): Monthly table Daily table ------------- ----------- 2009-01-01 2009-01-01 2009-02-01 2009-01-02 : : ...

Updating records in Postgres using nested sub-selects

I have a table where I have added a new column, and I want to write a SQL statement to update that column based on existing information. Here are the two tables and the relevant columns 'leagues' => id => league_key => league_id (this is the new column) 'permissions' => id => league_key Now, what I want to do, in plain English, i...