| View previous topic :: View next topic |
| Author |
Message |
pvrajesh31
Beginner
Joined: 10 Aug 2004
Posts: 31
Topics: 11
|
 Posted: Tue Jul 11, 2006 1:48 am Post subject: SQL for Unique Business Owners |
 |
|
Hi ,
I need a SQL for the following requirement
i have a database with two columns
column1 - Owner
column2 - business
both columns are part of primary key.
A owner can have more than one business like cloth,retail,supermarket,cinema,etc. But i want to find the owner who has only one business i.e. cinema. Owners who has only cinema business and no other business.
Please help me out ....
Thanks in advance.
_________________
Rajesh |
|
| Back to top |
|
 |
shekar123
Advanced
Joined: 22 Jul 2005
Posts: 528
Topics: 90
Location: Bangalore India
|
| Posted: Tue Jul 11, 2006 3:13 am Post subject: |
|
|
Rajesh,
I had created a table in the following way and inserted values.Please check if this is the kind of situation you are looking for ?
| Code: |
CREATE TABLE NEW
(OWNER CHAR(10) NOT NULL,
BUSINESS CHAR(10) NOT NULL,
PRIMARY KEY(OWNER,BUSINESS));
CREATE UNIQUE INDEX NEW1 ON NEW(OWNER,BUSINESS);
INSERT INTO NEW VALUES('SHEKAR','CINEMA');
INSERT INTO NEW VALUES('SHEKAR','CLOTH');
INSERT INTO NEW VALUES('SHEKAR','RETAIL');
INSERT INTO NEW VALUES('SHEKAR','MARKET');
INSERT INTO NEW VALUES('KOLUSU','CINEMA');
INSERT INTO NEW VALUES('KOLUSU','CLOTH');
INSERT INTO NEW VALUES('KOLUSU','RETAIL');
INSERT INTO NEW VALUES('KOLUSU','MARKET');
SELECT * FROM NEW;
|
OUPUT
| Code: |
SELECT * FROM NEW;
---------+---------+
OWNER BUSINESS
---------+---------+
KOLUSU CINEMA
KOLUSU CLOTH
KOLUSU MARKET
KOLUSU RETAIL
SHEKAR CINEMA
SHEKAR CLOTH
SHEKAR MARKET
SHEKAR RETAIL
|
Now use the query to execute your output.
| Code: |
SELECT OWNER FROM NEW WHERE BUSINESS = 'CINEMA';
---------+---------+---------+---------+--------
OWNER
---------+---------+---------+---------+--------
KOLUSU
SHEKAR
|
_________________
Shekar
Grow Technically |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12384
Topics: 75
Location: San Jose
|
| Posted: Tue Jul 11, 2006 4:36 am Post subject: |
|
|
| Quote: |
Owners who has only cinema business and no other business.
|
Shekhar,
The poster wanted to find out owners with Just cinema business. According to your data , none of the rows would qualify.
Pvrajesh,
Try this
| Code: |
SELECT *
FROM TABLE
WHERE OWNER NOT IN (SELECT OWNER
FROM TABLE
WHERE BUSINESS = 'CINEMA')
;
|
Hope this helps...
Cheers
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
vivek1983
Intermediate
Joined: 20 Apr 2006
Posts: 222
Topics: 24
|
| Posted: Tue Jul 11, 2006 4:43 am Post subject: |
|
|
Rajesh,
| Quote: |
Owners who has only cinema business and no other business
|
Please try this query:
| Code: |
SELECT
owner
business
FROM Table1
WHERE owner IN (SELECT owner FROM
Table1 GROUP BY owner
HAVING COUNT(busniess) <= 1)
AND business = 'cinema'
FOR FETCH ONLY WITH UR
|
Hope this helps.
Regards,
Vivek.G |
|
| Back to top |
|
|
pvrajesh31
Beginner
Joined: 10 Aug 2004
Posts: 31
Topics: 11
|
| Posted: Tue Jul 11, 2006 5:55 am Post subject: |
|
|
Hi Vivek,
Thanks for the SQL. Your SQL is giving the results i wanted.
Kolusu,
Actually your SQL is giving all owners who does not have 'Cinema' business,but i need to find owners having only 'cinema' business and no other business.
Thanks to all for helping me in finding the way.
Rajesh
_________________
Rajesh |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12384
Topics: 75
Location: San Jose
|
| Posted: Tue Jul 11, 2006 6:42 am Post subject: |
|
|
pvrajesh,
You just need to add an AND clause to the outer select.
| Code: |
SELECT *
FROM TABLE
WHERE OWNER NOT IN (SELECT OWNER
FROM TABLE
WHERE BUSINESS = 'CINEMA')
AND BUSINESS = 'CINEMA'
;
|
Hope this helps...
Cheers
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
vivek1983
Intermediate
Joined: 20 Apr 2006
Posts: 222
Topics: 24
|
| Posted: Tue Jul 11, 2006 6:53 am Post subject: |
|
|
kolusu,
The query will return no rows since
the predeciates are contradictory.
Meaning .. NOT IN (SELECT OWNER
FROM TABLE
HERE BUSINESS = 'CINEMA') will select rows that corresponds to non-cinema whereas AND BUSINESS = 'CINEMA' corresponds to cinema-rows.
Please let me know if i have missed something.
Thanks,
Vivek.G
_________________
Vivek G
--------------------------------------
A dream is just a dream. A goal is a dream with a plan and a deadline. (Harvey Mackay) |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12384
Topics: 75
Location: San Jose
|
| Posted: Tue Jul 11, 2006 8:15 am Post subject: |
|
|
vivek1983,
Yes you are right. The sql I gave Does not yield the desired results. Sorry.
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
pvrajesh31
Beginner
Joined: 10 Aug 2004
Posts: 31
Topics: 11
|
| Posted: Tue Jul 11, 2006 8:16 am Post subject: |
|
|
Hi Vivek,
You are right.
That SQL will always give SQL code 100. Because outer select will always get owners who does not have cinema business.
Kolusu,
I made a small correction in the SQL then it is working. Pls see below
| Code: |
SELECT *
FROM TABLE
WHERE OWNER NOT IN (SELECT OWNER
FROM TABLE
WHERE BUSINESS <> 'CINEMA')
AND BUSINESS = 'CINEMA'
;
|
I hope this is correct. Please advice.
Thanks
Rajesh
_________________
Rajesh |
|
| Back to top |
|
|
coolman
Intermediate
Joined: 03 Jan 2003
Posts: 283
Topics: 27
Location: US
|
| Posted: Fri Jul 14, 2006 11:14 am Post subject: |
|
|
would this not help?
| Code: |
SELECT COL1 FROM TABLE1
GROUP BY COL1
HAVING COUNT(*) = 1
AND COL2 = 'CINEMA'
|
________
marijuana vaporizer
Last edited by coolman on Sat Feb 05, 2011 1:44 am; edited 1 time in total |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12384
Topics: 75
Location: San Jose
|
| Posted: Fri Jul 14, 2006 12:06 pm Post subject: |
|
|
coolman,
Did you test your sql ? The query will not work.
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
coolman
Intermediate
Joined: 03 Jan 2003
Posts: 283
Topics: 27
Location: US
|
| Posted: Mon Jul 17, 2006 4:25 pm Post subject: |
|
|
No, I didn't test it. I don't have access to creating tables. Btw, is it because of the composite key that the group by would fail?
________
Carrozzeria Ghia
Last edited by coolman on Sat Feb 05, 2011 1:44 am; edited 1 time in total |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12384
Topics: 75
Location: San Jose
|
| Posted: Tue Jul 18, 2006 8:00 am Post subject: |
|
|
| coolman wrote: | | Btw, is it because of the composite key that the group by would fail? |
You are using a column function Count(*) and you are checking for COl2 which is not a part of the Select query.
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
