Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Join making conflict.... Expand / Collapse
Author
Message
Posted Monday, August 27, 2012 3:20 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, June 25, 2014 6:20 AM
Points: 159, Visits: 622

SELECT @MSGOUT = @MSGOUT + '
<tr>
<td>' + ss.ServerName +'</td>
<td>' + ss.DBName +'</td>
<td>' + Occurrences +'</td>

</tr>
'

FROM DBINVENTORY ss
INNER JOIN
(SELECT ServerName,DBname,
cast(COUNT(dbname) as varchar(5))AS Occurrences
FROM DBINVENTORY d
GROUP BY ServerName,DBName
HAVING ( COUNT(DBName) > 1 )
)sq ON ss.DBName=sq.DBName

this giving me output like

----------------------------------------

Server_Name Dbname Occurences
X ABC 2
X ABC 2
Y HIJ 2
Y HIJ 2
Y NOP 2
Y NOP 2
Z BCD 4
Z BCD 4
Z BCD 4
Z BCD 4
Z JIK 4
Z JIK 4
Z JIK 4
Z JIK 4
Z JIK2 4
Z JIK2 4
Z JIK2 4
Z JIK2 4
A ABC 2
A ABC 2
A TUV 2
A TUV 2
B EFG 2
B EFG 2
D ACD 2
D ACD 2
---------------------------------------

Actual output required is


Server_Name Dbname Occurences
X ABC 2
Y HIJ 2
Y NOP 2
Z BCD 4
Z BCD 4
Z JIK 4
Z JIK 4
Z JIK2 4
Z JIK2 4
A ABC 2
A TUV 2
B EFG 2
D ACD 2


Please advise the join condition.....


Sagar Sonawane
** Every DBA has his day!!
Post #1350277
Posted Monday, August 27, 2012 3:56 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:55 PM
Points: 13,357, Visits: 10,222
Maybe you can give some sample data for table DBINVENTORY.
Why is it OK for some servername-dbname pairs to have 2 rows as output and for others isn't?




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1350292
Posted Monday, August 27, 2012 4:24 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, June 25, 2014 6:20 AM
Points: 159, Visits: 622


Main objective is to find duplicate db's...... and out put is perfect..... which I am looking for....

Can you please help wi


Sagar Sonawane
** Every DBA has his day!!
Post #1350302
Posted Monday, August 27, 2012 4:41 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 1:59 AM
Points: 562, Visits: 885
Saga... (8/27/2012)


Main objective is to find duplicate db's...... and out put is perfect..... which I am looking for....

Can you please help wi


Sorry but it is difficult without the sample data and the actual requirement.Here none of these are not clear


--rhythmk
------------------------------------------------------------------
To post your question use below link

http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1350307
Posted Monday, August 27, 2012 5:30 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:55 PM
Points: 13,357, Visits: 10,222
Saga... (8/27/2012)


Main objective is to find duplicate db's...... and out put is perfect..... which I am looking for....

Can you please help wi


I asked two questions. You didn't answer any of them.
How much effort do you think I will put in your questions?




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1350324
Posted Monday, August 27, 2012 5:33 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, June 25, 2014 6:20 AM
Points: 159, Visits: 622
SELECT @MSGOUT = @MSGOUT + '
<tr>
<td>' + ss.ServerName +'</td>
<td>' + ss.DBName +'</td>
<td>' + Occurrences +'</td>

</tr>
'

FROM /*DBINVENTORY ss --after removing this commented words...I got required output
INNER JOIN*/

(SELECT ServerName,DBname,
cast(COUNT(dbname) as varchar(5))AS Occurrences
FROM DBINVENTORY d
GROUP BY ServerName,DBName
HAVING ( COUNT(DBName) > 1 )
)sq ON ss.DBName=sq.DBName


Sagar Sonawane
** Every DBA has his day!!
Post #1350327
Posted Monday, August 27, 2012 7:39 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:28 PM
Points: 13,138, Visits: 11,977
Saga... (8/27/2012)
SELECT @MSGOUT = @MSGOUT + '
<tr>
<td>' + ss.ServerName +'</td>
<td>' + ss.DBName +'</td>
<td>' + Occurrences +'</td>

</tr>
'

FROM /*DBINVENTORY ss --after removing this commented words...I got required output
INNER JOIN*/

(SELECT ServerName,DBname,
cast(COUNT(dbname) as varchar(5))AS Occurrences
FROM DBINVENTORY d
GROUP BY ServerName,DBName
HAVING ( COUNT(DBName) > 1 )
)sq ON ss.DBName=sq.DBName


So does this mean you solved your issue or not?


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1350385
Posted Monday, August 27, 2012 7:55 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 3:37 PM
Points: 3,374, Visits: 7,300
You were joining your grouped results with the original table with duplicates. You can get an even simpler version of your code like this, no need of a subquery:

SELECT @MSGOUT = @MSGOUT + '
<tr>
<td>' + ServerName +'</td>
<td>' + DBName +'</td>
<td>' + cast(COUNT(dbname) as varchar(5)) +'</td>

</tr>
'
FROM DBINVENTORY
GROUP BY ServerName,DBName
HAVING ( COUNT(DBName) > 1 )




Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1350396
Posted Thursday, August 30, 2012 4:41 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, June 25, 2014 6:20 AM
Points: 159, Visits: 622
Luis Cazares (8/27/2012)
You were joining your grouped results with the original table with duplicates. You can get an even simpler version of your code like this, no need of a subquery:

SELECT @MSGOUT = @MSGOUT + '
<tr>
<td>' + ServerName +'</td>
<td>' + DBName +'</td>
<td>' + cast(COUNT(dbname) as varchar(5)) +'</td>

</tr>
'
FROM DBINVENTORY
GROUP BY ServerName,DBName
HAVING ( COUNT(DBName) > 1 )




Thanks luis....


Sagar Sonawane
** Every DBA has his day!!
Post #1352094
Posted Thursday, August 30, 2012 4:43 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, June 25, 2014 6:20 AM
Points: 159, Visits: 622
Sean Lange (8/27/2012)
Saga... (8/27/2012)
SELECT @MSGOUT = @MSGOUT + '
<tr>
<td>' + ss.ServerName +'</td>
<td>' + ss.DBName +'</td>
<td>' + Occurrences +'</td>

</tr>
'

FROM /*DBINVENTORY ss --after removing this commented words...I got required output
INNER JOIN*/

(SELECT ServerName,DBname,
cast(COUNT(dbname) as varchar(5))AS Occurrences
FROM DBINVENTORY d
GROUP BY ServerName,DBName
HAVING ( COUNT(DBName) > 1 )
)sq ON ss.DBName=sq.DBName


So does this mean you solved your issue or not?


Yes Sean...it solved my issue...


Sagar Sonawane
** Every DBA has his day!!
Post #1352095
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse