February 9, 2012 at 3:09 pm
how we can solve this problem
Cross Product
T_VOL_USAGE
VOL_nameNODE-Name
V1B
V12F
V3F
V2C
V2D
V3E
i want a query that will dynamically eliminate the multiple relationships and resulting in V1, B as the only 1 to 1 Relationship
February 9, 2012 at 3:34 pm
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 9, 2012 at 4:09 pm
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
February 9, 2012 at 5:18 pm
thanks it works
October 26, 2012 at 5:20 pm
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
October 27, 2012 at 5:21 am
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.
October 28, 2012 at 9:53 pm
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 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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply