March 31, 2004 at 1:53 pm
I got two tables T1 AND T2
T1 Structure
ID
DESC
DATE
T2 Structure
ID
DESC
When i execute the following query
SELECT * FROM T1 WHERE ID <> '' AND (DATE BETWEEN DATE1 AND DATE2) getting 24 rows
For following
SELECT A.ID,B.ID,B.DESC,A.DESC,A.DATE FROM T1 A,T2 B WHERE
(A.ID <> '')
(A.DATE BETWEEN DATE1 AND DATE2) AND
A.ID = B.ID
Getting 20
For following
SELECT A.ID,B.ID,B.DESC,A.DESC,A.DATE FROM T1 A,T2 B WHERE
(A.ID <> '')
(A.DATE BETWEEN DATE1 AND DATE2) AND
A.ID *= B.ID
Getting 24 but B.ID AND B.DESC are NULL VALUES
A.ID's are existing in T2.
Would you please help me where i am doing wrong.
Thanks in advance.
March 31, 2004 at 1:57 pm
Run
SELECT a.id
FROM TA a
INNER JOIN TB b
ON a.id = b.id
WHERE b.id IS NULL
If it returns any id, then those ones are missing in TB.
March 31, 2004 at 3:00 pm
I did find the problem but don't the solution. The problem is ID in both tables is 20 CHARS but we store 9 or 10 CHARS.
In both tables ID's are looking same but in T1 ID there is some thing after 9 or 10 CHARS. I tried with RTRIM got the same result.
Any ideas please.
April 1, 2004 at 2:45 pm
Something like below:
From T1 (nolock)
JOIN T2 (nolock) on cast(T1.ID as char(10)) = cast(T2.ID as char(10))
I can't really help you more without the actual data types and some sample data.
Signature is NULL
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply