Need help with a query (a bit urgent... :))

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

SQL Server Development (2000)

Need help with a query (a bit urgent... :))

Author

Topic

royc
Yak Posting Veteran

53 Posts

Posted - 2008-03-05 : 11:43:48

Hello,

Suppose I have these two tables:
In table A I have id and month fields.
In table B I have id, month and description fields.
The id fields in both tables are the same and can be used for joins.
Table A contains all the id's while table B may contain the ids.
I would like to get all the ids from table A and the description field attached to each id based on a given month.
Now, I tried this query:
SELECT...
FROM A LEFT JOIN B on A.id = B.id
WHERE A.month = 02.2008.

The problem is this:
Suppose that tables A and B contain these records:
A->id=123,month=02.2008
B->id=123,month=01.2008,description=hello

This query will bring back 'hello' as a description for id=123 for month=02.2008.

If I add a restriction WHERE A.month = 02.2008 AND B.month = 02.2008
then I won't get the record from table A in case the the record in table B does not exist.

If I change it to INNER JOIN I get the same problem as adding another restriction to the WHERE clause.

Any suggestions how to get this right?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-05 : 11:56:44

WHERE A.month = 02.2008 AND (B.month = 02.2008 OR B>Month IS NULL)

royc
Yak Posting Veteran

53 Posts

Posted - 2008-03-05 : 12:31:29

Thank you for the quick response.
When I run my 1st query:
SELECT...
FROM A LEFT JOIN B on A.id = B.id
WHERE A.month = 02.2008

on the real data tables I get 1818 records, which is the right number.
When I add your restriction:
WHERE A.month = 02.2008 AND (B.month = 02.2008 OR B.month IS NULL)
I get 1813 records, meaning I am missing 5 records comparing to the previous query.
I would expected to get the exact same number, what could be the reason for this?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-03-05 : 13:04:29

I suspect that the issue is putting the restriction in the WHERE clause. LEFT OUTER JOIN often cause issues for people in this regard. Here is some sample data and different queries:

DECLARE @A TABLE (ID INT, MonthYear CHAR(7))

INSERT @A
SELECT 1, '02.2008'
UNION ALL SELECT 2, '01.2008'
UNION ALL SELECT 3, '12.2007'
UNION ALL SELECT 4, '02.2008'
UNION ALL SELECT 5, '02.2008'

DECLARE @B TABLE (ID INT, MonthYear CHAR(7), Description VARCHAR(50))

INSERT @B
SELECT 1, '02.2008', 'hello'
UNION ALL SELECT 1, '01.2008', 'bye'
UNION ALL SELECT 2, '02.2008', 'foo'
UNION ALL SELECT 2, '01.2008', 'bar'
UNION ALL SELECT 3, '01.2008', 'love'
UNION ALL SELECT 3, '02.2008', 'hate'


--------------------------
-- I think this what you want??
--------------------------
SELECT
	A.ID,
	B.MonthYear,
	B.Description
FROM
	@A AS A
LEFT OUTER JOIN
	@B AS B
	ON A.ID = B.ID
	AND B.MonthYear = '02.2008' 
WHERE
	A.MonthYear = '02.2008'

--------------------------
SELECT
	A.ID,
	B.MonthYear,
	B.Description
FROM
	@A AS A
LEFT OUTER JOIN
	@B AS B
	ON A.ID = B.ID

--------------------------
SELECT
	A.ID,
	B.MonthYear,
	B.Description
FROM
	@A AS A
LEFT OUTER JOIN
	@B AS B
	ON A.ID = B.ID
	AND B.MonthYear = '02.2008' 

--------------------------
SELECT
	A.ID,
	B.MonthYear,
	B.Description
FROM
	@A AS A
LEFT OUTER JOIN
	@B AS B
	ON A.ID = B.ID
WHERE
	A.MonthYear = '02.2008'

--------------------------
SELECT
	A.ID,
	B.MonthYear,
	B.Description
FROM
	@A AS A
LEFT OUTER JOIN
	@B AS B
	ON A.ID = B.ID
	AND A.MonthYear = '02.2008'
	AND B.MonthYear = '02.2008' 

--------------------------
SELECT
	A.ID,
	B.MonthYear,
	B.Description
FROM
	@A AS A
LEFT OUTER JOIN
	@B AS B
	ON A.ID = B.ID
WHERE
	A.MonthYear = '02.2008'
	AND (B.MonthYear = '02.2008' OR B.MonthYear IS NULL)

royc
Yak Posting Veteran

53 Posts

Posted - 2008-03-05 : 13:09:47

If I'll run this:
SELECT
A.ID,
B.MonthYear,
B.Description
FROM
@A AS A
LEFT OUTER JOIN
@B AS B
ON A.ID = B.ID
AND B.MonthYear = '02.2008'
WHERE
A.MonthYear = '02.2008'

I won't get the data back in case table B doesn't YET contain records for 02.2008, so it's not the right one.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-03-05 : 13:33:55

quote:
Originally posted by royc

I won't get the data back in case table B doesn't YET contain records for 02.2008, so it's not the right one.

Ok, so given the data I provided, what do you want as output?

royc
Yak Posting Veteran

53 Posts

Posted - 2008-03-05 : 13:42:49

Gived the data you provided I want 4 and 5 from table A assuming it's '02.2008'.
If you also had in table B:
UNION ALL SELECT 4, '02.2008', 'foo2'
Then I would like to get 4's description as well.

BUT - if you also had in table B for example:
UNION ALL SELECT 4, '01.2008', 'foo2'
Then I still want 4 and 5 but with no description for 4 since it's for '01.2008' and not '02.2008'

royc
Yak Posting Veteran

53 Posts

Posted - 2008-03-05 : 14:00:44

Assuming we're querying for '02.2008' and given the data you provided then I want 1, 4 and 5 from table A and 'hello' for 1 from table B.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-03-05 : 14:06:43

Using the query you said "won't get the data back" I get the following results:


ID	MonthYear	Description
1	02.2008		hello
4	NULL		NULL
5	NULL		NULL

royc
Yak Posting Veteran

53 Posts

Posted - 2008-03-05 : 14:29:03

hmmm I'll test that.
So what's the difference between putting the restriction in the JOIN and in the WHERE?
Other than performance issues it should bring back the same results shouldn't it?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-03-05 : 14:50:10

Check out this link, it should help show the difference: http://weblogs.sqlteam.com/jeffs/archive/2007/05/14/60205.aspx

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-03-05 : 15:02:08

For grins, here is a standard sample that I give to co-works and use for interviews that deals with this issue:

DECLARE @Logon TABLE (LogonID int, Logon varchar(50))
DECLARE @Message TABLE (MessageID int, Message varchar(200), CreateDate datetime, LogonID int)

INSERT INTO @Logon
	SELECT 1, 'foo1' 
	UNION ALL SELECT 2, 'foo2' 
	UNION ALL SELECT 3, 'foo3' 
	UNION ALL SELECT 4, 'foo4' 

INSERT INTO @Message
	SELECT 1, 'Msg1', GETDATE() - 10, 1
	UNION ALL SELECT 2, 'Msg2', GETDATE() - 20, 2
	UNION ALL SELECT 3, 'Msg3', GETDATE() - 40, 3
	UNION ALL SELECT 4, 'Msg4', GETDATE() - 50, 1
	UNION ALL SELECT 5, 'Msg4', GETDATE() - 45, 4
	

-----------------------------------------------------------
-- Get all the Logons that have posted in the last 30 days.
-----------------------------------------------------------
-- Condition on the JOIN
SELECT 
	l.Logon
FROM
	@Logon l
INNER JOIN
	@Message m
	ON l.LogonID = m.LogonID
	AND m.CreateDate > GETDATE() - 30

-- Condition on the WHERE clause
SELECT 
	l.Logon
FROM
	@Logon l
INNER JOIN
	@Message m
	ON l.LogonID = m.LogonID
WHERE
	m.CreateDate > GETDATE() - 30

---------------------------------------------------------------
-- Get all the logons that haved NOT posted in the last 30 days.
---------------------------------------------------------------
-- Putting the condition on the WHERE clause does NOT work
SELECT 
	l.Logon
FROM
	@Logon l
LEFT OUTER JOIN
	@Message m
	ON l.LogonID = m.LogonID
WHERE
	m.CreateDate > GETDATE() - 30
	AND MessageID IS NULL


-- Putting the condition onthe join, DOES work
SELECT 
	l.Logon
FROM
	@Logon l
LEFT OUTER JOIN
	@Message m
	ON l.LogonID = m.LogonID
	AND m.CreateDate > GETDATE() - 30
WHERE
	MessageID IS NULL

royc
Yak Posting Veteran

53 Posts

Posted - 2008-03-05 : 16:07:36

Thank you very much Lamprey for the solution and detailed explanation!

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-03-05 : 16:30:41

Glad to help.

royc
Yak Posting Veteran

53 Posts

Posted - 2008-03-18 : 13:12:57

Hi again guys,

I got new requirement: I need to to produce the same query as above only for the entire year, so the condition in the LEFT JOIN has been changed from: AND B.calMonth = 02.2008 to: AND B.calMonth LIKE '%2008'.

The problem is that I'm getting duplicates.
The duplication appears when I got more than one record in the joined table for 02.2008 and 03.2008 for example. How can I prevent this?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources