Left self join? - 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

General SQL Server Forums

New to SQL Server Programming

Left self join?

Author

Topic

sqlbug
Posting Yak Master

201 Posts

Posted - 2009-03-10 : 17:35:07

One of my queries returns no data because there are no records for 2009 yet. So - I was thinking about a self left join concept to get the plot names from previous year's records in the table - and other column values from the current year. But could not get it to work. Here's the existing query:
-----
select plot_name, collection_month, delayed_by, site_id from submit_info where collection_year = " + year
+ " and site_id = '" + site + "' order by plot_name
-----
Need help please.

guptam
Posting Yak Master

161 Posts

Posted - 2009-03-11 : 00:18:03

So you want to get information for the year passed? But if year passed records don't exist you still want the remaining records for other years to return based on the site id?

--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCITP: Database Administrator
MCTS: SQL Server 2005
http://sqllearnings.blogspot.com/

sqlbug
Posting Yak Master

201 Posts

Posted - 2009-03-11 : 11:17:44

No...I don't want information for the year passed.
Since there are no information for the current year yet, the query is not returning anything. But I want the plot names with other fields empty. The plot names can be found in previous year's records - so I was thinking about a left self join (names from previous year, other fields from 2009).
Thanks.

guptam
Posting Yak Master

161 Posts

Posted - 2009-03-11 : 11:36:00

Can you please provide the table def?

Mohit.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-11 : 13:20:47

i think what you need is


SELECT t.PlotNames,t1.columns...
FROM (SELECT DISTINCT PlotNames FROm YourTable)t
LEFT JOIN YourTable t1
ON t1.PlotNames=t.PlotName
AND t1.DateField>DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)

sqlbug
Posting Yak Master

201 Posts

Posted - 2009-03-11 : 18:29:22

Sorry, that didn't work Visakh...I tried something like that before.
Here's the table def, I am not sure how much it'll help - but I put it anyway.

SITE_ID NOT NULL CHAR(2)
PLOT_ID NOT NULL CHAR(3)
SUBMIT_DATE NOT NULL DATE
ON_TIME CHAR(1)
DELAYED_BY NUMBER(38)
COLLECTION_YEAR NOT NULL NUMBER(38)
COLLECTION_MONTH NOT NULL VARCHAR2(12)
COLLECTION_DATE NOT NULL DATE

Thanks for your efforts.

Me

guptam
Posting Yak Master

161 Posts

Posted - 2009-03-11 : 19:18:20

Maybe I am over simplifying this ..

SELECT Distinct SITE_ID, PLOT_ID, '' AS SUBMIT_DATE, NULL AS ON_TIME, 0 AS DELAYED_BY, Year(GetDate()) AS Collection_Year, Month(GetDate()) AS Collection_Month, GetDate() AS Collection_Date
FROM YourTable
UNION ALL
SELECT SITE_ID .....
FROM YourTable
WHERE YourTable.PLOT_ID = 123

???

--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCITP: Database Administrator
MCTS: SQL Server 2005
http://sqllearnings.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-12 : 12:38:53

quote:
Originally posted by sqlbug

Sorry, that didn't work Visakh...I tried something like that before.
Here's the table def, I am not sure how much it'll help - but I put it anyway.

SITE_ID NOT NULL CHAR(2)
PLOT_ID NOT NULL CHAR(3)
SUBMIT_DATE NOT NULL DATE
ON_TIME CHAR(1)
DELAYED_BY NUMBER(38)
COLLECTION_YEAR NOT NULL NUMBER(38)
COLLECTION_MONTH NOT NULL VARCHAR2(12)
COLLECTION_DATE NOT NULL DATE

Thanks for your efforts.

Me

As per your explanation it should work. My query just takes distinct of plot names from your tables existing data (previous years) and puts it for current year with NULL values for other fields which is what you're looking at i guess.

sqlbug
Posting Yak Master

201 Posts

Posted - 2009-03-13 : 01:26:20

Visakh - Well..I tested it and it still comes up with no rows.
There is a little "but" here.
The database is Oracle 8i. That had a little weird syntax for LEFT join. But should have worked.

Mohit - I'll try your idea next.

Thanks to both of you.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-13 : 12:48:20

well...in that case it may be better you try your luck on some oracle forum like www.dbforums.com This is MS SQL Server forum

sqlbug
Posting Yak Master

201 Posts

Posted - 2009-03-13 : 15:17:57

Yup, I know that. But I like this forum better and we don't use oracle much - so will keep using this.
Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources