RE: SQL PROBLEM

[Mohan, Karthik (GEP)]

Title: MS access

 I do not seem to have exactly understood u r problem..........but try this anyway

 

select  count(*)
from    a
where   l_id in (
select b.bl_id
from a,b
where a.o_id = 17
and a.o_id = b.bo_id )
OR
(a.o_id = 17)

 

  COUNT(*)
----------
         2

 

1 row selected.

 

The table data is as listed below ....

 

SQL> select * from a;

 

      O_ID       L_ID
  ----------        ----------
        17            NULL
  NULL                 42

 

2 rows selected.

 

SQL> select * from b;

 

     BO_ID      BL_ID
----------          ----------
        17         42
        17         43
        17         44

 

3 rows selected.

 

 

 

Regards,

Karthik Mohan

TCS Consultant at GEP (HK)

DialComm 3310962

ISD : 852-26290962

 

-----Original Message-----
From: Niyi Olajide [mailto:[EMAIL PROTECTED]]
Sent: Friday, May 25, 2001 4:36 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: SQL PROBLEM

Can you try this:

 

select count (1) from (

select 1 from table_ a where ord_id = 17 

union

select 1 from table_b where ord_id = 17)

/

 

 

 Hi,

 

Favour me in suggest a hint in writing a sql for following scenaio:

 

Table A has 2 columns ord_id and line_id

 

example rows are:

 

ord_id         line_id

17                null

null              42

 

Table  B has columns ord_id and line_id

 

ord_id         line_id

17                42

17                43

17                44

 

I have to write a sql to count(*) from table A where ord_id=17 ..

as u see from table B line 42 is row of ord_id=17 ..so i want

count(*) should show =2 from tabel A

but if i do count(*) from table A where ord_id=17..it shows 1..

i need to put another condition which will check line id with

TABLE B and if it is  for this particular order ...add that also in

count(*).............

I know scenario is little fuzzy sorry for it..but if u got my point

kindly respond.

 

Thanks

Harvinder