Help in Select Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help in Select Query

Author

Topic

cyberpd
Yak Posting Veteran

60 Posts

Posted - 2010-10-05 : 05:19:08

Hi
I have two tables brmaster, pinmaster with common attribute brcode.
I am executing the following query : -


SELECT p.pin, p.pinvalue, b.brname, p.gdate, p.guser
FROM Pinmaster AS p, Brmaster AS b
WHERE b.brcode = p.brcode and 
p.brcode = 1 AND 
p.gdate >= '20100101' AND 
p.gdate <= '20100724'

33 rows are returned correctly, with the given date-range and brcode = 1 respectively.
Now, I want to join another table, Associates.
pinmaster and associates have common attribute, "acode".
My actual requirement is the pinmaster has column named "acode"; the query will match the acode with acode of associates table
and fetch corresponding aid; if pinmaster acode is null then the row will be returned with aid = 'N/A'.
That is, ultimately 33 rows will be returned but now aid will also be part of the result set.

I am executing the following:-


SELECT p.pin, p.pinvalue, b.brname, p.gdate, p.guser, a.aid
FROM Pinmaster AS p, Brmaster AS b, Associates AS a
WHERE b.brcode = p.brcode and 
p.brcode = 1 AND 
p.gdate >= '20100101' AND 
p.gdate <= '20100724'

and this returns 1287 tuples which is wrong. I think I can not filter the null values of pinmaster table properly.
Any help will be appreciated.
thank you

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-10-05 : 05:27:25

Try this:

SELECT p.pin, p.pinvalue, b.brname, p.gdate, p.guser, a.aid
FROM Pinmaster AS p, Brmaster AS b, Associates AS a
WHERE b.brcode = p.brcode and
p.brcode = 1 AND
p.gdate >= '20100101' AND
p.gdate <= '20100724'
and isnull(p.acode,'N/A') = a.acode

cyberpd
Yak Posting Veteran

60 Posts

Posted - 2010-10-05 : 05:43:18

Thanks for responding sir.
I should have mentioned that datatype of acode of pinmaster table is nvarchar(100)

when i run your query the following is returned: -

quote:
Conversion failed when converting the nvarchar value 'n/a' to data type int.

Thank you

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources