JOIN problems.. bashing my head

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)

JOIN problems.. bashing my head

Author

Topic

hooperp
Starting Member

1 Post

Posted - 2005-07-01 : 11:01:58

I'm bashing my head against a wall at the moment with a specific SQL Server problem. Can anyone help?

Basically I have two tables - tblUsers and tblOrders. The relevant fields are:

tblUsers.Email
tblUsers.DateJoined
tblUsers.ContactAllowed

tblOrders.Email
tblOrders.Date

Users register but don't all place orders.

I need to know two things. 1. How many users have agreed to be contacted and have not placed orders within a 6 week period.
2. How many users have agreed to be contacted and not placed orders within a 6 week period but have had orders outside of that time period.

I have tried the following but it comes up with the same figure which is unlikely.

SELECT COUNT(DISTINCT tblUsers.Email) AS Expr1
FROM tblUsers LEFT OUTER JOIN
tblOrders ON tblUsers.Email = tblOrders.Email
WHERE (tblUsers.MFIContact = 1) AND (NOT (tblOrders.Date BETWEEN CONVERT(DATETIME, '2004-12-28 00:00:00', 102) AND CONVERT(DATETIME, '2005-02-08 00:00:00', 102))) AND (tblUsers.DateJoined <= CONVERT(DATETIME, '2005-02-08 00:00:00', 102))

SELECT COUNT(DISTINCT tblUsers.Email) AS Expr1
FROM tblUsers LEFT OUTER JOIN
tblOrders ON tblUsers.Email = tblOrders.Email
WHERE (tblUsers.MFIContact = 1) AND (NOT (tblOrders.Date BETWEEN '20041228' AND '20050208')) AND
(tblOrders.Date < CONVERT(DATETIME, '2004-12-28 00:00:00', 102) OR
tblOrders.Date > CONVERT(DATETIME, '2005-02-08 00:00:00', 102)) AND (tblUsers.DateJoined <= CONVERT(DATETIME, '2005-02-08 00:00:00', 102))

Anyone any ideas?!?"

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2005-07-01 : 11:21:52

Have a look at the "not exists" construct.

Q1. should then look SOMETHING like....YOU MAY HAVE ISSUES WITH THE FINAL ")" BELOW, AS I'M NOT SYNTAX CHECKING IT.

SELECT COUNT(DISTINCT tblUsers.Email) AS Expr1
FROM tblUsers WHERE NOT EXISTS (SELECT * FROM tblOrders WHERE tblUsers.Email = tblOrders.Email
AND tblOrders.Date BETWEEN CONVERT(DATETIME, '2004-12-28 00:00:00', 102) AND CONVERT(DATETIME, '2005-02-08 00:00:00', 102)))
and (tblUsers.MFIContact = 1)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources