Thread: Help in a Join query
01-12-15, 05:22 #1Registered User
- Join Date
- Jan 2015
Unanswered: Help in a Join query
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
01-12-15, 12:18 #2Registered User
Provided Answers: 4
- Join Date
- Jan 2004
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!