OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » Select Query

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Select Query [message #8601]

Wed, 03 September 2003 08:09

Michele
Messages: 77
Registered: December 2000

Member

Hi,

I have the below 2 tables. I need to return the company name of records that have a sp_space of 80 and 86 for ad_ed = '04'.

I came up with the below query where I just searched against Table B for a count but I was looking for a way to include the company name.

Table A
ad_acct ad_co1 ad_ed ad_pub
X123456, 3M, 04, 01
T987654, Nabisco, 04, 01
L787878, Mead Inc, 04, 01
L787878, Mead Inc, 03, 01

Table B
sp_acct sp_space sp_ed sp_pub
X123456, 86, 04, 01
X123456, 80, 04, 01
T987654, 86, 04, 01
L787878, 80, 04, 01
L787878, 80, 03, 01

What I did:

SELECT count(*)
FROM (select * from Table B where sp_space = '86' and sp_ed = '04')t, (select * from Table B where sp_space = '80' and sp_ed = '04') s
WHERE t.sp_acct=s.sp_acct;

**Now I would like to return a list of the companies. So the above would return just one result:

3M

Thanks
Michele

Report message to a moderator

Re: Select Query [message #8602 is a reply to message #8601]

Wed, 03 September 2003 08:26

Art Metzer
Messages: 2480
Registered: December 2002

Senior Member

Michele,

What about the following?

SQL> CREATE TABLE table_a (
  2      ad_acct     VARCHAR2(7)
  3  ,   ad_co1      VARCHAR2(8)
  4  ,   ad_ed       VARCHAR2(2)
  5  ,   ad_pub      VARCHAR2(2)
  6  );
SQL> INSERT INTO table_a VALUES ('X123456', '3M', '04', '01');
SQL> INSERT INTO table_a VALUES ('T987654', 'Nabisco', '04', '01');
SQL> INSERT INTO table_a VALUES ('L787878', 'Mead Inc', '04', '01');
SQL> INSERT INTO table_a VALUES ('L787878', 'Mead Inc', '03', '01');
SQL> CREATE TABLE table_b (
  2      sp_acct     VARCHAR2(7)
  3  ,   sp_space    VARCHAR2(2)
  4  ,   sp_ed       VARCHAR2(2)
  5  ,   sp_pub      VARCHAR2(2)
  6  );
SQL> INSERT INTO table_b VALUES ('X123456', '86', '04', '01');
SQL> INSERT INTO table_b VALUES ('X123456', '80', '04', '01');
SQL> INSERT INTO table_b VALUES ('T987654', '86', '04', '01');
SQL> INSERT INTO table_b VALUES ('L787878', '80', '04', '01');
SQL> INSERT INTO table_b VALUES ('L787878', '80', '03', '01');
SQL> COMMIT;
  
SQL> SELECT 
  2  DISTINCT a.ad_co1
  3  FROM     table_a        a
  4  ,        table_b        b
  5  WHERE    EXISTS (SELECT NULL
  6                   FROM   table_b   b1
  7                   WHERE  b1.sp_acct = b.sp_acct
  8                   AND    b1.sp_space = '80'
  9                   AND    b1.sp_ed = '04')
 10  AND      EXISTS (SELECT NULL
 11                   FROM   table_b   b2
 12                   WHERE  b2.sp_acct = b.sp_acct
 13                   AND    b2.sp_space = '86'
 14                   AND    b2.sp_ed = '04')
 15  AND      b.sp_acct = a.ad_acct
 16  /
  
AD_CO1
--------
3M
  
SQL>

Report message to a moderator

Re: Select Query [message #8603 is a reply to message #8602]

Wed, 03 September 2003 08:32

Michele
Messages: 77
Registered: December 2000

Member

Perfect!

Thanks Art!

Michele

Report message to a moderator

Previous Topic:	finding max date in table of numerous sames
Next Topic:	Column in Table

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Apr 24 15:38:52 CDT 2024