Help with INNER JOIN - 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 2000 Forums

Transact-SQL (2000)

Help with INNER JOIN

Author

Topic

SpeshulK926
Starting Member

16 Posts

Posted - 2006-12-13 : 09:56:03

I have my current statement that is working OK, but it's not pulling data if there's nothing in AgencyBankInformation. I need to pull the data even if no a.AgencyID exists in AgencyBankInformation. If no data exists in AgencyBankInformation, then the fields that need it can be hard-coded...Status='N', CodeDescription='CheckByMail', Prenote='', StatusDate='' if there is no BankInfo. Any suggestions would be much appreciated! :)

SELECT b.AgencyTaxID, b.Name, a.AgencyID, a.EmailAddress, a.CreateDate, c.Status, d.CodeDescription,
       c.PrenoteDate, c.StatusDate, b.AgencyNumber
 FROM dbo.AgencyLogin A
  INNER JOIN dbo.Agency B
   ON A.AgencyID = B.Id
  INNER JOIN dbo.AgencyBankInformation C
   ON A.AgencyID = C.AgencyID
  INNER JOIN dbo.StatusCodes D
   ON C.Status = D.Code
  WHERE a.CreateDate BETWEEN CONVERT(Char(10),@BeginDate ,110) + ' 00:00:00'
    AND CONVERT(Char(10),@EndDate ,110) + ' 23:59:59'
  ORDER BY a.CreateDate

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-13 : 10:00:46

Start with this. I have also made the query faster to run due to other syntax for date comparison

SELECT		b.AgencyTaxID,
		b.Name,
		a.AgencyID,
		a.EmailAddress,
		a.CreateDate,
		ISNULL(c.Status, 'N') AS Status,
		ISNULL(d.CodeDescription, 'CheckByMail') AS CodeDescription,
		c.PrenoteDate,
		c.StatusDate,
		b.AgencyNumber
FROM		dbo.AgencyLogin AS a
INNER JOIN	dbo.Agency AS b ON b.ID = a.AgencyID
LEFT JOIN	dbo.AgencyBankInformation AS c ON c.AgencyID = a.AgencyID
LEFT JOIN	dbo.StatusCodes AS d ON d.Code = c.Status
WHERE		a.CreateDate >= DATEADD(day, DATEDIFF(day, 0, @BeginDate), 0)
		a.CreateDate < DATEADD(day, DATEDIFF(day, 0, @EndDate), 1)
ORDER BY	a.CreateDate

Peter Larsson
Helsingborg, Sweden

SpeshulK926
Starting Member

16 Posts

Posted - 2006-12-13 : 10:13:35

That worked really well... Is there any way that for the ones where the Status and Code Descriptions are Null that the Status='N' and CodeDescription='CheckByMail'? I just can't figure out how to do this (never needed to before now). Thanks for your help! That did make my query much faster too, I'm gonna go change my other SP to pull dates that way :)

OK, so I understand that I'm supposed to use the ISNULL(), so don't worry about it! Thanks for your help!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-13 : 10:48:51

You can also use COALESCE function, if there is a need for checking more values than 1.
ISNULL(<some value here>, <default value here>)
COALESCE(<some value here>, <another value here>, <third value here>, <default value here>)

Peter Larsson
Helsingborg, Sweden

SpeshulK926
Starting Member

16 Posts

Posted - 2006-12-13 : 10:54:09

quote:
Originally posted by Peso

You can also use COALESCE function, if there is a need for checking more values than 1.
ISNULL(<some value here>, <default value here>)
COALESCE(<some value here>, <another value here>, <third value here>, <default value here>)

Peter Larsson
Helsingborg, Sweden

I did not know about the COALESCE. That sounds really great, thanks for your help! :)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-13 : 10:56:05

COALESCE continues from left to right over the parameters until a non-null value is found.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources