Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2015

    Unanswered: Help in a Join query

    Hi All,

    Below is my query

    SELECT hh_id, ffile, created, deleted, flag=0 from processed_h A
    WHERE (((A.manual = 0) OR (A.deleted IS NOT NULL)) and (A.ffile <> 'deleted')) and cust not in (2001,2002,2003)

    produce 47 rows. I am just trying to get the value from processed_h where the customer id not havining (2001,2002,2003) . There is other table where
    we have the customer id. so instead of doing a hardcodind values in the query I tried to get the same by join as below

    SELECT A.hh_id, A.ffile, A.created, A.deleted, flag=0 from processed_h A,ref_customer B
    WHERE (((A.manual = 0) OR (A.deleted IS NOT NULL)) and (A.ffile <> 'deleted')) and B.customer_nid <> '0'
    and A.custom_type <> convert(int,B.customer_nid)

    this is returning 1447 rows which have duplicate row , why ? even if i use distinct the count is not getting as 47 . What mistake I am doing


  2. #2
    Join Date
    Jan 2004
    Provided Answers: 4
    You haven't joined table A and B so you are getting a cartesian product. Every single one of the 47 rows in table A that applies to the where clauses is joined with every row in table B that applies tho the where clause for table B.
    Does table A have a foreign key that references table B? You need to join the foreign key with the primary key of table B.

    Also, cust not in (2001,2002,2003) from you first query is not mentioned in your second query so you might be getting more than 47 records returned from it.
    I'm not crazy, I'm an aeroplane!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts