Help with SubQuery - 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

SQL Server Development (2000)

Help with SubQuery

Author

Topic

jtrapat1
Starting Member

43 Posts

Posted - 2002-12-05 : 14:06:26

I have a table with these fields and I'd like to write them out to a report:
-------------------------------
adds_id
comments1
comments2
------------------------------
There is a budget_yr field in the table too and we'd like to write out the comments for the current year AND the previous year.
So, I'd like to include these two values in my report:
---------------------
lastyrcomments1
lastyrcomments2
------------------------------------
Can someone help me write this query - I thought I could write it with one subselect statement:
I tried this statement and hard-coded in the current and previous year:
-----------------------------------
SELECT bp12.adds_id, bp12.comment1,
(SELECT bp12.comment1
FROM nysa.bp12t_approp_adds
WHERE bp12.budget_yr = 2001)
FROM nysa.bp12t_approp_adds bp12
WHERE bp12.budget_yr = 2002;
--------------------------------------
But, when I ran the query, I got no values returned for the inner comment1 and there is data in the table for that year.
----------------------------
Thanks in Advance
John

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-12-05 : 14:32:08

SELECT *
FROM TABLE A LEFT JOIN TABLE B ON A.adds_id = B.adds_id AND A.year = B.year - 1
WHERE A.year = 2002

Edited by - ValterBorges on 12/05/2002 14:34:30

jtrapat1
Starting Member

43 Posts

Posted - 2002-12-09 : 09:47:55

ValterBorges,

Thanks for the response.
I think you're writing this correctly but I must be coding it wrong.
I hard-coded the years in for this statement and I'm getting blanks for my second comments field and I know that there are values in this field.
Can you check out my statement and see if you notice anything wrong?
I think I understand what you are doing and it seems like it should work - getting values for both this year and the previous year.
Let me know if this is correct:
You're linking the table to itself on the key field and giving it an alias of A and B.
-----------------------------------------------------
SELECT A.adds_id, A.comment1, A.comment2, B.comment1, B.comment2
FROM FROM nysa.bp12t_approp_adds A
LEFT JOIN nysa.bp12t_approp_adds B
ON A.adds_id = B.adds_id
AND A.year = 2002
WHERE A.year = 2001
----------------------------------------------------
Thanks in Advance
John

nr
SQLTeam MVY

12543 Posts

Posted - 2002-12-09 : 10:21:35

You want 2002 and comments from 2001 if it exists?

SELECT A.adds_id, A.comment1, A.comment2, B.comment1, B.comment2
FROM FROM nysa.bp12t_approp_adds A
LEFT JOIN nysa.bp12t_approp_adds B
ON B.adds_id = B.adds_id
AND B.year = 2001
WHERE A.year = 2002

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-12-09 : 10:32:00

Oopps, Got the aliases reversed.

DECLARE @Year Integer
SET @Year = 2002

SELECT A.adds_id, A.comment1, A.comment2, B.comment1, B.comment2
FROM FROM nysa.bp12t_approp_adds A
LEFT JOIN nysa.bp12t_approp_adds B
ON A.adds_id = B.adds_id
AND B.year = A.year - 1
WHERE A.year = @Year

It think this thread is cursed nr joined on the wrong alias.
Edited by - ValterBorges on 12/09/2002 10:33:57

Edited by - ValterBorges on 12/09/2002 10:34:28

jtrapat1
Starting Member

43 Posts

Posted - 2002-12-09 : 15:09:02

ValterBorges,
Thanks again for all the help.
However, I still think I have something wrong in my query.

I'm getting duplicates on my linking field, adds_id.
Ideally, I would like to get one adds_id and then list the 2002 comments and then the 2001 comments for each record.

I think the problem may be with the join.

I even tried to select DISTINCT and then an INNER JOIN and I'm still getting duplicate adds_ids.
If you have any ideas, let me know.
If I'm not giving enough info, let me know that too.

Thanks in Advance.
John

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-12-09 : 16:30:17

You would get duplicates if you have more than 1 record per year.
Can you post some sample data and expected output.

Edited by - ValterBorges on 12/09/2002 16:32:05

jtrapat1
Starting Member

43 Posts

Posted - 2002-12-09 : 17:02:47

Thanks,
Here's the select statement that I ran:
---------------------------------------------------
"select distinct A.adds_id, A.comment1, B.comment1 from nysa.bp12t_approp_adds A left join nysa.bp12t_approp_adds B on A.adds_id = B.adds_id and B.budget_yr = 2001 where A.budget_yr = 2002 order by A.adds_id"
-----------------------------------------------------
And, here is my output based on an adds_id, and the following are comments from 2002 and the previous year:
In the first record, the comment2 column is blank but the second time that adds_id shows up, it's filled with the comment from last year.
----------------------------------------------------
ADDS_ID COMMENT1 COMMENT2
10378 WAIT FOR GUNTHER
10378 WAIT FOR GUNTHER GUNTHER PRIORITY; 99-00 FUNDS USED FOR SULLIVAN CTY PLANNING DEPT&CATSKILLS IDEA
------------------------------------------------------
So, I would like to get that second value of the adds_id which includes the adds_id and both comments from this year and the previous year.
So, I'd really like that adds_id to show up only once, if that's possible.

Thanks
John

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-12-09 : 17:12:34

How do you tell which one is last do you have a datetimestamp field or another id

Edited by - ValterBorges on 12/09/2002 17:12:51

jtrapat1
Starting Member

43 Posts

Posted - 2002-12-10 : 09:06:45

ValterBorges,

No, there is no timestamp field;
I just figured that the second record that was returned was the correct one.

Maybe you did give me the correct query but I'm not running it correctly.

Thanks
John

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-12-10 : 09:28:17

The query will return both records because both records match the criteria. In order for you to get only the second you have to be able to specify how the query can disguish between the two.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources