Welcome Guest, Not a member yet? Register   Sign In
SQL Sub-query
#1

[eluser]theshiftexchange[/eluser]
I'm trying to write a MySQL Sub-query to get some info. I've got two separate functions that work correctly, but I need to join them as one.

This is my main select statement - it grabs all the users that are available on a particular day.

Code:
SELECT * FROM overtime_list
JOIN users ON users.user_ID = overtime_list.user_ID
WHERE overtime_list.date = '$date'

Now when I get that result list, I run a loop in an array, and check another table to see how much (if any) overtime they have done up to now:

Code:
SELECT SUM(shift_length) AS overtime_total
FROM overtime_assigned
WHERE user_ID = '$user_ID'


What I need is the second function to be added to the first function; i.e. as the first function is getting all the users, its grabbing their current overtime_total at the same time (rather than run a loop later on).

yes - i've posted this question before - but I've reworded it to try to help clarify the issue

Thanks in advance
#2

[eluser]umefarooq[/eluser]
hi try this query
Code:
SELECT *,(SELECT SUM(shift_length)FROM overtime_assigned
WHERE overtime_assigned.user_ID = users.user_ID ) AS overtime_total FROM overtime_list
JOIN users ON users.user_ID = overtime_list.user_ID
WHERE overtime_list.date = '$date'

you can also use single query by joining three tables but you have to make group by to get you result because you are using sum to calculate it

Code:
SELECT *,SUM(shift_length) AS overtime_total FROM overtime_list
JOIN users ON users.user_ID = overtime_list.user_ID
Join overtime_assigned on overtime_assigned.user_id = users.user_ID
WHERE overtime_list.date = '$date'
Group by users.user_ID

try both hope both will give you your desired results
#3

[eluser]Unknown[/eluser]
A subquery is a SELECT query that returns a single value and is nested inside a SELECT, INSERT, UPDATE, or DELETE statement, or inside another subquery. A subquery can be used anywhere an expression is allowed. In this example a subquery is used as a column expression named MaxUnitPrice in a SELECT statement.

SELECT Ord.OrderID, Ord.OrderDate,
(SELECT MAX(OrdDet.UnitPrice)
FROM Northwind.dbo.[Order Details] AS OrdDet
WHERE Ord.OrderID = OrdDet.OrderID) AS MaxUnitPrice
FROM Northwind.dbo.Orders AS Ord

A subquery is also called an inner query or inner select, while the statement containing a subquery is also called an outer query or outer select.

Many Transact-SQL statements that include subqueries can be alternatively formulated as joins. Other questions can be posed only with subqueries. In Transact-SQL, there is usually no performance difference between a statement that includes a subquery and a semantically equivalent version that does not. However, in some cases where existence must be checked, a join yields better performance. Otherwise, the nested query must be processed for each result of the outer query to ensure elimination of duplicates. In such cases, a join approach would yield better results. This is an example showing both a subquery SELECT and a join SELECT that return the same result set:

/* SELECT statement built using a subquery. */
SELECT ProductName
FROM Northwind.dbo.Products
WHERE UnitPrice =
(SELECT UnitPrice
FROM Northwind.dbo.Products
WHERE ProductName = 'Sir Rodney''s Scones')

/* SELECT statement built using a join that returns
the same result set. */
SELECT Prd1.ProductName
FROM Northwind.dbo.Products AS Prd1
JOIN Northwind.dbo.Products AS Prd2
ON (Prd1.UnitPrice = Prd2.UnitPrice)
WHERE Prd2.ProductName = 'Sir Rodney''s Scones'

A subquery nested in the outer SELECT statement has the following components:

* A regular SELECT query including the regular select list components.

* A regular FROM clause including one or more table or view names.

* An optional WHERE clause.

* An optional GROUP BY clause.

* An optional HAVING clause.

The SELECT query of a subquery is always enclosed in parentheses. It cannot include a COMPUTE or FOR BROWSE clause, and may only include an ORDER BY clause when a TOP clause is also specified.

A subquery can be nested inside the WHERE or HAVING clause of an outer SELECT, INSERT, UPDATE, or DELETE statement, or inside another subquery. Up to 32 levels of nesting is possible, although the limit varies based on available memory and the complexity of other expressions in the query. Individual queries may not support nesting up to 32 levels. A subquery can appear anywhere an expression can be used, if it returns a single value.

If a table appears only in a subquery and not in the outer query, then columns from that table cannot be included in the output (the select list of the outer query).

Statements that include a subquery usually take one of these formats:

* WHERE expression [NOT] IN (subquery)

* WHERE expression comparison_operator [ANY | ALL] (subquery)

* WHERE [NOT] EXISTS (subquery)

In some Transact-SQL statements, the subquery can be evaluated as if it were an independent query. Conceptually, the subquery results are substituted into the outer query (although this is not necessarily how Microsoft® SQL Server™ actually processes Transact-SQL statements with subqueries).

There are three basic types of subqueries. Those that:

* Operate on lists introduced with IN, or those that a comparison operator modified by ANY or ALL.

* Are introduced with an unmodified comparison operator and must return a single value.

* Are existence tests introduced with EXISTS.
#4

[eluser]theshiftexchange[/eluser]
Thank you umefarooq and jonalararts for your replies - I'll try them later on and see how it goes!!!!




Theme © iAndrew 2016 - Forum software by © MyBB