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

multiple relationships Expand / Collapse
Author
Message
Posted Thursday, February 9, 2012 3:09 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, January 17, 2014 7:01 PM
Points: 91, Visits: 464
how we can solve this problem
Cross Product
T_VOL_USAGE

VOL_name NODE-Name
V1 B
V12 F
V3 F
V2 C
V2 D
V3 E
i want a query that will dynamically eliminate the multiple relationships and resulting in V1, B as the only 1 to 1 Relationship
Post #1249999
Posted Thursday, February 9, 2012 3:34 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:48 PM
Points: 13,427, Visits: 12,292
You are going to have to provide some level of detail in order for anybody to have a chance here. There just simply isn't enough information in your post. Take a look at the first link in my signature about best practices on posting questions.

_______________________________________________________________

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 #1250007
Posted Thursday, February 9, 2012 4:09 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 4:27 AM
Points: 1,919, Visits: 19,323
here's a thought...any good?


USE [tempdb]
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[T_VOL_USAGE]') AND type in (N'U'))
DROP TABLE [dbo].[T_VOL_USAGE]
GO

CREATE TABLE [dbo].[T_VOL_USAGE](
[VOL_NAME] [nvarchar](5) NULL,
[NODE_NAME] [nvarchar](5) NULL
) ON [PRIMARY]

GO

INSERT INTO [dbo].[T_VOL_USAGE]([VOL_NAME], [NODE_NAME])
SELECT 'V1', 'B' UNION ALL
SELECT 'V12', 'F' UNION ALL
SELECT 'V3', 'F' UNION ALL
SELECT 'V2', 'C' UNION ALL
SELECT 'V2', 'D' UNION ALL
SELECT 'V3', 'E'

SELECT TV.VOL_NAME,
TV.NODE_NAME
FROM T_VOL_USAGE TV
INNER JOIN (SELECT VOL_NAME
FROM T_VOL_USAGE
GROUP BY VOL_NAME
HAVING ( COUNT(*) = 1 )) A ON TV.VOL_NAME = A.VOL_NAME
INNER JOIN (SELECT NODE_NAME
FROM T_VOL_USAGE
GROUP BY NODE_NAME
HAVING ( COUNT(*) = 1 )) B ON TV.NODE_NAME = B.NODE_NAME



______________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Post #1250015
Posted Thursday, February 9, 2012 5:18 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, January 17, 2014 7:01 PM
Points: 91, Visits: 464
thanks it works
Post #1250025
Posted Friday, October 26, 2012 5:20 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, December 13, 2012 7:50 PM
Points: 5, Visits: 22
I am just curious for why you included 'brackets' [ inside every object in your query solution?
I am a newbie and I am just confused for why it looks so complicated with all those brackets?
Will it be possible to successfully execute the query without including the brackets and just keeping it simple?
Thank you
Highest regards
Ernesto
Post #1377914
Posted Saturday, October 27, 2012 5:21 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Sunday, August 24, 2014 3:38 PM
Points: 336, Visits: 942
ernesto.felix.city (10/26/2012)
I am just curious for why you included 'brackets' [ inside every object in your query solution?
I am a newbie and I am just confused for why it looks so complicated with all those brackets?
Will it be possible to successfully execute the query without including the brackets and just keeping it simple?
Brackets are necessary if you have 'bad' object names, i.e. names with spaces, foreign characters and such. If the names are well constructed, brackets are not necessary, but they never hurt. Some people just get in the habit of using them as a precaution, some people put them in posts to keep your (potentially) badly named object from breaking the code they post for you, when you change object names from their examples to your own.
Post #1377941
Posted Sunday, October 28, 2012 9:53 PM


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 @ 10:55 PM
Points: 3,648, Visits: 5,321
I am not 100% sure given the limited sample data but this may also work:

SELECT VOL_NAME, NODE_NAME=MAX(NODE_NAME) 
FROM T_VOL_USAGE
GROUP BY VOL_NAME
HAVING COUNT(*) = 1
INTERSECT
SELECT VOL_NAME=MAX(VOL_NAME), NODE_NAME
FROM T_VOL_USAGE
GROUP BY NODE_NAME
HAVING COUNT(*) = 1





My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1378088
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse