June 2, 2011 at 8:58 am
TABLE:
CREATE TABLE [dbo].[vwCourseClass](
[CourseTitle] [nchar](10) NULL,
[ClassTitle] [nchar](10) NULL
) ON [PRIMARY]
Insert values:
INSERT INTO [dbo].[vwCourseClass]
([CourseTitle]
,[ClassTitle])
VALUES
('F;CHA','F;CHA;B1')
Input SQL Query:
SELECT
vwCourseClass.ClassTitle
, vwCourseClass_1.ClassTitle
FROM
vwCourseClass
,vwCourseClass AS vwCourseClass_1
WHERE
(
((vwCourseClass.CourseTitle)='F;BUA')
And
((vwCourseClass_1.CourseTitle)='F;CHA' )
)
ORDER BY vwCourseClass.ClassTitle;
Output:
vwCourseClass.ClassTitle vwCourseClass_1.ClassTitle
F;BUA;A1 F;CHA;A1
F;BUA;A1 F;CHA;B1
F;BUA;E1 F;CHA;A1
F;BUA;E1 F;CHA;B1
Expected Output:
Now I would like to update the SQL Input so that I will get the output as:
F;BUA;A1 F;CHA;B1
F;BUA;E1 F;CHA;A1
F;BUA;E1 F;CHA;B1
Means: I would like to explude the combination where last 2 character match. Here ( F;BUA;A1 F;CHA;A1 ) in both data "A1" is same. So this data will exclude.
Please help. THanks.
June 2, 2011 at 10:10 am
Please take a look at the first link my signature for best practices on how to post a question in such a way that you will get the results you are looking for. Based on the description of what you posted it is unclear what the tables look like, what the data is and what you want for output.
_______________________________________________________________
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/
June 2, 2011 at 10:43 am
You can test the rightmost two characters by using the RIGHT() function. In your example, try adding this to the WHERE clause.
AND RIGHT(dbo_vwCourseClass.ClassTitle,2) <> RIGHT(dbo_vwCourseClass_1.ClassTitle,2)
However, the solution above is untested. I echo Sean's request. If you post CREATE statements for your source table, and INSERT statements for some sample data, you will more people willing to offer you coded and tested solutions.
Make it easier for people to help you.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
June 2, 2011 at 1:21 pm
Sorry this is my first log in a question about getting help. Thanks for your kind advice. I have updated it.
June 2, 2011 at 1:30 pm
The Dixie Flatline (6/2/2011)
You can test the rightmost two characters by using the RIGHT() function. In your example, try adding this to the WHERE clause.
AND RIGHT(dbo_vwCourseClass.ClassTitle,2) <> RIGHT(dbo_vwCourseClass_1.ClassTitle,2)
However, the solution above is untested. I echo Sean's request. If you post CREATE statements for your source table, and INSERT statements for some sample data, you will more people willing to offer you coded and tested solutions.
Make it easier for people to help you.
Hiya, Thank you for your code. It is giving me no result. Means after adding this, I am not getting any output of the query. Please help me. Thanks.
June 2, 2011 at 1:34 pm
If you actually provide some sample data (more than 1 row) either Dixie or myself can help in a matter of seconds. Otherwise the best either of us can do is speculate what your data might look like. Remember we can't see over your shoulder and we don't know anything about your project other than what you have posted.
_______________________________________________________________
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/
June 2, 2011 at 1:38 pm
Are you editing your first post? If so, please don't. Just add new posts so others can follow what is happening.
Looking at your data, there is no way it produces the output you describe in the first place. There is only one row, and it will be omitted by your WHERE clause because the course title cannot be both "F;BUA" and "F;CHA" before it even gets around to testing the rightmost two characters.
Do you see now why we need you to supply data that gives us your output, so that we can see what is needed to give you the output you really want?
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
June 2, 2011 at 1:51 pm
You beat me to it, Sean.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
June 2, 2011 at 1:51 pm
Hello,
Sorry yess I did update the original post. Okay here are the complete code which I just paste in a new query of MS SQL 2008.
DROP TABLE [dbo].[vwCourseClass]
GO
CREATE TABLE [dbo].[vwCourseClass](
[CourseTitle] [nchar](10) NULL,
[ClassTitle] [nchar](10) NULL
) ON [PRIMARY]
GO
INSERT INTO [dbo].[vwCourseClass] ([CourseTitle] ,[ClassTitle]) VALUES('F;BUA','F;BUA;A1')
INSERT INTO [dbo].[vwCourseClass] ([CourseTitle] ,[ClassTitle]) VALUES('F;BUA','F;BUA;E1')
INSERT INTO [dbo].[vwCourseClass] ([CourseTitle] ,[ClassTitle]) VALUES('F;CHA','F;CHA;A1')
INSERT INTO [dbo].[vwCourseClass] ([CourseTitle] ,[ClassTitle]) VALUES('F;CHA','F;CHA;B1')
GO
SELECT
vwCourseClass.ClassTitle
, vwCourseClass_1.ClassTitle
FROM
vwCourseClass
,vwCourseClass AS vwCourseClass_1
WHERE
(
((vwCourseClass.CourseTitle)='F;BUA')
And
((vwCourseClass_1.CourseTitle)='F;CHA' )
)
GO
Output:
F;BUA;A1 F;CHA;A1
F;BUA;E1 F;CHA;A1
F;BUA;A1 F;CHA;B1
F;BUA;E1 F;CHA;B1
Code need help to get output: (
F;BUA;E1 F;CHA;A1
F;BUA;A1 F;CHA;B1
F;BUA;E1 F;CHA;B1
June 2, 2011 at 1:57 pm
Thank you.
You are defining your course titles as NCHAR, which means they are padded with blanks on the right to the specified length. NVARCHAR might be a better choice, but you can trim the blanks using RTRIM().
Try this
AND RIGHT(RTRIM(vwCourseClass.ClassTitle),2) <> RIGHT(RTRIM(vwCourseClass_1.ClassTitle),2)
Hope you get an "A". 😉
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
June 2, 2011 at 2:00 pm
The Dixie Flatline (6/2/2011)
Thank you.You are defining your course titles as NCHAR, which means they are padded with blanks on the right to the specified length. NVARCHAR might be a better choice, but you can trim the blanks using RTRIM().
Try this
AND RIGHT(RTRIM(vwCourseClass.ClassTitle),2) <> RIGHT(RTRIM(vwCourseClass_1.ClassTitle),2)
Hope you get an "A". 😉
And you beat ME to it this time. 😛
_______________________________________________________________
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/
June 2, 2011 at 2:02 pm
Let's call it a great team effort and cross the finish line with hands joined in victory. 😀
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
June 2, 2011 at 2:04 pm
The Dixie Flatline (6/2/2011)
Let's call it a great team effort and cross the finish line with hands joined in victory. 😀
WOOHOO!!! Beers all around!!!!
_______________________________________________________________
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/
June 2, 2011 at 2:24 pm
Hiya...
OMG you guys are really really helpful. It works fine. I have to work on upto 10 data to compare. Not sure I am doing it right by comparing 2 at a time like folows or there is a short way to compare more then 2 at a time. I can carry on doing it manually like how I get expected output. But if there is a good way, then that would be really great.
Once again, many many thanks for your kind help.
DROP TABLE [dbo].[vwCourseClass]
GO
CREATE TABLE [dbo].[vwCourseClass](
[CourseTitle] [nchar](10) NULL,
[ClassTitle] [nchar](10) NULL
) ON [PRIMARY]
GO
INSERT INTO [dbo].[vwCourseClass] ([CourseTitle] ,[ClassTitle]) VALUES('F;BUA','F;BUA;A1')
INSERT INTO [dbo].[vwCourseClass] ([CourseTitle] ,[ClassTitle]) VALUES('F;BUA','F;BUA;E1')
INSERT INTO [dbo].[vwCourseClass] ([CourseTitle] ,[ClassTitle]) VALUES('E;BIA','E;BIA;A1')
INSERT INTO [dbo].[vwCourseClass] ([CourseTitle] ,[ClassTitle]) VALUES('E;BIA','E;BIA;B1')
INSERT INTO [dbo].[vwCourseClass] ([CourseTitle] ,[ClassTitle]) VALUES('F;CHA','F;CHA;A1')
INSERT INTO [dbo].[vwCourseClass] ([CourseTitle] ,[ClassTitle]) VALUES('F;CHA','F;CHA;E1')
INSERT INTO [dbo].[vwCourseClass] ([CourseTitle] ,[ClassTitle]) VALUES('F;CHA','F;CHA;F1')
INSERT INTO [dbo].[vwCourseClass] ([CourseTitle] ,[ClassTitle]) VALUES('1;REX','1;REX;A1')
INSERT INTO [dbo].[vwCourseClass] ([CourseTitle] ,[ClassTitle]) VALUES('1;REX','1;REX;B1')
INSERT INTO [dbo].[vwCourseClass] ([CourseTitle] ,[ClassTitle]) VALUES('1;REX','1;REX;A1')
INSERT INTO [dbo].[vwCourseClass] ([CourseTitle] ,[ClassTitle]) VALUES('1;REX','1;REX;B1')
INSERT INTO [dbo].[vwCourseClass] ([CourseTitle] ,[ClassTitle]) VALUES('1;REX','1;REX;C1')
INSERT INTO [dbo].[vwCourseClass] ([CourseTitle] ,[ClassTitle]) VALUES('1;REX','1;REX;D1')
INSERT INTO [dbo].[vwCourseClass] ([CourseTitle] ,[ClassTitle]) VALUES('1;REX','1;REX;E1')
INSERT INTO [dbo].[vwCourseClass] ([CourseTitle] ,[ClassTitle]) VALUES('1;REX','1;REX;F1')
GO
SELECT
vwCourseClass.ClassTitle
, vwCourseClass_1.ClassTitle
, vwCourseClass_2.ClassTitle
, vwCourseClass_3.ClassTitle
FROM
vwCourseClass
,vwCourseClass AS vwCourseClass_1
,vwCourseClass AS vwCourseClass_2
,vwCourseClass AS vwCourseClass_3
WHERE
(
((vwCourseClass.CourseTitle)='F;BUA')
And
((vwCourseClass_1.CourseTitle)='F;CHA' )
And
((vwCourseClass_2.CourseTitle)='E;BIA' )
And
((vwCourseClass_3.CourseTitle)='1;REX' )
AND
(RIGHT(RTRIM(vwCourseClass.ClassTitle),2) <> RIGHT(RTRIM(vwCourseClass_1.ClassTitle),2) )
AND
(RIGHT(RTRIM(vwCourseClass.ClassTitle),2) <> RIGHT(RTRIM(vwCourseClass_2.ClassTitle),2) )
AND
(RIGHT(RTRIM(vwCourseClass.ClassTitle),2) <> RIGHT(RTRIM(vwCourseClass_3.ClassTitle),2) )
AND
(RIGHT(RTRIM(vwCourseClass_1.ClassTitle),2) <> RIGHT(RTRIM(vwCourseClass_2.ClassTitle),2) )
AND
(RIGHT(RTRIM(vwCourseClass_1.ClassTitle),2) <> RIGHT(RTRIM(vwCourseClass_3.ClassTitle),2) )
AND
(RIGHT(RTRIM(vwCourseClass_2.ClassTitle),2) <> RIGHT(RTRIM(vwCourseClass_3.ClassTitle),2) )
)
GO
June 2, 2011 at 4:20 pm
I'm sorry to say I don't see another approach at this time.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply