Home » SQL & PL/SQL » SQL & PL/SQL » query help
query help [message #293074] Thu, 10 January 2008 15:47 Go to next message
roopla
Messages: 52
Registered: May 2006
Member
CREATE TABLE t (f1 NUMBER, val VARCHAR2(3))
2 /

Table created.

SQL> INSERT INTO t VALUES (1, 'E');
SQL> INSERT INTO t VALUES (1, 'W');
SQL> INSERT INTO t VALUES (2, 'E');
SQL> INSERT INTO t VALUES (3, 'E');
SQL> INSERT INTO t VALUES (4, 'W');
SQL> INSERT INTO t VALUES (4, 'E');

I want to select rows which has both 'E' and 'W' in both f1 field not one of them.

Select query should return
1
4

Please help
Re: query help [message #293076 is a reply to message #293074] Thu, 10 January 2008 15:57 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
FROM T T1, T T2

& YES, I realize a more efficient solution exists but it is easier to give this clue

[Updated on: Thu, 10 January 2008 15:59] by Moderator

Report message to a moderator

Re: query help [message #293103 is a reply to message #293074] Thu, 10 January 2008 22:19 Go to previous messageGo to next message
navkrish
Messages: 189
Registered: May 2006
Location: NJ,USA
Senior Member

select f1 
  from (
         select distinct 
                f1,val
           from t 
 	  where val in ('E','W')
        )
  group by f1
 having count(*)>1;


regards,
naveen
Re: query help [message #293111 is a reply to message #293074] Thu, 10 January 2008 22:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
COUNT
Regards
Michel
Re: query help [message #293112 is a reply to message #293074] Thu, 10 January 2008 23:01 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
what happens to proposed solutions when the table data is as follows?

SQL> INSERT INTO t VALUES (1, 'E');
SQL> INSERT INTO t VALUES (1, 'W');
SQL> INSERT INTO t VALUES (2, 'W');
SQL> INSERT INTO t VALUES (2, 'W');
SQL> INSERT INTO t VALUES (3, 'E');
SQL> INSERT INTO t VALUES (3, 'E');
SQL> INSERT INTO t VALUES (4, 'W');
SQL> INSERT INTO t VALUES (4, 'E');

[Updated on: Thu, 10 January 2008 23:01] by Moderator

Report message to a moderator

Re: query help [message #293115 is a reply to message #293112] Thu, 10 January 2008 23:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Some work others don't. Wink

Regards
Michel
Re: query help [message #293119 is a reply to message #293074] Thu, 10 January 2008 23:38 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

You can try Proposed solution as suggested Ana . It is called Self Join technique .

Or else Correlated subquery technique.

select distinct F1 from T
where exists( select NULL from T t1
	  		  where t1.f1 = t.f1
			  and   t1.VAL ='E')
AND exists( select NULL from T t2
	  		  where t2.f1 = t.f1
			  and   t2.VAL ='W')


Thumbs Up
Rajuvan

[Updated on: Thu, 10 January 2008 23:47]

Report message to a moderator

Re: query help [message #293126 is a reply to message #293119] Fri, 11 January 2008 00:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Let him work rather than posting a query that is far from the best one.

Regards
Michel

[Updated on: Fri, 11 January 2008 00:07]

Report message to a moderator

Re: query help [message #293143 is a reply to message #293074] Fri, 11 January 2008 00:29 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Here Nobody is claiming about the Best Query .

I am just trying to point to some ways of doing the same work . I dont thing it is wrong to do , as this is Newbie forum and Not SQL expert forum.

Thumbs Up
Rajuvan.
Re: query help [message #293152 is a reply to message #293143] Fri, 11 January 2008 00:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

this is Newbie forum and Not SQL expert forum.

This is the reason to give clue and not solution in Newbies forum otherwise they don't learn.
Expert forums are the place to give solutions as these are hard to find and/or to explain just in words.

Why don't you just mention the possibility to use EXISTS condition as you did for self-join? This is a good answer for newbies.

Regards
Michel

[Updated on: Fri, 11 January 2008 00:40]

Report message to a moderator

Re: query help [message #293164 is a reply to message #293152] Fri, 11 January 2008 01:11 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Set theory might also help to get the result:
SELECT f1 FROM t WHERE val = 'E'
INTERSECT
SELECT f1 FROM t WHERE val = 'W';
Re: query help [message #293165 is a reply to message #293074] Fri, 11 January 2008 01:11 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

I agree Michel.
But this is not the first time One is giving Direct Answer in Newbie setion . Smile

Well , I will take care in future.

Thumbs Up
Rajuvan

[Updated on: Fri, 11 January 2008 01:13]

Report message to a moderator

Re: query help [message #293182 is a reply to message #293165] Fri, 11 January 2008 02:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you are talking about my posts:

First link, there were many answers before, this was just to correct previous ones and add one more efficient. In this case OP already showed what he tried, just a fix.

Second link, idem another solution after several ones and in addition this is absolutly not the same kind of question.

Quote:

Well , I will take care in future.

You already said that. Wink

Regards
Michel

Re: query help [message #293185 is a reply to message #293074] Fri, 11 January 2008 02:19 Go to previous messageGo to next message
boukerker
Messages: 11
Registered: January 2008
Location: Paris
Junior Member
another query
select f1, count(*)
where val = 'E' or val = 'w'
group by f1
HAVING Count(*) > 1

Regards,
boukerker

[Updated on: Fri, 11 January 2008 06:35]

Report message to a moderator

Re: query help [message #293189 is a reply to message #293185] Fri, 11 January 2008 02:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Good first step but this does not solve the case posted by Ana (anacedent).

Next time, 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. Use the "Preview Message" button to verify.


Regards
Michel

[Updated on: Fri, 11 January 2008 02:37]

Report message to a moderator

Re: query help [message #293201 is a reply to message #293074] Fri, 11 January 2008 03:34 Go to previous message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

I was not particularly interested to point out your case .
(Your presrnce is there everywhere in the forum ) Wink
I was taking some example in general .

Quote:

Well , I will take care in future.


Let me change it to .

Quote:

Well , I will try to take care in future


Smile

Thumbs Up
Rajuvan.
Previous Topic: Problem in locking
Next Topic: Multi Dimensional Array
Goto Forum:
  


Current Time: Wed Dec 07 20:46:28 CST 2016

Total time taken to generate the page: 0.11133 seconds