All,


A very happy New Year to you all!!!



I have two tables f and sm

structure for f:

uid int not null,
cbuid int not null,
pid int not null,
gid int not null,
sid int not null,
mnth tinyint not null


structure for sm:

sid int not null,
mnth tinyint not null


contents/rows in table f are:

uid cbid pid gid sid mnth
--- ------ ------ ------ ----- ----
8 92 10057 4 40 2
8 92 10057 4 40 3
8 92 10057 4 40 4
18 125 10057 4 40 2


contents/rows in table sm are:

sid mnth
--- ----
40 2
40 3
40 4
40 5


The requirement is compare (f, sm) and return matching and non-matching
rows.

now the sql:

1)

select f.uid, f.cbid, f.pid, f.gid, f.sid, f.mnth, sm.mnth
from f left join sm on f.sid = sm.sid
where f.Pid = 10057 AND
f.gid = 4 AND f.cbid = '125'


output:

uid cbid pid gid sid
mnth mnth
----------- ---------------------- ----------- ----------- -----------
----- -----
18 125 10057 4 40
2 5 -> Row retrieved
18 125 10057 4 40
2 4
18 125 10057 4 40
2 3
18 125 10057 4 40
2 2



The above output returns as expected until I change the predicate.....
See below:



select f.uid, f.cbid, f.pid, f.gid, f.sid, f.mnth, sm.mnth
from f left join sm on f.sid = sm.sid
where f.Pid = 10057 AND
f.gid = 4 AND f.cbid = '92'


output:

uid cbid pid gid sid
mnth mnth
----------- ---------------------- ----------- ----------- -----------
----- -----
8 92 10057 4 40
2 5
8 92 10057 4 40
3 5
8 92 10057 4 40
4 5
8 92 10057 4 40
2 4
8 92 10057 4 40
3 4
8 92 10057 4 40
4 4
8 92 10057 4 40
2 3
8 92 10057 4 40
3 3
8 92 10057 4 40
4 3
8 92 10057 4 40
2 2
8 92 10057 4 40
3 2
8 92 10057 4 40
4 2



The above output seems to be cartesian ?

Please help on how to resolve ...


2)

Is there a way where I could have non-matching rows like MINUS in
Oracle... I even tried NOT EXISTS but that did not work...


Any thoughts would be highly appreciated....
Thanks a bunch in advance,
Anu