Home » SQL & PL/SQL » SQL & PL/SQL » Correlated Subqueries (Oracle 10g)
icon5.gif  Correlated Subqueries [message #339048] Wed, 06 August 2008 12:00 Go to next message
aokwuadigb
Messages: 18
Registered: August 2008
Junior Member
I am currently trying to write a query to perform the following:

For those deactivated agents that are on more than seven missions, change their deactivation date to the earliest deactivation date of all agents who were activated in the same year as the agent you are updating.

I understand the question but dont know how to go about breaking it down.

Is there a method for mapping correlated sub-queries?

The above requires me to access two tables 'Missions_agents' and 'Agents'. 'Mission_agent' only contains mission_id and agent_id, 'Agents' contains the rest.

If anyone could shed light on how i show approach this i would be grateful.
Re: Correlated Subqueries [message #339052 is a reply to message #339048] Wed, 06 August 2008 12:04 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above
Re: Correlated Subqueries [message #339056 is a reply to message #339048] Wed, 06 August 2008 12:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Put what you already tried and before read the link Ana provided to know how to post.

Regards
Michel
Re: Creating Correlated Subqueries [message #339066 is a reply to message #339048] Wed, 06 August 2008 12:30 Go to previous messageGo to next message
aokwuadigb
Messages: 18
Registered: August 2008
Junior Member
AGENTS
agent_id primary key
name
location_id foreign key
activation_date
deactivation_date

MISSION_AGENT
agent_id FK
mission_id FK

I have a few solutions which I am working on they all seem to be going nowhere here are a couple.
SELECT
	a2.agent_id,
	new.mission_count,
	a2.deactivation_date d_day,
	a2.activation_date a_day
FROM
	agents a2
INNER JOIN
	(

	SELECT
		a.agent_id agent_id,
		count(m.mission_id) mission_count
	FROM
		agents a
	INNER JOIN

		missions_agents m
	ON
		a.agent_id=m.agent_id
	WHERE
		deactivation_date is not null
	GROUP BY
		a.agent_id
	) new
ON
	a2.agent_id=new.agent_id

WHERE
	new.mission_count>7

=========================================================

SELECT
	d_day
FROM
	(
	SELECT
		min(deactivation_date) d_day,
		TO_CHAR(activation_date,'YYYY') a_year
	FROM
		agents t2
	GROUP BY
		TO_CHAR(activation_date,'YYYY')

	)
WHERE

	t2.a_year=t.TO_CHAR(activation_date,'YYYY')


I am ateempting to write the query in bits as I don't know how to look at a problem like this, occasionally I stumble on the write answer.

[Mod-Edit: Frank added code-tags to improve readability]

[Updated on: Thu, 07 August 2008 03:36]

Report message to a moderator

Re: Correlated Subqueries [message #339374 is a reply to message #339048] Thu, 07 August 2008 06:52 Go to previous messageGo to next message
aokwuadigb
Messages: 18
Registered: August 2008
Junior Member
Latest attempt but still failing any suggestions how i can fix the problem, which is the last WHERE clause.

UPDATE
agents up
SET
deactivation_date = (

SELECT
d_day
FROM
(
SELECT
min(deactivation_date) d_day,
TO_CHAR(activation_date,'YYYY') a_year
FROM
agents t2
INNER JOIN
(
SELECT
a.agent_id agent_id,
count(m.mission_id) mission_count
FROM
agents a
INNER JOIN

missions_agents m
ON
a.agent_id=m.agent_id
WHERE
deactivation_date is not null
GROUP BY
a.agent_id
HAVING
count(m.mission_id)>7
)rr
ON
t2.agent_id=rr.agent_id
GROUP BY
TO_CHAR(activation_date,'YYYY')
)
WHERE
up.agent_id=a.agent_id )

[Updated on: Thu, 07 August 2008 06:54]

Report message to a moderator

Re: Correlated Subqueries [message #339392 is a reply to message #339374] Thu, 07 August 2008 07:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You still failed to format your post.

Regards
Michel
Re: Correlated Subqueries [message #339405 is a reply to message #339048] Thu, 07 August 2008 07:41 Go to previous messageGo to next message
aokwuadigb
Messages: 18
Registered: August 2008
Junior Member
Think I am getting closer.
SELECT
	min(deactivation_date) d_day,
	TO_CHAR(activation_date,'YYYY') a_year
FROM
	(
	SELECT
		a.agent_id agent_id,
		count(m.mission_id) mission_count, 
		max(a.activation_date) activation_date, 
		max(a.deactivation_date) deactivation_date

	FROM
		agents a
	INNER JOIN

		missions_agents m
	ON
		a.agent_id=m.agent_id
	WHERE
		deactivation_date is not null
	GROUP BY
		a.agent_id
	HAVING
		count(m.mission_id)>7
	)
GROUP BY
	TO_CHAR(activation_date,'YYYY')
Re: Error in query [message #339436 is a reply to message #339405] Thu, 07 August 2008 08:59 Go to previous messageGo to next message
aokwuadigb
Messages: 18
Registered: August 2008
Junior Member
Can anyone see why I am updating to many records in the query below?

UPDATE
	agents aaa
SET
aaa.deactivation_date = ( SELECT d_day
FROM
(
SELECT
	min(deactivation_date) d_day,
	TO_CHAR(activation_date,'YYYY') a_year,
	max(agent_id) agent_id
FROM
	(
	SELECT
		a.agent_id agent_id,
		count(m.mission_id) mission_count, 
		max(a.activation_date) activation_date, 
		max(a.deactivation_date) deactivation_date

	FROM
		agents a
	INNER JOIN

		missions_agents m
	ON
		a.agent_id=m.agent_id
	WHERE
		deactivation_date is not null
	GROUP BY
		a.agent_id
	HAVING
		count(m.mission_id)>7
	)
GROUP BY
	TO_CHAR(activation_date,'YYYY')
)fff
WHERE
	aaa.agent_id=fff.agent_id
)

[Updated on: Thu, 07 August 2008 08:59]

Report message to a moderator

Re: Correlated Subqueries [message #339451 is a reply to message #339048] Thu, 07 August 2008 09:21 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
UPDATE Agents aaa
SET    aaa.deActiVatIon_Date = (SELECT d_Day
                                FROM   (SELECT   MIN(deActiVatIon_Date) d_Day,
                                                 To_char(ActiVatIon_Date,'YYYY') a_Year,
                                                 MAX(Agent_Id) Agent_Id
                                        FROM     (SELECT   a.Agent_Id Agent_Id,
                                                           COUNT(m.Mission_Id) Mission_Count,
                                                           MAX(a.ActiVatIon_Date) ActiVatIon_Date,
                                                           MAX(a.deActiVatIon_Date) deActiVatIon_Date
                                                  FROM     Agents a
                                                           INNER JOIN Missions_Agents m
                                                             ON a.Agent_Id = m.Agent_Id
                                                  WHERE    deActiVatIon_Date IS NOT NULL 
                                                  GROUP BY a.Agent_Id
                                                  HAVING   COUNT(m.Mission_Id) > 7)
                                        GROUP BY To_char(ActiVatIon_Date,'YYYY')) fff
                                WHERE  aaa.Agent_Id = fff.Agent_Id)


which is easier to read?
Do you intend to have any WHERE clause on the UPDATE itself?

[Updated on: Thu, 07 August 2008 09:22] by Moderator

Report message to a moderator

Re: Correlated Subqueries [message #339453 is a reply to message #339451] Thu, 07 August 2008 09:29 Go to previous messageGo to next message
aokwuadigb
Messages: 18
Registered: August 2008
Junior Member

Thanks

The last WHERE clause is supposed to be for the update, but it doesnt work. Looking at other queries like this they have used the WHERE clause like this.
Where am I going wrong?

WHERE aaa.Agent_Id = fff.Agent_Id
Re: Correlated Subqueries [message #339460 is a reply to message #339048] Thu, 07 August 2008 09:45 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
How does the SQL parser know the end of SQL statement starting with "(SELECT d_Day"?
Re: Correlated Subqueries [message #339468 is a reply to message #339460] Thu, 07 August 2008 09:59 Go to previous messageGo to next message
aokwuadigb
Messages: 18
Registered: August 2008
Junior Member
'"(SELECT d_Day' is closed by 'WHERE aaa.Agent_Id = fff.Agent_Id)'.
Re: Correlated Subqueries [message #339475 is a reply to message #339468] Thu, 07 August 2008 10:09 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
aokwuadigb wrote on Thu, 07 August 2008 07:59
'"(SELECT d_Day' is closed by 'WHERE aaa.Agent_Id = fff.Agent_Id)'.


Correct & therefore no WHERE clause is present on the UPDATE itself.
Re: Correlated Subqueries [message #339479 is a reply to message #339475] Thu, 07 August 2008 10:21 Go to previous messageGo to next message
aokwuadigb
Messages: 18
Registered: August 2008
Junior Member
Yes your right, but I have been given an example which uses the same method for the WHERE clause. I thought I matched it but it is not working for me.

The update WHERE clause is 'WHERE dates.datejob = j1.job_id' where j1 refers to the update.

UPDATE jobs j1

SET

      j1.job_date =

      (

            SELECT 

                  youngest

            FROM

            (

                  SELECT

                        max(p.birth_date) youngest,

                        j.job_id datejob

                  FROM

                              plumbers p

                        INNER JOIN

                              plumbers_jobs pj

                        ON

                              pj.plumber_id=p.plumber_id

                        INNER JOIN

                              jobs j

                        ON

                              j.job_id=pj.job_id

                  GROUP BY

                        j.job_id

            ) dates

            WHERE

                  dates.datejob = j1.job_id

      )

;

Re: Correlated Subqueries [message #339482 is a reply to message #339048] Thu, 07 August 2008 10:31 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above

Please use the available SQL Formatter
http://orafaq.com/utilities/sqlformatter.htm

If/when the WHERE is inside the closing parenthesis for the SELECT,
it is NOT used by the UPDATE statement itself!

UPDATE Jobs j1
SET    j1.Job_Date = (SELECT Youngest
                      FROM   (SELECT   MAX(p.Birth_Date) Youngest,
                                       j.Job_Id DateJob
                              FROM     Plumbers p
                                       INNER JOIN Plumbers_Jobs pj
                                         ON pj.Plumber_Id = p.Plumber_Id
                                       INNER JOIN Jobs j
                                         ON j.Job_Id = pj.Job_Id
                              GROUP BY j.Job_Id) Dates
                      WHERE  Dates.DateJob = j1.Job_Id);

>The update WHERE clause is 'WHERE dates.datejob = j1.job_id' where j1 refers to the update.
NO it does not. The WHERE is part of the (sub)SELECT!
Re: Correlated Subqueries [message #339487 is a reply to message #339048] Thu, 07 August 2008 10:43 Go to previous message
aokwuadigb
Messages: 18
Registered: August 2008
Junior Member
Thanks

I will just start from the begining as I think i have headed in the wrong direction with this. I tried to follow the method given without making he correct adjustments needed for my query.

Any suggestions on how I should structure it?

I have got solutions but I am trying to understand how i should approach queries like this in the future.
Previous Topic: Does exception no_data_found requires for this?
Next Topic: Integrity problem with FK
Goto Forum:
  


Current Time: Wed Dec 07 12:22:14 CST 2016

Total time taken to generate the page: 0.10103 seconds