SQL query help

Dear expert

Please help with the sql query:

Where ValidTo > '2018-04-26' and ValidTo is NULL I need the criteria like that. But not working.
I also need when column 'ID' is showing same number pick the one of the sort_order column what has number '2' in the result.

Regards
LVL 1
WeTiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SujithData ArchitectCommented:
What database are you on?  Oracle/SQL server/DB2...?
what is the error?
0
WeTiAuthor Commented:
OK the first is ok, I just change the statement to OR.
0
WeTiAuthor Commented:
MSSQL
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

WeTiAuthor Commented:
Now I need this to work: if ID is the same, select the sort_order column with value 1
0
SujithData ArchitectCommented:
Select <columns>
from (
 <your query
)
where sort_order = 1
;
0
WeTiAuthor Commented:
if ID column got same value select the column sort_order = 1 in the same row... your quest shows all rows with criteria 1 in sort_order.
0
SujithData ArchitectCommented:
Can you post the full query? or the section that generates the sort_order?
0
WeTiAuthor Commented:
Sure, now I got double with the criteria below, and I want if ID got same value select the sort_order with value 1.  
select P.SSN, P.FirstName, P.LastName, isnull(E.EmailAddress,'') as Email,
    Telefon = isnull ( STUFF((
        SELECT ', ' + TelephoneNo FROM Data_Telephone TEL WHERE TEL.ID = P.ID
        FOR XML PATH ('')
    ),1,2,'')
    ,'')
    ,isnull(AD.Careof,'')as Careof , AD.StreetAddress, AD.Zipcode, AD.City, Type, ID
from Data_Membership M
inner join Data_Person P on M.ID = P.ID
inner join Data_Email E on E.ID = P.ID
inner join Data_Address AD on ( AD.ID = P.ID )

where M.Status = 'Member' and StreetAddress is not null and Zipcode is not null and city is not null and ValidTo is null or ValidTo > '2018-04-26' 
ORDER BY Type asc

Open in new window

0
SujithData ArchitectCommented:
Try this -
select  *
from (
select P.SSN, P.FirstName, P.LastName, isnull(E.EmailAddress,'') as Email,
    Telefon = isnull ( STUFF((
        SELECT ', ' + TelephoneNo FROM Data_Telephone TEL WHERE TEL.ID = P.ID
        FOR XML PATH ('')
    ),1,2,'')
    ,'')
    ,isnull(AD.Careof,'')as Careof , AD.StreetAddress, AD.Zipcode, AD.City, Type, ID
    , ROW_NUMBER() OVER(ORDER BY ID ASC) AS RN
from Data_Membership M
inner join Data_Person P on M.ID = P.ID
inner join Data_Email E on E.ID = P.ID
inner join Data_Address AD on ( AD.ID = P.ID )
where M.Status = 'Member' and StreetAddress is not null and Zipcode is not null and city is not null and ValidTo is null or ValidTo > '2018-04-26' 
)
where rn = 1

Open in new window

0
ste5anSenior DeveloperCommented:
You need to take into consideration, that AND has the higher precedence. Thus you need parentheses:

SELECT   P.SSN ,
         P.FirstName ,
         P.LastName ,
         ISNULL(E.EmailAddress, '') AS Email ,
         ISNULL(STUFF((   SELECT ', ' + TelephoneNo
                          FROM   Data_Telephone TEL
                          WHERE  TEL.ID = P.ID
                          FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(255)') ,
                      1 ,
                      2 ,
                      '') ,
                '') AS Telefon ,
         ISNULL(AD.Careof, '') AS Careof ,
         AD.StreetAddress ,
         AD.Zipcode ,
         AD.City ,
         [Type] ,
         ID
FROM     Data_Membership M
         INNER JOIN Data_Person P ON M.ID = P.ID
         INNER JOIN Data_Email E ON E.ID = P.ID
         INNER JOIN Data_Address AD ON ( AD.ID = P.ID )
WHERE    M.[Status] = 'Member'
         AND StreetAddress IS NOT NULL
         AND Zipcode IS NOT NULL
         AND city IS NOT NULL
         AND (   ValidTo IS NULL
                 OR ValidTo > '2018-04-26' )
ORDER BY [Type] ASC;

Open in new window


btw, use always table alias names, there are some columns without. Also consider using the TYPE with the .value() a approach to decode entities correctly:

DECLARE @Sample TABLE
    (
        ID INT ,
        Payload NVARCHAR(255)
    );

INSERT INTO @Sample ( ID ,
                      Payload )
VALUES ( 1, N'<10' ) ,
       ( 1, N'20' ) ,
       ( 1, N'>30' );

SELECT   O.ID ,
         STUFF((   SELECT ', ' + I.Payload
                   FROM   @Sample I
                   WHERE  I.ID = O.ID
                   FOR XML PATH('')) ,
               1 ,
               2 ,
               '') AS EntityTroubles ,
         STUFF((   SELECT ', ' + I.Payload
                   FROM   @Sample I
                   WHERE  I.ID = O.ID
                   FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(255)') ,
               1 ,
               2 ,
               '') AS BetterLikeThis
FROM     @Sample O
GROUP BY O.ID;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
WeTiAuthor Commented:
Sujith I got Msg 156, Level 15, State 1, Line 18
Incorrect syntax near the keyword 'where'.

Ste5an your query didn't do the: if find ID column is same value as ID column select the sort_order column with value 1
0
ste5anSenior DeveloperCommented:
???
0
WeTiAuthor Commented:
I want the output show this:
If ID column shows doubles value, pick the one that has value 2 in the Sort_order column in the same row. if you do: where sort_order = 1 then the result output only shows the column sort_order with 1 value. Its wrong for me to get.
For exempel:
ID                        Name                  Sort_order                Address
8282                   Henrik                  1                                Some_street_name_1
8182                   Maria                    1                                Some_street_name_1
8282                   Henrik                  2                                 Some_diffirent_street_name_2
8182                   Maria                    2                                Some_diffirent_Street_name_2
1234                   Eric                        1                                Some_street_name_1
1232                   Lisa                        1                               Some_street_name_1

Now that is the exemple of the output, once this happens, I want to see Sort_order 2 in the total result, output sort_order 1 you can exclude, but all other output with Sort_order 1 we still want to see in the result. like 1234 and 1232 those still should show up.  Now this is not as much as urgent anymore, but i still want to know how.

I did a workaround in this is to export all to excel and use the data remove duplication. But i still want to know how to do in SQL.
0
WeTiAuthor Commented:
Anyone?
0
ste5anSenior DeveloperCommented:
I have to admit, that I don't understand your problem. It's not clear, what your problem is.

Please post a concise and complete T-SQL sample, include table DDL and sample data INSERT statements as a single, runnable T-SQL script.
0
WeTiAuthor Commented:
ID                        Name                  Sort_order                Address
8282                   Henrik                  1                                Some_street_name_1
8182                   Maria                    1                                Some_street_name_1
8282                   Henrik                  2                                 Some_diffirent_street_name_2
8182                   Maria                    2                                Some_diffirent_Street_name_2
1234                   Eric                        1                                Some_street_name_1
1232                   Lisa                        1                               Some_street_name_1

Here is the output of a query. Now I want to show: sort_order = 2 as result in the total duplicating result... I want it to show like this:
ID                        Name                  Sort_order                Address
8282                   Henrik                  2                                 Some_diffirent_street_name_2
8182                   Maria                    2                                Some_diffirent_Street_name_2
1234                   Eric                        1                                Some_street_name_1
1232                   Lisa                        1                               Some_street_name_1
Because ID is a duplication, I want only the Sort_order 2 as in the total result.
If I do: where Sort_Order = '2' This will only show all with Sort_order 2.
0
ste5anSenior DeveloperCommented:
Please post a concise and complete T-SQL sample, include table DDL and sample data INSERT statements as a single, runnable T-SQL script
0
WeTiAuthor Commented:
Never mind for this now, the double row result you can sort those rows in Excel and remove the double rows the there. I thought there should be a SQL query way for doing this.
0
ste5anSenior DeveloperCommented:
I thought there should be a SQL query way for doing this.
There is a way.

But we help on a voluntarily basis. So you need to help us by providing a concise and complete example (in this case as on runnable T-SQL script) to enable us to help you. Cause it is an unnecessarily time consuming task, taking your textual description to craft our own sample.  When you do this, you also show the necessary understanding of tackling a problem.

Thus as I like to cite myself:
Please post a concise and complete T-SQL sample, include table DDL and sample data INSERT statements as a single, runnable T-SQL script.

If you wonder, how this could look like: exactly as the second script in #42546408.
1
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

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.