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 @ 8:15 AM
Points: 13,017, Visits: 10,800
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: Tuesday, August 12, 2014 11:45 PM
Points: 562, Visits: 887
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 @ 8:15 AM
Points: 13,017, Visits: 10,800
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: Today @ 3:59 PM
Points: 13,007, Visits: 12,426
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: Today @ 6:55 PM
Points: 3,636, Visits: 8,150
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.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

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