Breakdown of SELECT statement.

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)

Breakdown of SELECT statement.

Author

Topic

gwhaley262
Starting Member

2 Posts

Posted - 2012-06-13 : 13:28:46

I'm a BES admin but SQL newbie. I've found the following query to pull data out of a BES database:

select
a.DisplayName,
a.MailboxSMTPAddr,
d.GroupName,
c.ModelName,
c.PhoneNumber,
c.PIN,
c.HomeNetwork,
convert(varchar(10),a.CreationTime, 101)CreationDate,
convert(varchar(10),a.ActivationTime, 101)ActivationDate,
b.MsgsForwarded,
b.MsgsSent,
convert(varchar(10),b.LastFwdTime, 101)LastFwdDate,
convert(varchar(10),b.LastSentTime, 101)LastSentDate
from (userconfig a left join groupconfig d on a.GroupConfigId = d.Id), userstats b, vHandheldSummaryInfo c
where a.id = b.userconfigid and a.displayname = c.displayname
order by a.displayname

I'd like to edit this query to pull data from other locations. I've figured out the query down to the line:

from (userconfig a left join groupconfig d on a.GroupConfigId = d.Id), userstats b, vHandheldSummaryInfo c

I'm trying to figure out this "FROM" statement. Could the gurus assist me here?

any help is greatly appreciated!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-06-13 : 15:14:35

It is using a combination of old style joins for the inner joins and newer style joins for the left join. You can convert all of those to newer style joins like shown below. There should be no functional difference, it just is more readable (to me).

SELECT
	a.DisplayName,
	a.MailboxSMTPAddr,
	d.GroupName,
	c.ModelName,
	c.PhoneNumber,
	c.PIN,
	c.HomeNetwork,
	CONVERT(VARCHAR(10), a.CreationTime, 101)CreationDate,
	CONVERT(VARCHAR(10), a.ActivationTime, 101)ActivationDate,
	b.MsgsForwarded,
	b.MsgsSent,
	CONVERT(VARCHAR(10), b.LastFwdTime, 101)LastFwdDate,
	CONVERT(VARCHAR(10), b.LastSentTime, 101)LastSentDate
FROM
	(
	    userconfig a LEFT JOIN groupconfig d ON a.GroupConfigId = d.Id
	)
	INNER JOIN userstats b ON a.id = b.userconfigid
	INNER JOIN vHandheldSummaryInfo c ON a.displayname = c.displayname
ORDER BY
	a.displayname

If you want to know about the INNER JOIN vs LEFT JOIN, take a look at the w3schools web pages:

http://www.w3schools.com/sql/sql_join_inner.asp
http://www.w3schools.com/sql/sql_join_left.asp

gwhaley262
Starting Member

2 Posts

Posted - 2012-06-15 : 12:04:29

Thanks for your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources