Home » SQL & PL/SQL » SQL & PL/SQL » select ceratin records with the same 'chain_id' when one record has a 'completion_code' = 'RPC'
select ceratin records with the same 'chain_id' when one record has a 'completion_code' = 'RPC' [message #300392] Fri, 15 February 2008 05:03 Go to next message
dawild
Messages: 26
Registered: July 2005
Location: England
Junior Member
Hi,

I'm looking to write an SQL statement that looks at the field 'completion_code' and I want to show all records that have a 'completion_code' that is 'RPC' and also show any other records that have the same 'chain_id' as the records that have a 'completion_code' that is 'RPC'.

Any help would be greatly appreciated?

Thanks,

Dean
Re: select ceratin records with the same 'chain_id' when one record has a 'completion_code' = 'RPC' [message #300393 is a reply to message #300392] Fri, 15 February 2008 05:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64137
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post what you already try and where you are stuck.

But before please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code.
Use the "Preview Message" button to verify.

Regards
Michel

[Updated on: Fri, 15 February 2008 05:08]

Report message to a moderator

Re: select ceratin records with the same 'chain_id' when one record has a 'completion_code' = 'RPC' [message #300394 is a reply to message #300392] Fri, 15 February 2008 05:08 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
Quote:
Any help would be greatly appreciated?

No CREATE TABLE and INSERT statments provided.how can any one help you with what you have described in your post?


regards,
Re: select ceratin records with the same 'chain_id' when one record has a 'completion_code' = 'RPC' [message #300401 is a reply to message #300394] Fri, 15 February 2008 05:37 Go to previous messageGo to next message
dawild
Messages: 26
Registered: July 2005
Location: England
Junior Member
Sorry dhananjay but I don't know what you mean by your post, 'no CREATE TABLE and INSERT statements provided' as I'm only a newbie regarding SQL, can you be more specific?

I don't currently have any code to try as I've no idea how it can be done, obviously to select a 'completion_code' that is an 'RPC then the code would be: -

SELECT name, completion_code, chain_id FROM calling_list
WHERE completion_code = 'RPC'

but I've no idea how I would then also return other records with the same chain_id that have a completion_code = 'RPC'.

Any help would be appreciated please,

Thanks
Re: select ceratin records with the same 'chain_id' when one record has a 'completion_code' = 'RPC' [message #300407 is a reply to message #300401] Fri, 15 February 2008 05:45 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
Quote:
No CREATE TABLE and INSERT statments provided.

i meant you could provide us with a small test case.with CREATE TABLE statements and some INSERT statements.and your desired output .so that we don't waste time in creating dummy tables/records.don't forget to format your code.you can check out the forum guide lines.



PS :check @MICHEL's post.


regards,
Re: select ceratin records with the same 'chain_id' when one record has a 'completion_code' = 'RPC' [message #300413 is a reply to message #300407] Fri, 15 February 2008 06:01 Go to previous messageGo to next message
dawild
Messages: 26
Registered: July 2005
Location: England
Junior Member
Sorry guys, please see below for my question with formatting on my code: -

I don't currently have any code to try as I've no idea how it can be done, obviously to select a 'completion_code' that is an 'RPC then the code would be: -

SELECT name, completion_code, chain_id FROM calling_list
WHERE completion_code = 'RPC'


but I've no idea how I would then also return other records which have the same chain_id that have a completion_code = 'RPC'.

If you need any futher information please let me know, any help would be appreciated please?

Thanks
Re: select ceratin records with the same 'chain_id' when one record has a 'completion_code' = 'RPC' [message #300414 is a reply to message #300413] Fri, 15 February 2008 06:09 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
How would you select records that have a chain_id equal to 'X'?
How would you select the chain_id from records that have a completion_code equal to 'RPC'?

How would you join these two queries? Hint: use the same table twice in the FROM clause.
Re: select ceratin records with the same 'chain_id' when one record has a 'completion_code' = 'RPC' [message #300418 is a reply to message #300413] Fri, 15 February 2008 06:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64137
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Another way: your query gives the chain_id for this completion code and you want all rows where chain_id is IN this list.

Regards
Michel
Re: select ceratin records with the same 'chain_id' when one record has a 'completion_code' = 'RPC' [message #300424 is a reply to message #300414] Fri, 15 February 2008 06:28 Go to previous messageGo to next message
dawild
Messages: 26
Registered: July 2005
Location: England
Junior Member
Hi Frank,

Q. How would you select records that have a chain_id equal to 'X'?

A.
SELECT name, chain_id FROM calling_list
WHERE chain_id = 'X'


Q. How would you select the chain_id from records that have a completion_code equal to 'RPC'?

A.
SELECT name, completion_code, chain_id FROM calling_list
WHERE completion_code = 'RPC'


Q. How would you join these two queries? Hint: use the same table twice in the FROM clause.

A. That's the part I'm struggling with. Sorry for not understanding your hint but I only use SQL a few times a year and only have a really basic knowledge in it.

Michel - Sorry but your latest post doesnt make any sense to me.
Re: select ceratin records with the same 'chain_id' when one record has a 'completion_code' = 'RPC' [message #300425 is a reply to message #300424] Fri, 15 February 2008 06:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64137
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Merge your second query inside in the first one.
The second query gives what must in place of the 'X' in the first one.

Regards
Michel

[Updated on: Fri, 15 February 2008 06:30]

Report message to a moderator

Re: select ceratin records with the same 'chain_id' when one record has a 'completion_code' = 'RPC' [message #300428 is a reply to message #300425] Fri, 15 February 2008 06:40 Go to previous messageGo to next message
dawild
Messages: 26
Registered: July 2005
Location: England
Junior Member
As I said, merging the two queries together is the part I'm struggling with. If I had to have a go at doing it I would say the following: -

SELECT name, chain_id FROM calling_list
WHERE chain_id = (SELECT name, completion_code, chain_id FROM calling_list
WHERE completion_code = 'RPC')


Is this anywhere near correct?

Thanks
Re: select ceratin records with the same 'chain_id' when one record has a 'completion_code' = 'RPC' [message #300435 is a reply to message #300424] Fri, 15 February 2008 06:54 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
dawild wrote on Fri, 15 February 2008 13:28


Q. How would you select the chain_id from records that have a completion_code equal to 'RPC'?

A.
SELECT name, completion_code, chain_id FROM calling_list
WHERE completion_code = 'RPC'



Reread the question
Reread the answer

Did I ask for name or completion_code?
Re: select ceratin records with the same 'chain_id' when one record has a 'completion_code' = 'RPC' [message #300436 is a reply to message #300392] Fri, 15 February 2008 07:13 Go to previous messageGo to next message
dawild
Messages: 26
Registered: July 2005
Location: England
Junior Member
Sorry, put name, completion_code in by mistake: -

A.
SELECT name, completion_code, chain_id FROM calling_list
WHERE completion_code = 'RPC'


Therefore adding the two together would look like: -
SELECT name, chain_id FROM calling_list
WHERE chain_id = (SELECT chain_id FROM calling_list
WHERE completion_code = 'RPC')


Is this correct?

Thanks
Re: select ceratin records with the same 'chain_id' when one record has a 'completion_code' = 'RPC' [message #300439 is a reply to message #300436] Fri, 15 February 2008 07:21 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
dawild wrote on Fri, 15 February 2008 14:13

SELECT name, chain_id FROM calling_list
WHERE chain_id = (SELECT chain_id FROM calling_list
WHERE completion_code = 'RPC')


Is this correct?

Thanks


Almost.

WHERE chain_id = (SELECT chain_id ...)


What happens when the SUB-SELECT retrieves more than one row?

ORA-01427: single-row subquery returns more than one row


But that's easily fixed!
Re: select ceratin records with the same 'chain_id' when one record has a 'completion_code' = 'RPC' [message #300440 is a reply to message #300436] Fri, 15 February 2008 07:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64137
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
dawild wrote on Fri, 15 February 2008 14:13
Therefore adding the two together would look like: -
SELECT name, chain_id FROM calling_list
WHERE chain_id = (SELECT chain_id FROM calling_list
WHERE completion_code = 'RPC')


Is this correct?

Thanks

Close just reread what I said:
Quote:
you want all rows where chain_id is IN this list.

Regards
Michel

Re: select ceratin records with the same 'chain_id' when one record has a 'completion_code' = 'RPC' [message #300447 is a reply to message #300440] Fri, 15 February 2008 07:47 Go to previous messageGo to next message
dawild
Messages: 26
Registered: July 2005
Location: England
Junior Member
Think it's all coming together now and making sense, how does the following look?

SELECT name, chain_id FROM calling_list
WHERE chain_id IS IN (SELECT chain_id FROM calling_list
WHERE completion_code = 'RPC')


Re: select ceratin records with the same 'chain_id' when one record has a 'completion_code' = 'RPC' [message #300448 is a reply to message #300447] Fri, 15 February 2008 07:48 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
That looks good.
Now how will you combine the two?
Re: select ceratin records with the same 'chain_id' when one record has a 'completion_code' = 'RPC' [message #300453 is a reply to message #300447] Fri, 15 February 2008 07:53 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
dawild wrote on Fri, 15 February 2008 14:47
Think it's all coming together now and making sense, how does the following look?

SELECT name, chain_id FROM calling_list
WHERE chain_id IS IN (SELECT chain_id FROM calling_list
WHERE completion_code = 'RPC')





Remove IS from the IS IN and you're there.

Any chance for you to try this query?
icon14.gif  Re: select ceratin records with the same 'chain_id' when one record has a 'completion_code' = 'RPC' [message #300465 is a reply to message #300453] Fri, 15 February 2008 09:04 Go to previous messageGo to next message
dawild
Messages: 26
Registered: July 2005
Location: England
Junior Member
Thanks very much for your guidance guys, very much appreciated. Smile
Re: select ceratin records with the same 'chain_id' when one record has a 'completion_code' = 'RPC' [message #300503 is a reply to message #300453] Fri, 15 February 2008 12:39 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
MarcS wrote on Fri, 15 February 2008 14:53
Remove IS from the IS IN and you're there.


Frank wrote on Fri, 15 February 2008 14:48
That looks good.

whoops
Previous Topic: Round Function
Next Topic: Need an average by year of an average by month
Goto Forum:
  


Current Time: Thu Dec 08 06:31:42 CST 2016

Total time taken to generate the page: 0.14351 seconds