Home » SQL & PL/SQL » SQL & PL/SQL » And condition within the same table
And condition within the same table [message #292098] Mon, 07 January 2008 16:48 Go to next message
kharpoh
Messages: 1
Registered: January 2008
Junior Member
Hello all,

Was wondering if anyone knows what's the best and most efficient way to query for something similar to the example below.

Table1
ID CID VALUE
-- --- -----
1 1 a
2 2 b
3 3 c
4 1 c

Find me all customer id where value is a and c
This should only bring back cid = 1.

Thanks in advance
Re: And condition within the same table [message #292100 is a reply to message #292098] Mon, 07 January 2008 16:57 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
FROM TABLE1 T1, TABLE1 T2

[Updated on: Mon, 07 January 2008 16:57] by Moderator

Report message to a moderator

Re: And condition within the same table [message #292133 is a reply to message #292100] Mon, 07 January 2008 23:55 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Since table 1 t2 is not used in the select it should be removed from the "from" list. (the mantra of a well-known and well-respected member Wink )

As an alternative to using the source-table twice, you can use "where exists"
Re: And condition within the same table [message #292139 is a reply to message #292098] Tue, 08 January 2008 00:30 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
Yes, it is better to follow Frank's advice.
Re: And condition within the same table [message #292141 is a reply to message #292098] Tue, 08 January 2008 00:36 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
For each customer you can could the number of a and c and only return whose count reflects there are both a and c (keywords: count, decode/case, group by, having).

Regards
Michel
Previous Topic: Error Ora -0054 in transaction
Next Topic: problem insert clob with more than 4000 bytes
Goto Forum:
  


Current Time: Sat Dec 03 03:38:24 CST 2016

Total time taken to generate the page: 0.04889 seconds