Select Query

I'm playing with this and cannot seem to get the proper results. I am looking to only select the main 4 "RoadJur" values withing that column. Currently i am selecting all of them. Whats the best way in the select statement at the top to NOT  select the "WAO" Value? which leaves me with the "KINGS", "TWP", "CNTY","PRIV" values.


SELECT     TOP (100) PERCENT dbo.Segment.ROADJUR, dbo.Segment.L_F_ADD, dbo.Segment.L_T_ADD, dbo.Segment.R_F_ADD, dbo.Segment.R_T_ADD, 
                      dbo.Segment.Geometry,
                          (SELECT     LTRIM(RTRIM(UPPER(ISNULL(dbo.Street.Prefix_Full, ''))) + ' ' + RTRIM(UPPER(ISNULL(dbo.Street.Street_Name, ''))) 
                                                   + ' ' + RTRIM(UPPER(ISNULL(dbo.Street.Suffix_Cnty, ''))) + '' + RTRIM(UPPER(ISNULL(dbo.Street.Suffix_Bell, ''))) 
                                                   + ' ' + RTRIM(UPPER(ISNULL(dbo.Street.Suffix_Direction, '')))) AS Expr1) AS Name, w1.Ward_Name AS L_MUNAME, w2.Ward_Name AS R_MUNAME, 
                      CASE WHEN dbo.Segment.ROADJUR = 'KINGS' THEN 9 WHEN dbo.Segment.ROADJUR = 'CNTY' THEN 15 WHEN dbo.Segment.ROADJUR = 'TWP' THEN 16 WHEN dbo.Segment.ROADJUR
                       = 'PRIV' THEN 17 END AS [LEVEL]
FROM         dbo.Segment LEFT OUTER JOIN
                      dbo.Ward AS w1 ON dbo.Segment.Left_Mun_ID = w1.Ward_ID LEFT OUTER JOIN
                      dbo.Ward AS w2 ON dbo.Segment.Right_Mun_ID = w2.Ward_ID INNER JOIN
                      dbo.Street ON dbo.Segment.Street_ID = dbo.Street.Street_ID

Open in new window

PtboGiserAsked:
Who is Participating?
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>Whats the best way in the select statement at the top to NOT  select the "WAO" Value? which leaves me with the "KINGS", "TWP", "CNTY","PRIV" values.
To pull off conditions like this add them to the WHERE clause..

{your query above}
WHERE RoadJur IN ('KINGS', 'TWP', 'CNTY', PRIV')

<or>

{your query above}
WHERE RoadJur <> 'WAO'
0
 
PtboGiserAuthor Commented:
I'm doing this and its wrong

SELECT     TOP (100) PERCENT
                          (SELECT     ROADJUR
                            FROM          dbo.Segment
                            WHERE      (ROADJUR <> 'WAO')) AS Expr1,
Segment_1.L_F_ADD, Segment_1.L_T_ADD, Segment_1.R_F_ADD, Segment_1.R_T_ADD, Segment_1.Geometry,
0
 
PtboGiserAuthor Commented:
Man sometimes i overthink things. Sorry
Thx
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Thanks for the grade.  Good luck with your project.  -Jim
0
 
jogosCommented:
I guess that peace of code completed must be this (seeing the query in your first question)
SELECT     TOP (100) PERCENT
                          (SELECT     ROADJUR
                            FROM          dbo.Segment
                            WHERE      (ROADJUR <> 'WAO')) AS Expr1, 
Segment_1.L_F_ADD, Segment_1.L_T_ADD, Segment_1.R_F_ADD, Segment_1.R_T_ADD, Segment_1.Geometry,
from dbo.segment as Segment_1

Open in new window

Then you don't have a condition to join the subselect with the  rest of
the query, predictably giving error 'multiple rows .... where one row expected'.


Why you need a subselect to select that value when the table is already in the query?

 select .......
dbo.Segment 
LEFT OUTER JOIN  dbo.Ward AS w1 ON dbo.Segment.Left_Mun_ID = w1.Ward_ID
 LEFT OUTER JOIN  dbo.Ward AS w2 ON dbo.Segment.Right_Mun_ID = w2.Ward_ID 
INNER JOIN
                      dbo.Street ON dbo.Segment.Street_ID = dbo.Street.Street_ID
where RaodJur <> 'WAO'

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.