Home » SQL & PL/SQL » SQL & PL/SQL » Query Problem
icon8.gif  Query Problem [message #590039] Sat, 13 July 2013 13:06 Go to next message
rasi_85
Messages: 9
Registered: May 2008
Junior Member
Hi,

Oracle Version: 10g

I want to select which are the mwtmg_team_group_id are not having PRIMARY_IN ='Y' in group_member_mwgrm table. But below query is returning "0" records.

Can you please correct the below query and let me know what is the missing? But in the table we have data primary_in ='N' values.


Query 1:
-----------

-- 0 rows

select distinct mwtmg_team_group_id
from group_member_mwgrm
where mwrol_member_role_id IN (3,4,15,16) and del_in='N' and
mwtmg_team_group_id not in( select mwtmg_team_group_id
from group_member_mwgrm t1
where exists (select 1
from group_member_mwgrm t2
where t1.mwtmg_team_group_id = t2.mwtmg_team_group_id and t1.primary_in='Y')
) order by mwtmg_team_group_id;




Thanks,.
Rasi
Re: Query Problem [message #590050 is a reply to message #590039] Sat, 13 July 2013 13:24 Go to previous messageGo to next message
Michel Cadot
Messages: 59821
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From your previous topic:

BlackSwan wrote on Mon, 08 July 2013 20:09
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/


Michel Cadot wrote on Mon, 08 July 2013 20:58
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals.

Use SQL*Plus and copy and paste your session, the WHOLE session including the error, procedure creation and execution.

Regards
Michel


And I add: With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

And don't forget:

Michel Cadot wrote on Tue, 26 February 2013 16:55
...
Tell me and I'll forget; show me and I may remember; involve me and I'll understand.

Regards
Michel


And If you don't know how to format a query, learn it using SQL Formatter.

Re: Query Problem [message #590075 is a reply to message #590050] Sun, 14 July 2013 04:16 Go to previous messageGo to next message
Littlefoot
Messages: 19824
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
I want to select which are the mwtmg_team_group_id are not having PRIMARY_IN ='Y' in group_member_mwgrm table

select mwtmg_team_group_id
from group_member_mwgrm
where primary_in <> 'Y'
Re: Query Problem [message #590109 is a reply to message #590039] Sun, 14 July 2013 23:29 Go to previous message
Lalit Kumar B
Messages: 2522
Registered: May 2013
Location: World Wide on the Web
Senior Member
rasi_85 wrote on Sat, 13 July 2013 23:36

Can you please correct the below query and let me know what is the missing? But in the table we have data primary_in ='N' values.


We do not have the DDLs so can't check, but, the same query can be written in this way:-
SELECT DISTINCT MWTMG_TEAM_GROUP_ID
  FROM GROUP_MEMBER_MWGRM
 WHERE MWROL_MEMBER_ROLE_ID IN (3, 4, 15, 16)
   AND DEL_IN = 'N'
   AND MWTMG_TEAM_GROUP_ID <> 'Y'
 ORDER BY MWTMG_TEAM_GROUP_ID;


Simple...isn't it?

Now, please tell the reason why you thought to use EXISTS. Hope you understand that EXISTS does not scan all the rows, only 1 row will suffice for the condition.

Please refer the link for usage of EXISTS : http://www.orafaq.com/wiki/EXISTS
Previous Topic: Analytic function
Next Topic: Selecting the data greater than some value into one column
Goto Forum:
  


Current Time: Fri Nov 28 17:13:22 CST 2014

Total time taken to generate the page: 0.10482 seconds