SQL Subqueries
07/26/2001In the last article, I alluded to SQL subqueries which we'll discuss in more detail this week.
Subqueries are extremely useful, particularly for web-based database applications where you need to take two queries and manually put them together to reach a desired result -- subqueries allow SQL to do all of the heavy lifting! Subqueries can also be used in many cases to replace a self-join (or vice-versa). A SQL join is usually quicker but, as we've discussed many times before, there is usually more than one way to perform any given SQL task.
A query in a query?
The subquery is fairly straightforward part of the SQL specification. In a nutshell, a subquery is a SQL SELECT statement that is placed in the predicate of any other SQL statement we've explored -- SELECT, INSERT, UPDATE, or DELETE. You're quite smart enough on your own to figure them out without my intervention, but we'll cover them here to make sure we've hit everything!
A subquery can be used in a number of scenarios:
SELECT/UPDATE/DELETE .... WHERE (SELECT ...)which can be used to filter data before an action is applied to the results of that filter;INSERT INTO.... SELECT ....which can be used to copy tables or portions of tables into a new table for further manipulation;- Another subquery which can then be nested again up to the limits of your database platform -- or your sanity and understanding.
|
|
You've probably found yourself at various points in SQL development mentally creating subqueries in your head -- things like finding all of your high-volume customers who are also the ones that pay on time or maybe updating information about all the employees that are also managers in the company. Every major RDBMS lets you do at least some level of subquerying to address that exact issue.
Using a subquery
Let's say we want to find the names of all of the managers in the Employees table. Starting with the following table
| Employees | ||
| EmployeeID | EmployeeName | ManagerID |
| 61 | Sue Smith | (null) |
| 62 | David Jones | 61 |
| 63 | Troy Parker | 61 |
| 64 | Claire Smith-Jones | 63 |
| 65 | Grover Rivers | 63 |
we want to first select all of the values for ManagerID and then associate them with a name. We can do that with the following set of queries
SELECT EmployeeName AS Employee
FROM Employees
WHERE EmployeeID IN (SELECT DISTINCT ManagerID FROM Employees)
The queries are addressed from the inside out, so the first step is to perform the statement
SELECT ManagerID FROM Employees
which returns the result set (61,63). This means the outer query becomes
SELECT EmployeeName AS Employee
FROM Employees
WHERE EmployeeID IN (61,63)
which then gives us the record set ("Sue Smith", "Troy Parker").
The only caveat with subqueries is that you must be aware of exactly what the query will return as far as fields, field types, and values. Our subquery in this example returns a single column of values which are valid values for the WHERE EmployeeID IN clause. If the subquery returned the employee name, for example, you'd get a data type mismatch because "Sue Smith" is a string and EmployeeID is a numeric field -- WHERE EmployeeID IN ('Sue Smith'....) makes no sense. This caveat is especially true when you are using a SELECT subquery inside of an INSERT INTO statement -- both the number of fields, their order, and the data types must match up or the INSERT will fail.
Next steps
This article on subqueries is the final article in our introduction to the core SQL statements for manipulating data. Later, we'll move into SQL that manipulates the database itself, but before we do that, I'll devote the next several columns to the world of set algebra and the SQL commands relating to the UNION and INTERSECTION statements. Until then, feel free to contact me with your comments and questions.
Do you have questions or comments for John Paul Ashenfelter about subqueries?
You must be logged in to the O'Reilly Network to post a talkback.
You must be logged in to the O'Reilly Network to post a talkback.
Showing messages 1 through 86 of 86.
-
sql review
2001-10-01 18:14:25 dragonstep [Reply | View]
Excellent commentary; to the point and accurate. I've been doing RDMBS for years and still enjoyed the review.
-
mySQL help
2001-11-07 03:10:30 adunkey [Reply | View]
Hi John,
good article, could I ask you something? Say I have a mySQL web database with lots of stuff coming into it, I'm trying to separate this by the primary key 'id', so in my perl I have a command like: update table_name set item_name = 'blah' where id in(select max(id) from table_name); but it does'nt work, have you any ideas?
thanks
ian -
mySQL help
2001-11-24 14:26:17 the_newt [Reply | View]
MySQL does not yet support subqueries/sub-selects, but they're working on it. Its just not a priority. There are ways around it though.
Your question seems vague but I'll try to answer as best as I can. I'm assuming you want the last id in the table. MAX() is a summary function and isn't really what you want to use to get the last id in the table. It will work but the results can be pretty sketchy.
UPDATE <table> set item_name='bar' WHERE id IN( SELECT MAX(id) FROM <table>)
could be rewriten as
UPDATE <table> set item_name='bar' WHERE id = LAST_INSERT_ID(table_name.id)
I'm not sure why you would want to do this as record updates should always be updated against a primary key. You're asking for trouble if you don't. Both statements above will always update the last inserted record in the table and have a high possibility of messing up the records.
Hope this helps and John please step in if I'm wrong.
The Newt
-
MySQL does not have subqueries
2002-01-27 01:05:16 jaalto [Reply | View]
To my understand, the latest released MySQL as of 2002-01-26 3.23.47 does not support Subqueries. So my question is, why did the O'Reilly editors let this article appear in this forum labelled "MySQL"? I have had a high regard of O'Reilly's accuracy, but this is a mistake that should be corrected.
Please move this article and other similar related to Subquesries to a general "SQL" forum, because the readers get wrong impression that you can use these examples in mySQL.
-
sort a query with two statements
2002-07-08 06:25:24 frank.reckers [Reply | View]
How can I sort (order by) a query with two statements?
-
subquery
2002-08-05 07:54:46 jgconst [Reply | View]
I have a database that is somewhat like a timesheet for employees. Let me give you an example, I have repeat entries with only the date as the primary key. So, I have id, name, address, phone, hours of work. There is an entry for each day they come to work for each employee. So, I want to get a list of all employees, the last day they worked, and all related data for that record. I have not had any sucess, please help. They are all on one table. I have used Order By "ID", "DATE" DESC, which sorts the table, so all I have to do is pull the distinct entry (but then I can't use Select * to see all my other fields. Do you have any suggestions?
-
multiple updates in a single query
2002-11-14 00:53:31 anonymous2 [Reply | View]
hi,
it was really a nice article. I have a question.
I a trying to update multiple instances
-
multiple updates in a single query
2002-11-14 00:57:59 anonymous2 [Reply | View]
hi,
it was really a nice article. I have a question.
I a trying to update multiple entries with a single update statement. But my nth update is dependent on n-1th update. LEt us take an example.
lets say the query being executed is
update tbl set id = max(id) +1 where id < -9.
So in this, my last set id call will change the max(id).
On execution this query throws some exception ora-00934 saying group function is not allowed here.
can this type of updates be done? or is my query wrong?
Regards
A. Bansal
-
SubQueries
2003-02-27 17:51:37 john1948 [Reply | View]
I have 2 sql tables.
(1) One Table contains single part numbers. In this table the part numbers are unique.
(2) The Second table contains multiples of the single part number in the first table. This table contains values in the second part record that I need to access. I have written a procedure to get at this data in a cursor. This cursor however take over 6 minutes to update the 1st table. The second issue is that the first table only contains about 3,000 records. I see no easy way of accessing this second record.
Do you?
-
SubQueries
2003-06-04 06:27:40 anonymous2 [Reply | View]
What you have to decide is what row you want to get from the second table. If I am reading your description correctly, you have a two tables that have a one to many relationship. For the table that you want data that has the many relationship, you have to define (assuming you want one row from the "many" table) additional criteria. Once this is defined, then you can use 'MIN', MAX', or a specific value from the "many" table.
Ex -
SELECT *
FROM table1 a LEFT OUTER JOIN table2 b on
a.part = b.part
and b.column = select min(c.column)
from table2 c INNER JOIN
b.part = c.part
This query attempts to use a subquery to obtain the unique value from the 'many' table.
-
subqueries
2003-04-27 06:31:41 anonymous2 [Reply | View]
Sir, ur explenation is excelent, but one suggestion that if it contains more examples(cotentwise)
ThnkU
Srinu
MyID : srinivasaraju_n@hotmail.com
-
subqueries
2003-05-15 11:32:27 anonymous2 [Reply | View]
how do you do a subquery where you are using a composite primary key for example:
select courseID, studentID
from whatever
where courseID AND? StudentID IN (
select courseID, studentID
from somewhereelse
); -
subqueries
2003-06-26 07:54:31 anonymous2 [Reply | View]
You'd have to use the EXISTS operator instead of the IN operator for that, IN will only work with single column results:
SELECT
courseID
, studentID
FROM
whatever W
WHERE
EXISTS (
SELECT
1 -- doesn't matter what you return here, even NULL is OK, the EXISTS operator is satisfied as soon as anything is returned
FROM
somewhereelse S
WHERE
S.courseID = W.courseID
AND S.studentID = W.studentID
)
Another option would be to do an inner join on the two columns, but that's not a subquery so beyond the scope of this discussion :)
-
Subquery
2003-06-21 03:17:00 anonymous2 [Reply | View]
I would like to combine the following two queries in one, how please?
SELECT
AS_OF_DATE,
SERIES_NAME,
AVG(VALUE) OVER (PARTITION BY SERIES_NAME ORDER BY AS_OF_DATE ASC ROWS 4 PRECEDING) MOVING_AVG_5_DAY
FROM V_DBO_VALUE
WHERE PERIOD = 'D' AND
SERIES_NAME IN ('WTIMARK', 'DUBAI') AND
AS_OF_DATE > TO_DATE('20030501', 'YYYYMMDD')
The second query is:
SELECT
AS_OF_DATE,
SOURCE.PERIOD,
SUM(DECODE(TRIM(SERIES_NAME), 'WTIMARK', VALUE, NULL)) MWTI
SUM(DECODE(TRIM(SERIES_NAME), 'DUBAIM', VALUE, NULL)) MDUBAI
FROM V_DBO_VALUE
WHERE
SERIES NAME IN ('WTIMARK', 'DUBAI') AND
SOURCE.PERIOD='D'
GROUP BY AS_OF_DATE, PERIOD
-
SQL SUBQUERIES
2003-07-29 15:59:25 anonymous2 [Reply | View]
Go article, I have a question.
If I have to database that I want to use for update or delete. For example. Database1 has a field/column labeled OREF#. Database2 has a field/column labeled OREF1#. How would I update or delete all the records in database1 where the OREF# equals OREF1# in database2.
-
number of rows returned by query
2003-08-28 03:42:57 anonymous2 [Reply | View]
Hi SQL guruz,
I am just a beginner in the SQL and have a little problem.
I have an query which works flawlessly and return the expectant results. It looks like:
select idchain from chains
group by 1
having count (idchain) > 1
Now. I am not interested in the content of the result. I just want to know, how much rows this query will return. I tried to enclose all that query in the other, but it does not work. I get the syntax error instead of count of rows. This what I have tried:
select count (*) from (
select idchain from chains
group by 1
having count (idchain) > 1
)
What I am doing wrong?
Thanks in advance, Roman G., Prague
-
Subqueries
2003-09-15 05:26:20 anonymous2 [Reply | View]
can subqueries return the row or multiple rows or whole table or only the column..
-
Sub Queries
2004-01-27 11:31:22 leenmary [Reply | View]
Hi,
I'm working with Oracle as part of an ASP project and need to add a query. I have this query working within Access but am not sure how to transfer it to the correct syntax for oracle. Here's the Access Query:
SELECT CAnd.Name, Count(VotetoC.No2) AS CountOfNo2
FROM CAnd INNER JOIN VotetoC ON CAnd.Cand_ID = VotetoC.Can_ID
GROUP BY CAnd.Name
HAVING (((Count(VotetoC.No2))=True));
I keep getting the error for the last line. I need to count only the rows where VotetoC.No2 has a value of True.
Thank you in advance!
-
Subqueries
2004-01-29 22:43:01 wildmaniac2004 [Reply | View]
High, im trying to work on a transfrom query that includes or calls a subquery. I am supposed to generate a recordset that provides a summary of donation money received by alumni graduation year. That is not hard, however i am getting stuck when it then states that for alumni that have more than one degree i should take the lastest graduation date. Note that the infromation im using is coming from two tables that are linked by the StudentID field. One has the donation information for each student and the other has the graduation dates for each. Please let me know if there is anyway i can retreive records with the latest graduation date.
-
Do I need a subquery?
2004-11-08 07:53:54 andy_mcghee [Reply | View]
Hi everyone. I'm pretty new to SQL and there is something I'm stuck with something.
Put simply, I have a table which stores information about groups. Now, some groups have sub groups and hence some groups have parents. So, there is a field called GroupGUID and a field called ParentGroupGUID. I want to write a query where I can get the description of the group and it's parent group. Both these groups are stored in the same table. I just cant think how to do this.
Am I being stupid?
Thanks
Andy
-
Alternate sql query for existing quesry
2004-11-18 23:11:01 Samu [Reply | View]
My query given below is very slow to execute
"select TempTable.* from TempTable where TempTable.col1 + TempTable.col2 not in (select col1 + col2 from OrigTable)"
Both the tables referenced have 90,000 records
and combination of col1 and col2 is the primary key.
Can you suggest some alternative fast method to execute my above mentioned query
Samu
-
A little SQL Subquery help, please?
2004-12-14 22:02:03 LFaler [Reply | View]
This explanation is great, but I'm having a problem applying it to something.
I have a table that contains 4 columns. "ID", "Name", "Yes/No", "Date". There is only one record per day per name. What I would like is for it to return the information as follows:
___________________________________
| Name | Date 1 | Date 2 | Date 3 |
-------------------------------------
| Name 1 | Yes/No | Yes/No | Yes/No |
| Name 2 | Yes/No | Yes/No | Yes/No |
| Name 3 | Yes/No | Yes/No | Yes/No |
I've been toying with the following query, but it isn't returning it right. How should I write it?
"SELECT DISTINCT a.Name, (SELECT YesNo FROM L_Mail_Log b WHERE Date=1/2/2004 AND Name = a.Name) AS [1/2/2004], (SELECT YesNo FROM L_Mail_Log b WHERE Date=1/3/2004 AND Name = a.Name) AS [1/3/2004], (SELECT YesNo FROM L_Mail_Log b WHERE Date=1/4/2004 AND Name = a.Name) AS [1/4/2004] FROM L_Mail_Log a"
-
A little SQL Subquery help, please?
2004-12-21 12:34:42 msantoyo [Reply | View]
"LFALER" You can try soemthing like this (only works if you have an exact number of days.
For the following example I am altering a little bit your table info... if a certain id/name doesn't exist on a certain day , it won't appear on the table.
select
distinct b.id, b.name, b.day1,
case
when t.id is not null then 1
else 0
end as day2
from
(
select
distinct a.id, a.name,
case
when t.id is not null then 1
else 0
end as day1
from
(
select
distinct id, name
from
table t (nolock)
) as A
left join
table t (nolock)
on a.id=t.id and t.date='xxxxx' /*Date 1*/
) as B
left join
table t (nolock)
on b.id=t.id and t.date='xxxxx' /*date 2*/
and another try, for your actual table structure:
select
distinct a.id, a.name,
t.yesno day1,
t.yesno day2, ... dayn
from
(
select
distinct id, name
from
table t (nolock)
) as A
inner join
table t (nolock)
on a.id=t.id and t.date='xxxxx' /*Date 1*/
inner join
table t2 (nolock)
on a.id=t2.id and t2.date='xxxxx' /*Date 2*/
....
inner join
table tN
on a.id=tn.id and tn.date='xxxxx' /*Date N*/
hope this helps
-
Subqueries question - multiple conditions
2005-01-26 01:24:57 S-P-A-R-K [Reply | View]
Hi,
I'd trying to make a grouped query that returns a count of unique values but where I have 2 boolean parameters:
For example - I have an "ID" column, and another boolean column named uType of 1 and 2.
I want to display the count of unique IDs of the 2 kinds (1,2) in 2 different columns.
The table has these fields:
ID | uType("1" or "2")
The result is sort of this:
ID Unique 1 Unique 2
6454 4 3
9788 9 7
9977 5 5
101 8 6
I only can show it in 2 queries and I need to make it one....
SELECT ID, COUNT(DISTINCT ID) AS UniqueID FROM MyTable WHERE uType = 1 GROUP BY ID
Thanks in advance!!! -
Subqueries question - multiple conditions
2005-03-07 07:37:33 edsel99 [Reply | View]
Any resolution to this issue? I'm have the same problem.
-
Subquery Question
2005-01-27 10:44:48 jcc [Reply | View]
On the web page, you have:
"we want to first select all of the values for ManagerID and then associate them with a name. We can do that with the following set of queries
SELECT EmployeeName AS Employee
FROM Employees
WHERE EmployeeID IN (SELECT DISTINCT ManagerID FROM Employees)"
Would it be the same if I were to write it like this without using a subquery?
select distinct managerID, employeename
from employees
where managerid <> null;
Thanks.
-
i want a previous value of the max value
2005-01-31 20:33:08 raviambala [Reply | View]
i want a prevoius value of the max value
for example we have a table Employee
EmpId EmpName Salary
a001 Ravi 10000
a002 Amit 9500
a003 Chetan 15000
a004 Rahul 6500
the max of salary is 15000
in the above table i want the second max salary means 10000
-
i want a previous value of the max value
2005-03-29 03:41:22 Ansu [Reply | View]
select * from Table1 A
where (n-1) =(select count(*) from Table1 b
where a.[fieldname] < b.[fieldname])
-
Help! Need to increase salary by 5%
2005-03-22 07:24:39 cmrosiek [Reply | View]
Help,
I am taking a sql class in which we are using Access to program. I have a table with employees and salaries in which I need to increase the salary by 5%. Do I need a subquery and how do I do it?
Help, thanks.
-
help
2005-09-13 21:47:18 sumeet_ [Reply | View]
i want to select all columns from a table for the minimum of two columns. the table has few records with both minimums coming together.so i want these rows.
i tried e.g.
select distinct * from emp where sal=(select min( e.sal) from emp e where e.deptno <= (select min(t.deptno) from emp t));
i also tried
select * from emp where sal=(select min( e.sal) from emp e where 1=(select count ( di
stinct empno) from emp t where e.deptno <=t.deptno group by e.sal));
-
subqueries and group by
2005-09-16 10:21:39 LadyReader [Reply | View]
I have a SQL table that contains data accumulated over many months. I need to:
1) extract the rows from with a specified date range
2) consolidate the remaining rows where 2 of the fields both match
3) Sum up the contents of a 3rd field ("Qty") during that consolidation
4) select all fields, both summed and not, of the consolidated rows to import into a 2nd table.
The problem I am having is that in order to select all the fields, SQL is insisting that all the fields must appear in the Group By clause I used to sum up Qty.
I tried to use a subquery to do the initial date range filter, and this worked fine. However, when I tried to use another subquery to then sum on Qty, SQL threw an error saying that the subquery was returning more tahn 1 result. I understand the problem but I don't know how to overcome it. Can you advise?
Thanks!
-
Moving selected information
2005-10-10 14:04:59 Greg007 [Reply | View]
I have a database of customers that I need to first, select information from one table in that database and then move it to a new table in that database. I have got as far as selecting the information with SELECT * FROM (table) WHERE (field)="?" Now I just need to know how to move that information into a newly created table. -
Moving selected information
2005-10-16 20:03:54 isabella57 [Reply | View]
There are several ways. I think this is the easier. After you selected the information with SELECT, write COPY TO {NEW TABLE NAME} and you will have your select in a new table. After you write OPEN NEW TABLE NAME y you can use it.
ISABELLA57
-
hi
2005-12-05 21:02:47 sqlserver2000 [Reply | View]
i read ur subquery concept very useful. you reterive manager from your query.but i want empname,managername from that table .plz mail my id ramani_1910@rediffmail.com.
thankz in advance
-
sql query
2005-12-09 00:00:58 davaleswarapu [Reply | View]
i had a table named production with columns names as machine,item,sdate,edate,qty.
i need a query in such a way that the qty of a item with respect to machine with its corresponding sdate and edates are consequtive is to be summed.
-
How to sort a subquery?
2006-01-26 13:06:12 cyberlogi [Reply | View]
Hello,
I have a query with a subquery that looks like the following
SELECT name FROM userTable WHERE id = ANY ( SELECT id FROM otherTable WHERE otherId = X)
OtherTable is a table where id can be connected to many otherIds. And userTable's primary key is id and contains the name of the user among other relevant information. I need to sort this query according the lastUpdate time in otherTable. I have tried adding ORDER BY to the subquery, with many different combination, but it always spits out the data in the same order.
Please advise what I can do differenty in my query to ORDER this data. Thanks.
-
nth subquery if the records has same salary for two or three employees
2006-02-06 04:55:06 onlinestudyguide [Reply | View]
sir,
the following is the emp1 table havin
empno| empname| sal|deptno
101 |balu|6000| 101101 -
nth subquery if the records has same salary for two or three employees
2007-07-05 15:00:00 Poon [Reply | View]
you can even solve the query like the following:
This takes care of the duplicate and also the null value in the salary column:
select employee_ssn,salary from employee e1 where (select count(*) from(select distinct salary from employee) e2 where nvl(e2.salary,0)>nvl(e1.salary,0))<&a;
-
nth subquery if the records has same salary for two or three employees
2006-02-06 04:57:32 onlinestudyguide [Reply | View]
sir,
the following is the emp1 table havin
empno| empname| sal|deptno
101 |balu|6000| 101101
102 |subbu|5000|102101
103 |----|4500|103101
104|-----|3500|104101
105|-----|5000|105101
108|----|6000|108101
pls tell me how to write a nth max.salary in this table to find out correctly.
thanks! in advance
-
accumulate a field
2006-03-02 01:53:41 st.chen [Reply | View]
thanks in advance,
if i have a table having the following 2 fields:
date qty
-------- ---
20060101 100
20060102 120
20060103 111
...
can we get the following data using a SELECT statement?
date qty tqty
-------- --- ----
20060101 100 100
20060102 120 220
20060103 111 331
...
-
accumulate a field
2006-04-05 21:30:05 paul21_7 [Reply | View]
SELECT date,
qty,
(
(SELECT qty
FROM table b
WHERE b.date = (a.date - 1))
+ qty
) tqty
FROM table a
Syntactically its fine ... I made a small assumption on date being the incremental field, so just alter to suit
enjoy
-
Subquery
2006-04-01 05:21:25 rooman [Reply | View]
i want to count comm col in emp table.there are some NULL values.i want my screen to show me two columns ,one telling me the count on NULL values and the other on NOT NULL values.
-
transforming subqueries to joins
2006-04-21 02:20:46 sinus [Reply | View]
Is it possible to transform subqueries in WHERE clasue to joins? I can do it for some class of subqueries but have difficulties, e.g., when there is an aggregation in a subquery.
-
Subquery - how
2006-06-07 06:37:45 Brickyard [Reply | View]
I am a newbie to writing SQL!
I need to create a file where each row is unique to an individual. In one of the tables I need to query, a field exists named PHONE_USE_CD (1-Business, 2-Fax, 3-Voicemail). Two other fields are AREA_CODE and PHONE_NUMBER. I need to retrieve both the Business phone and Voicemail phone numbers into the same row. I also have a table of spoken languages where a field exists named LANGUAGE (FR-French, IT-Italian, SP-Spanish, GM-German, etc.) Some people speak multiple languages and I need to identify each language spoken in the single row. I think I need to write a subquery, but am unsure how to do so.
Thanks!
-
i need total information
2006-07-09 03:04:24 SyedNasurUllah [Reply | View]
i want the total in formation about sql and pl/sql
with query and subquery with details which use ina project thanking you.
your's faithfully
nasur
-
sub query help
2006-08-25 14:39:28 fperez [Reply | View]
HI- I'm having trouble getting the results I need from an sql query. I believe I can accomplish my task with a subquery however I can't get it to work. I have a table of book titles and a one to many table of books read (that holds many people and the book they read). Now I need to get a list of books left to read by a particular person from my join. So the query starts with a join between the books table and the books read table to get the book titles. I use a left join to get all the book titles even if they haven't been read. However I need it to filter out the books that the particular person has read and leave me with a list of books left to read by the particular person.
please advise-frank -
sub query help
2006-11-17 21:08:48 pfindley [Reply | View]
Frank,
It's been a long time since August 25th when you posted your question, but i just saw it last night and thought i would give it a try. I created a sample db called "bks" with 3 tables in it:
1) Books (1 field called 'books')
2) BooksRead (2 fields called 'rdr' and 'bk')
3) Readers (1 field called 'reader')
For my example, i included 7 book titles in table 'Books', 3 student's names in table 'Readers'. 'BooksRead' is a table that includes student's names from 'Readers' along with books they have read from 'Books'.
Here is the data in Books:
Mere Christianity
God In the Docks
Miracles
The Great Divorce
The Problem of Pain
The Screwtape Letters
The Weight of Glory
Here is the data in BooksRead:
Student1 Mere Christianity
Student1 Miracles
Student2 God In the Docks
Student2 The Problem of Pain
Student2 The Screwtape Letters
Student1 The Weight of Glory
Student1 The Screwtape Letters
Student3 The Screwtape Letters
Student3 Mere Christianity
Here is the query that will return a list of the books each student has NOT read:
SELECT CONCAT( reader, books ) , reader, books
FROM Books, Readers
WHERE CONCAT( reader, books ) NOT IN
(
SELECT CONCAT( ` rdr` , `bk` ) FROM BooksRead
)
ORDER BY reader;
There are probably easier ways to do this, but this was the way i was able to figure it out. Here is an explanation:
SELECT CONCAT( reader, books ) , reader, books
FROM Books, Readers
First thing i did was join and concatonate a list of all the readers with all the books. This may not be such a great option depending on how many books/students you have. This basically produces a list of ALL the readers, alongside ALL the students. Then it compares this list to a list the following code produces:
WHERE CONCAT( reader, books ) NOT
IN (
SELECT CONCAT( ` rdr` , `bk` )
FROM BooksRead
)
Anywhere there is NOT a match in the two lists it includes that data, but if there is a match of the first list to this second list, it does not include that data.
Lastly, the following simply puts it in order by reader's name:
ORDER BY reader;
Something tells me you probably figured that last part out already... perhaps by now, you've figured all of this out and in a much better way. If so, would you mind sharing your knowledge with me on how you did it? I'd appreciate learning from you.
Hope his was helpful.
God bless,
paige findley
-
Count the number of record first existing in table at a date
2006-10-16 20:50:17 Devils [Reply | View]
Hi,
I have proble to find the number of single record in a table. e.g.
field A | field B
111 | 1/10/2005
111 | 2/10/2005
222 | 2/10/2005
333 | 2/10/2005
222 | 3/10/2005
I want to know how many record first exist at 2/10/2005. The result should be 2.
i.e.
111 first exist is 1/10/2005 -> not count
222 first exist is 2/10/2005 -> count
333 first exist is 2/10/2005 -> count
Can you help how to do this?
Regards,
Devils
-
Count the number of record first existing in table at a date
2006-10-29 01:25:20 ramu82 [Reply | View]
select distinct(field A) from date -
Count the number of record first existing in table at a date
2006-12-13 05:39:38 jmtsnprn [Reply | View]
the query could be:
select count(fielda) from table where fieldb = '2005-02-10'
-
need answer..
2007-02-17 02:53:52 rajii [Reply | View]
i have related two tables.
table name are "usermanagementtable" and "projectstable"
usermanagementtable have Employeeid's of project manager,teamleader,associate projects.it has other coloumn called designation.(designation will be manager,teamleader,associate projects).
projectstable has project manager EmpID who are assigned to some projects.
here i set usermanagementtable Employeeid as primary key and projectstable Employeeid as foriegn key.
now i need projects manager empid from usermanagementtable where those id should not be in projects table..
that is project manager id's who has not assigned to any
projects.
can anyone answer this please...
-
answer for this
2007-04-02 21:44:52 sethumeena [Reply | View]
if u this following subquery u can easily reteive the answer for ur question.i ve checked. u also check with program and inform.
select a.emplid,a.designation from usermanage_tbl a where a.designation='pm' and
a.emplid not in(select a.emplid from projectable_tbl b where a.emplid=b.emplid)
-
using Left join and MAX and group by function getting wrong reslts
2007-04-20 23:18:20 satyac46 [Reply | View]
I have a query like this
SELECT can.iCandidateId AS canId, can.iCInterviewId, concat( can.vFirstName, ' ', can.vMiddleName, ' ', can.vLastName ) AS candidatename, can.vCandidateCode, can.vPost AS designation, cei.vDegree, MAX( cei.vPassoutMonthYear )
FROM candidate AS can
LEFT JOIN `candidate_education_info` AS cei ON can.iCandidateId = cei.iCandidateId
WHERE cei.iCandidateId
IN (
SELECT iCandidateId
FROM candidate AS can
WHERE can.dEntryDate >= '2007-04-15 00:00:00'
AND can.dEntryDate <= '2007-04-21 23:59:59'
)
GROUP BY can.iCandidateId
i am getting Maximum date but what happend is my degree is not comming to that related date i unable to find where that query is mistaken
-
Types of Subqueries
2007-05-31 21:28:21 DJ86 [Reply | View]
Hello,
My question is "What are the all subquery cases?"
Thanks,
-
Will a sub query help with LONG-Data?
2007-07-30 20:10:08 dgreep [Reply | View]
I'm querying an Oracle DB where I want to retrieve totals and a comment field. The comment field is LONG data and causes a "group by" error. Is there a way to create such a query?
SELECT STDRPT_WO_DETAIL.EQ_EQUIP_NO, STDRPT_WO_DETAIL.WORK_ORDER_ID, STDRPT_WO_DETAIL.REAS_REAS_FOR_REPAIR, RSN_MAIN.DESCRIPTION, STDRPT_WO_DETAIL.JOB_TYPE, STDRPT_WO_DETAIL.JOB_STATUS, Sum(STDRPT_WO_DETAIL.XXFLT1202_LINE_TOTAL) AS SumOfXXFLT1202_LINE_TOTAL, STDRPT_WO_DETAIL.COMMENT_AREA AS FirstOfCOMMENT_AREA
FROM STDRPT_WO_DETAIL
INNER JOIN RSN_MAIN ON STDRPT_WO_DETAIL.REAS_REAS_FOR_REPAIR = RSN_MAIN.REAS_REAS_FOR_REPAIR
GROUP BY STDRPT_WO_DETAIL.EQ_EQUIP_NO, STDRPT_WO_DETAIL.WORK_ORDER_ID, STDRPT_WO_DETAIL.REAS_REAS_FOR_REPAIR, RSN_MAIN.DESCRIPTION, STDRPT_WO_DETAIL.JOB_TYPE,
STDRPT_WO_DETAIL.JOB_STATUS
HAVING STDRPT_WO_DETAIL.EQ_EQUIP_NO ="7002064";
-
Joins and pk, fk
2008-01-11 02:27:30 JugalKishorem [Reply | View]
we can retrieve data from two table using joins inspite there is no relation then what is use of primary key and foreign key and making a relation
-
how to get distinct values from second table on comparing with first table
2008-04-02 23:40:27 senthil.N [Reply | View]
I'm having 2 tables as shown below
tb1 tb2
col1 col2 col1 col2
10 10000 10 10000
11 15000 15 20000
15 20000 13 21000
21 21000 23 10000
i need solution like this
col1 col2
10 10000
11 15000
21 21000
13 21000
23 10000
plz help me !
-
Help with subquery please
2008-04-16 08:45:26 Viraco [Reply | View]
select a.purchaser_id, a.account_name
from account a, user_application_access b
where a.purchaser_id=b.constraint_value
and a.country_code in ('US','CA')
and 'UADM' not in (
select distinct c.application_id
from user_application_access c
where c.constraint_value=a.purchaser_id
)
order by a.purchaser_id
when run in asp code it returns no results, when run a sql query application it hourglasses
-
: Pending Delivery Note Details
2009-04-05 22:19:33 shebus [Reply | View]
• Details of delivery note which are not invoiced.
• Views used - VW_DELIVERY_ACTUAL, VW_SALES_INVOICE
• The report should be grouped by SALEPOINT,DOC_CODE
• Parameters of the report (SalePoint(T1), Customer(T2), From Date(D1) & To Date(D2))
• If Customer Parameter is null then Show all.
-
SQL subquery help
2009-05-28 13:16:59 w2kadmin [Reply | View]
Hello all,
I am trying to run a query that allows me to update values only for a particular user and only with a time frame. The query runs but it updates the values for all users. What am I doing wrong?
Thanks very much for any help. Here's the query in 2 parts:
select [patid], [id] ,[date],[mssql_user],[type]
from [CVMSSQL].[dbo].[RVS]
where mssql_user = 'ghaft'
update [CVMSSQL].[dbo].[RVS]
set [type] = '1' where [type] = '7' and date between '2009-01-14' and '2009-01-14'
