SQL Help

[senchiru]

All Pls help me writing SQL for the following scenario

Table 1
COL1

Table 2
COL1
COL2 ----> FK to TAB1.COL1 (1 to many)
COL3
COL4
COL5
COL6

Table 3 Is a cross reference table (For a given TAB2.COL1 there may be many TAB4.COL1 values)
COL1 -------> Foreign key to TAB2.COL1
COL2 -------> Foreign Key to TAB4.COL1

Table 4
COL1
COL2
COL3
COL4
COL5
COL6

Now the requirement is for a given TAB1.COL1 I need to find out the count of TAB2.COL3 <> TAB4.COL3
TAB2.COL4 <> TAB4.COL4
TAB2.COL5 <> TAB4.COL5
TAB2.COL6 <> TAB4.COL6

In One query The output should be something like
COUNT(COL3) = 1
COUNT(COL4) = 1
COUNT(COL5) = 1
COUNT(COL6) = 1

[sakumar9]

One possibility:

Your four individual queries:
(select count(tab2.col3) a from tab2, tab4 where tab2.col3 <> tab4.col3)
(select count(tab2.col4) b from tab2, tab4 where tab2.col4 <> tab4.col4)
(select count(tab2.col5) c from tab2, tab4 where tab2.col5 <> tab4.col5)
(select count(tab2.col6) d from tab2, tab4 where tab2.col6 <> tab4.col6)


Combined query:

WITH
    a (count1) AS
        (select count(tab2.col3) a from tab2, tab4 where tab2.col3 <> tab4.col3
        ),
    b (count2) AS
        (select count(tab2.col4) b from tab2, tab4 where tab2.col4 <> tab4.col4
        ),
    c (count3) AS
        (select count(tab2.col5) c from tab2, tab4 where tab2.col5 <> tab4.col5
        ),
    d (count4) AS
        (select count(tab2.col6) d from tab2, tab4 where tab2.col6 <> tab4.col6
        )
 SELECT a.count1, b.count2, c.count3, d.count4 FROM a,b,c,d;

Results:
COUNT1      COUNT2      COUNT3      COUNT4
----------- ----------- ----------- -----------
          0           0           0           0

  1 record(s) selected.

Is this what you were looking for?

Regards
-- Sanjay

[senchiru]

Sanjay,

Thank you so much this what exactly I am looking for

Sen