Select item in a column

Hi,

I have a query

select distinct TR.ModifiedByID,TR.ModifiedBy,ICD.ModifiedByID,
ICD.ModifiedBy 
from TestingRules TR
left join TestingICD ICD on TR.RuleID = ICD.RuleID
order by TR.ModifiedBy asc

Open in new window


the output of the above query is this

ModifiedByID      ModifiedBy                  ModifiedByID           ModifiedBy 
a133123	             Smith, Latife	NULL	                NULL
a133123	             Smith, Latife	a133123	                Smith, Latife
a133123	             Smith, Latife	s132345	                Smith, John
c631826	             Parne, Anil	                 c631826	                Parne, Anil

Open in new window


but what I want the output to be like this

ModifiedByID      ModifiedBy                 
a133123	             Smith, Latife	
s132345	             Smith, John
c631826	             Parne, Anil	                

Open in new window


I need your help, beacause I have no clue how to do it.

Thanks,
Lulu
lulu50Asked:
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.

Shaun KlineLead Software EngineerCommented:
You want the data from TestingICD where there is a corresponding record in TestingRules?

You can query TestingICD and include a WHERE EXISTS clause to only return records that also exist in TestingRules.

You can also do it with your query by removing the columns for TestingRules from your select clause.
0
PortletPaulfreelancerCommented:
select distinct TR.ModifiedByID,TR.ModifiedBy
from TestingRules TR
left join TestingICD ICD on TR.RuleID = ICD.RuleID
order by TR.ModifiedBy asc
0
lulu50Author Commented:
Shaun -> please provide an example

Paul -> Your query did not give me the correct result. I don't see "John,Smith" in my result because john smith belong to TestingICD "ModifiedBy"
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Shaun KlineLead Software EngineerCommented:
Using your query:
select distinct ICD.ModifiedByID,
ICD.ModifiedBy 
from TestingRules TR
left join TestingICD ICD on TR.RuleID = ICD.RuleID
order by TR.ModifiedBy asc

Open in new window


I didn't notice the ORDER BY, so the query above is the best choice.
0
lulu50Author Commented:
Shaun,

I get this error :

Msg 145, Level 15, State 1, Line 2
ORDER BY items must appear in the select list if SELECT DISTINCT is specified
0
PortletPaulfreelancerCommented:
SELECT
      TR.ModifiedByID
    , TR.ModifiedBy
FROM TestingRules TR

UNION

SELECT
      ICD.ModifiedByID
    , ICD.ModifiedBy
FROM TestingICD ICD

ORDER BY ModifiedBy ASC

Open in new window

0
Shaun KlineLead Software EngineerCommented:
Not necessarily the best option but try this:
select ICD.ModifiedByID, ICD.ModifiedBy 
from TestingRules TR
left join TestingICD ICD on TR.RuleID = ICD.RuleID
GROUP By ICD.ModifiedByID, ICD.ModifiedBy
order by TR.ModifiedBy asc

Open in new window

0
Shaun KlineLead Software EngineerCommented:
Curious. Is your intent to remove the row that has NULL for the last two columns in the original dataset you posted? If that is the case, you could just change your LEFT JOIN into an INNER JOIN.
0
lulu50Author Commented:
Paul your query is correct but I want to do the order by

I'm getting an error when I add order by to the union
0
lulu50Author Commented:
Shaun,

I should select ModifiedBy from both tables.
because John smith is in TestingICD
0
PortletPaulfreelancerCommented:
what is the error (please always include it with comments about an error)

you cannot include the table alias now

or you could try "order by 2 asc" (but I prefer to use column names instead of column positions)

SELECT
      TR.ModifiedByID
    , TR.ModifiedBy
FROM TestingRules TR

UNION

SELECT
      ICD.ModifiedByID
    , ICD.ModifiedBy
FROM TestingICD ICD

ORDER BY 2 ASC

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
lulu50Author Commented:
Yes, IT WORKS

Thank you Paul it is what I want.
0
lulu50Author Commented:
Thank you
0
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
Microsoft SQL Server 2008

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.