Inner 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

SQL Server 2000 Forums

Transact-SQL (2000)

Inner Join !!!

Author

Topic

nishithrn
Yak Posting Veteran

58 Posts

Posted - 2003-05-23 : 05:12:48

Hello,

I have a table which is something like this:

dbdate dbname dbsize data log
size size
04/03/2003 AEXCISE0203 18.5 12.94 5.56
11/03/2003 AEXCISE0203 18.5 12.94 5.56
18/03/2003 AEXCISE0203 19.81 14.25 5.56
25/03/2003 AEXCISE0203 19.81 14.25 5.56
01/04/2003 AEXCISE0203 19.81 14.25 5.56
08/04/2003 AEXCISE0203 19.81 14.25 5.56
15/04/2003 AEXCISE0203 19.81 14.25 5.56
22/04/2003 AEXCISE0203 19.81 14.25 5.56
29/04/2003 AEXCISE0203 19.81 14.25 5.56
04/05/2003 AEXCISE0203 19.81 14.25 5.56
11/05/2003 AEXCISE0203 19.81 14.25 5.56
18/05/2003 AEXCISE0203 19.81 14.25 5.56
29/04/2003 AEXCISE0304 27.63 12.94 14.69
04/05/2003 AEXCISE0304 28.94 14.25 14.69
11/05/2003 AEXCISE0304 28.94 14.25 14.69
18/05/2003 AEXCISE0304 28.94 14.25 14.69
29/04/2003 AURANGABADBACKUP 27.63 12.94 14.69
04/05/2003 AURANGABADBACKUP 27.63 12.94 14.69
11/05/2003 AURANGABADBACKUP 27.63 12.94 14.69
18/05/2003 AURANGABADBACKUP 27.63 12.94 14.69

I would like to create a query wherein I get an output like this:

dbdate AEXCISE0203 AEXCISE0304 AURANGABADBACKUP
04/03/2003 18.5
11/03/2003 18.5
18/03/2003 19.81
25/03/2003 19.81
01/04/2003 19.81
08/04/2003 19.81
15/04/2003 19.81
22/04/2003 19.81
29/04/2003 19.81 27.63 27.63
04/05/2003 19.81 28.94 27.63
11/05/2003 19.81 28.94 27.63
18/05/2003 19.81 28.94 27.63

I guess, INNER JOIN would help, but am still unsuccessful.
Any suggestions....

Thanks in Advance
Nishith

SamC
White Water Yakist

3467 Posts

Posted - 2003-05-23 : 09:03:53

I'm reminded of a quote originally from The Treasure of the Sierra Madre, with a twist: "Inner join? We don't need no inner join. I don't have to show you any stinkin inner join."

SELECT dbdate,

MAX(CASE WHEN dbname = 'AEXCISE0203' THEN dbsize ELSE 0 END) AS ASECISE0203,

MAX(CASE WHEN dbname = 'AEXCISE0304' THEN [data log size] ELSE 0 END AS AEXCISE0304,

MAX(CASE WHEN dbname = 'AURANGABADBACKUP' THEN size ELSE 0 END) AS AURANGBADBACKUP

FROM MyTable

GROUP BY dbdate

ORDER BY dbdate

I wasn't sure about the names of the last 3 columns in the source table. Hope it's right.

Sam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources