Showing Null Values - 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

General SQL Server Forums

New to SQL Server Programming

Showing Null Values

Author

Topic

borg_jos
Starting Member

1 Post

Posted - 2010-10-03 : 10:53:04

I have 2 tables:

Table_A has 3 records:

REG_NUMBER CAR_MAKE
ABC123 CAR1
DEF456 CAR2
XYZ999 CAR3

Table_B has 2 records:

REB_NUMBER DISTANCE_TRAVELLED
DEF456 5
XYZ999 20

I need a query to display for each record in Table A how much distance has travelled. However if a record has travelled no distance (ie no record is created in Table_B) I require that the query does not ommit such record however it displays a 0.

Example:

ABC123 CAR1 0
DEF456 CAR2 5
XYZ999 CAR3 20

With the below query as you can see it omits ABC123:

SELECT A1.*,A2.DISTANCE_TRAVELLED FROM TABLE_A A1, TABLE_B A2
WHERE A1.REG_NUMBER = A2.REG_NUMBER

Can someone please help?

singularity
Posting Yak Master

153 Posts

Posted - 2010-10-03 : 12:26:58

SELECT A1.*, IsNull(A2.DISTANCE_TRAVELLED,0)
FROM TABLE_A A1
LEFT JOIN TABLE_B A2 ON A1.REG_NUMBER = A2.REG_NUMBER

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-10-04 : 09:35:30

can there be multiple records for same REG_NUMBER in table_B?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources