Home » SQL & PL/SQL » SQL & PL/SQL » how to return result with more than one distinct record
how to return result with more than one distinct record [message #351062] Mon, 29 September 2008 02:44 Go to next message
kyo29
Messages: 2
Registered: September 2008
Junior Member
hi,
im a newbie oracle user and right now im having problems with getting result for my application.

my oracle database version is 9i running on windows server 2003 box and im using php to access my database and it is running under redhat enterprise.

so to illustrate what i want to achieve, here's my tables for references.

TBLMAIN - my main table
-------
S_ID COMPANY
1 Caboodles
2 Kitchen King
3 Mossimo

TBLMAIN_SUB - my 2nd table
-----------
S_ID BRANCH
1 USA
1 EUROPE
2 ASIA
3 AFRICA
3 AFRICA

those are my tables and i want to query those companies that have different branches on the 2nd tables, like this:

S_ID COMPANY
1 Caboodles

it should only return 'Caboodles' company because it is the only company that has two different branches.

i created an SQL and im having trouble correcting it, i hope someone can help me out on this:

SELECT a.S_ID, a.COMPANY FROM TBLMAIN AS a LEFT JOIN 
(SELECT S_ID, COUNT(DISTINCT BRANCH) AS CNT FROM TBLMAIN_SUB GROUP BY S_ID) 
AS b ON a.S_ID = b.S_ID WHERE b.CNT > 1 ;


so when i run this script it gives me this error :

"SQL command not properly ended"

i tried revising it but i cant make it work, i appreciate any help. thanks in advance.




Re: how to return result with more than one distinct record [message #351064 is a reply to message #351062] Mon, 29 September 2008 02:48 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Don't use the "AS" keyword when aliasing tables.
icon14.gif  Re: how to return result with more than one distinct record [message #351094 is a reply to message #351062] Mon, 29 September 2008 04:55 Go to previous message
kyo29
Messages: 2
Registered: September 2008
Junior Member
SELECT a.S_ID, a.COMPANY FROM TBLMAIN a LEFT JOIN 
(SELECT S_ID, COUNT(DISTINCT BRANCH) CNT FROM TBLMAIN_SUB GROUP BY S_ID) 
b ON a.S_ID = b.S_ID WHERE b.CNT > 1 ;


that did the trick Very Happy thanks sir
Previous Topic: oracle sql,pl/sql with interfaces
Next Topic: :NEW trigger issue
Goto Forum:
  


Current Time: Sun Dec 04 20:52:24 CST 2016

Total time taken to generate the page: 0.05683 seconds