Home » SQL & PL/SQL » SQL & PL/SQL » anything like using exists
anything like using exists [message #433723] Thu, 03 December 2009 17:12 Go to next message
miroconnect@yahoo.com
Messages: 202
Registered: April 2006
Senior Member
exists

SELECT *
FROM suppliers
WHERE EXISTS
(select *
from orders
where suppliers.supplier_id = orders.supplier_id);

This select statement will return all records from the suppliers table where there is at least one record in the orders table with the same supplier_id.



I must return only suppliers which match condition inside exists,
is there any thing available ?

[Updated on: Fri, 04 December 2009 00:48] by Moderator

Report message to a moderator

Re: anything like using exists [message #433724 is a reply to message #433723] Thu, 03 December 2009 17:23 Go to previous messageGo to next message
BlackSwan
Messages: 24906
Registered: January 2009
Senior Member
You provide us

No Operating System name or version
No Oracle version number (SELECT * from v$version).
No error message (not your interpretation of the actual full and complete message).

No DDL for your table(s)
No DML for test data
No actual SQL to produce your results.
No expected/desired results.
Therefore no assistance for you is possible at this time.

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

[Updated on: Thu, 03 December 2009 17:24]

Report message to a moderator

Re: anything like using exists [message #433727 is a reply to message #433723] Thu, 03 December 2009 19:37 Go to previous messageGo to next message
Kevin Meade
Messages: 2098
Registered: December 1999
Location: Connecticut USA
Senior Member
not sure what your question really is.

Are you asking us about basic sql syntax? This sounds like a homework exercise in which case you should have read you class book chapters on the sql language. If not, then buy a beginners book on sql (maybe SQL For Dummies). From that you will learn the answer to your question whatever because EXISTS is covered even in the simpler books.

Good luck, Kevin
Re: anything like using exists [message #433802 is a reply to message #433723] Fri, 04 December 2009 05:45 Go to previous messageGo to next message
jagadeesh1985
Messages: 4
Registered: November 2009
Location: Hyderabad
Junior Member
if u want get your desired answer you can use a simple join condition like below
select s.* from suppliers s, orders o where s.supplierid=o.supplierid;
Re: anything like using exists [message #433832 is a reply to message #433723] Fri, 04 December 2009 07:58 Go to previous messageGo to next message
LKBrwn_DBA
Messages: 487
Registered: July 2003
Location: WPB, FL
Senior Member
If you want this:
miroconnect@yahoo.com wrote on Thu, 03 December 2009 18:12
I must return only suppliers which match condition inside exists,
is there any thing available ?

And the exists condition is: "where suppliers.supplier_id = orders.supplier_id", then is this not your expected result?:
miroconnect@yahoo.com wrote on Thu, 03 December 2009 18:12
This select statement will return all records from the suppliers table where there is at least one record in the orders table with the same supplier_id.

DUH! Shocked

[Updated on: Fri, 04 December 2009 07:59]

Report message to a moderator

Re: anything like using exists [message #433849 is a reply to message #433832] Fri, 04 December 2009 09:02 Go to previous messageGo to next message
miroconnect@yahoo.com
Messages: 202
Registered: April 2006
Senior Member
Ok its not a simple sql dummies question,
I have a complex search query.
I have main table and several children

Ok let me explain in detail


this is my query
select * from (
SELECT audit_number, formatted_audit_number, ag.sys_audit_id, audit_begin_date, audit_end_date, auditeename,
ein, duns, city, state, org.award_org_abbrev, audit_review_code, audit_issue_date, oig_due_date,

( SELECT wmsys.wm_concat (gp.prog_acronym)
FROM inf_grant_program gp, ea_audit_program ap
WHERE ap.sys_prog_id = gp.sys_prog_id
AND ap.sys_audit_id = ag.sys_audit_id ) AS prog_acronyms,
( SELECT wmsys.wm_concat (ga.grant_nbr)
FROM inf_grant_award ga, ea_audit_program ap, ea_assign_audit_prog_grants apg
WHERE apg.sys_audit_prog_id = ap.sys_audit_prog_id
AND apg.sys_inf_ga_id = ga.sys_inf_ga_id
AND ap.sys_audit_id = ag.sys_audit_id ) AS grant_no,
( SELECT COUNT (fin.finding_number)
FROM ea_finding fin
WHERE fin.sys_audit_id = ag.sys_audit_id) AS no_of_findings
FROM ea_audit_general ag, inf_organization org
where ag.sys_org_id = org.sys_org_id )
WHERE award_org_abbrev=':PARAM_ORG_ABBREV'
for this query the columns I am retrieving are in the query ,


prog_acronyms,grant_no,no_of_findings



prog_acronyms,grant_no these are comma seperated values as u know wmsys.wm_concat does
and no_of_findings is the count of course ,

so My audit , has many programs , many grants and many findings


My query also needs pagination so If I join to any many related tables pagination gets distrubed , so I cannot use join.
next I if Have to add a filter to query lets say program acronym is 'FM' and grant_no is 12345

I have to append to this query
and sys_audit_id in (
select sys_audit_id from (program acronym filter) ,
union
select sys_audit_id from ( grants_no filter )

)
now If I use this approach the search conditions becomes or , not and , i need suggestions what else I can do to improve this , are ther any better ways to handle my situation ?



Re: anything like using exists [message #433853 is a reply to message #433849] Fri, 04 December 2009 09:41 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You do know that wmsys.wm_concat is an undocumented and unsupported function, don't you?

I don't understand why you can't just add
and sys_audit_id in (
select sys_audit_id from (program acronym filter)
union
select sys_audit_id from ( grants_no filter )


to the where clause - it won't cause duplicate rows.

I have no idea what you mean byQuote:
f I use this approach the search conditions becomes or , not and
Re: anything like using exists [message #433854 is a reply to message #433853] Fri, 04 December 2009 10:01 Go to previous messageGo to next message
miroconnect@yahoo.com
Messages: 202
Registered: April 2006
Senior Member
I know its not documented, I can ofcouse replace it with my own function.

but the second using sys_audit_id in

select sys_audit_id from (program acronym filter) (returns records and this does not satisfy ( grants_no filter ) )
union
select sys_audit_id from ( grants_no filter ) (returns 10 records
and the records returned from this does not satisfy first (program acronym filter) )


when User enters multiple search filters I should use and not or , so please tell me how to handle this ?

also

is there any oracle function for checking if a search string is present in a string

boolean contains(String str,String searchStr)

Checks if String contains a search

[Updated on: Fri, 04 December 2009 10:22]

Report message to a moderator

Re: anything like using exists [message #433864 is a reply to message #433854] Fri, 04 December 2009 10:44 Go to previous messageGo to next message
BlackSwan
Messages: 24906
Registered: January 2009
Senior Member
>is there any oracle function for checking if a search string is present in a string
INSTR
Re: anything like using exists [message #434140 is a reply to message #433854] Mon, 07 December 2009 07:19 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Try using
and sys_audit_id in (
select sys_audit_id from (program acronym filter)
INTERSECT
select sys_audit_id from ( grants_no filter )
Previous Topic: Table Space (merged)
Next Topic: Update table c1 for all entries that matches table c2 based on matching column a
Goto Forum:
  


Current Time: Tue Sep 27 04:14:54 CDT 2016

Total time taken to generate the page: 0.12844 seconds