T-SQL programming

Question

I need to write a query to pull out the records as below.

membership_id,
person_id,
first_name
last_name

who joined or called yesterday and the members who didn't join will have only person_id

But the below query is pulling out all the records from the table.

SELECT 
  dbo.pn_membership.membership_id, 
  dbo.pn.person_id,
  dbo.pn.first_name, 
  dbo.pn.surname, 
  dbo.pn.create_datetime 
 FROM 
  dbo.pn
  LEFT OUTER JOIN
  dbo.pn_membership 
  ON dbo.pn.person_id=dbo.pn_membership.person_id AND
  dbo.pn.create_datetime >=getdate()-1

I need the records only for the day before the run date.

Sparky · Answer 1 · 2014-01-10T00:30:24.003

Try this:

SELECT 
  dbo.pn_membership.membership_id, 
  dbo.pn.person_id,
  dbo.pn.first_name, 
  dbo.pn.surname, 
  dbo.pn.create_datetime 
 FROM 
  dbo.pn
  LEFT OUTER JOIN
  dbo.pn_membership 
  ON dbo.pn.person_id=dbo.pn_membership.person_id 
WHERE dbo.pn.create_datetime >=getdate()-1

Your query says...

Give me some fields from the pn table.
Also, if the person has matching membership record, give me that information
if they don't give me the fields from the membership table with NULL values

By moving the date test condition to the WHERE clause, you are reducing the rows from the pn table. By applying date as part of the JOIN, you are only increasing the likelihood of getting more NULL value columns from the membership table..

score 0 · Answer 2 · answered Jan 10 '14 at 00:25

are you saying that it pulls records with a date < getdate()-1 or that it pulls records where person_id is Null ? if the latter try this

SELECT dbo.pn_membership.membership_id, dbo.pn.person_id, dbo.pn.first_name, 
dbo.pn.surname, dbo.pn.create_datetime FROM dbo.pn LEFT OUTER JOIN dbo.pn_membership ON 
dbo.pn.person_id=dbo.pn_membership.person_id AND dbo.pn.create_datetime >=getdate()-1 
and dbo.pn.person_id is not NULL

peter.petrov · Accepted Answer · 2014-01-10T00:36:05.123

0

Try this version.

Your write in your question "who joined or called yesterday"
but your query does "who joined or called in the last 24 hours"
which is kind of different. Also, as Sparky noted you had this lack
of WHERE clause problem. My version does "who joined or called yesterday".

SELECT 
dbo.pn_membership.membership_id, 
dbo.pn.person_id,
dbo.pn.first_name, 
dbo.pn.surname, 
dbo.pn.create_datetime 
FROM 
dbo.pn
LEFT OUTER JOIN
dbo.pn_membership 
ON dbo.pn.person_id=dbo.pn_membership.person_id
WHERE
dbo.pn.create_datetime >= DATEADD(Day, DATEDIFF(Day, 0, getdate()), -1)
AND 
dbo.pn.create_datetime < DATEADD(Day, DATEDIFF(Day, 0, getdate()), 0)

edited Jan 10 '14 at 00:36

answered Jan 10 '14 at 00:25

peter.petrov

38,363
16
94
159

I think his problem is the date logic being on the LEFT JOIN statement, rather than a WHERE clause – Sparky Jan 10 '14 at 00:28
1

@Sparky Correct, thanks. I always tend to forget this. – peter.petrov Jan 10 '14 at 00:29

score 0 · Answer 4 · edited May 23 '17 at 12:03

0

It looks like the problem is at the very end of your query. Instead of AND dbo.pn.create_datetime >=getdate()-1, try WHERE dbo.pn.create_datetime >=getdate()-1. Including your filter criteria as part of the OUTER JOIN statement isn't the same thing as using a WHERE clause. See SQL Standard Regarding Left Outer Join and Where Conditions also.

edited May 23 '17 at 12:03

Community

1
1

answered Jan 10 '14 at 00:32

algadban

16
2

T-SQL programming

4 Answers4