difference between inner join and intersection

Data which I have

1001 2003 F

1001 2001 P

1002 2004 F

1003 2004 F

1004 2004 S

1004 2003 F

Which is considering all the cases

1004 -- 2003 1001

Result must be

1001 2003 2001 P

1002 2004 NULL F

1003 2004 NULL F

1004 2003 2004 S

But I am getting with this query

1001 2001 2003 P

1001 2003 2001 F

1002 2004 NULL F

1003 2004 NULL F

1004 2003 2004 F

1004 2004 2003 S

select a.acct_num, a.hh_id,b.hh_id, a.hh_acct_typ_cd from household_account a, household_account b where

(a.acct_num = b.acct_num and a.hh_id <> b.hh_id and a.hh_acct_typ_cd = 'P' )

union

select a.acct_num, a.hh_id

,(select b.hh_id from household_account b where b.acct_num = a.acct_num and a.hh_id<>b.hh_id ) AS STMT_HH_ID

, a.hh_acct_typ_cd from household_account a, household_account b

where (a.hh_id = b.hh_id and a.acct_num <> b.acct_num )

If the last case is not included then query is giving desired results

I split this from the bottom of another thread that was already answered. This seems like a straight T-SQL question, so I'm moving to the T-SQL forum.

Mike

|||

I am having a hard time following your queries. Can you build a table, insert the data that we can run your queries against?

Show us what you are getting, and what you really want