• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 308
  • Last Modified:

MS SQL Question select question

Hello

MS SQL Server 2008

I've got two tables Company and Person.
The Table Person includes the column ReportA and ReportB
Persons are linked to their Company's with the field: person.comp_id
So each Company has one or more Persons linked.

I would like to select a list of all company's where none of the persons has the report "xyz" neither in the column ReportA and ReportB

Some Company's have persons that uses "xyz" others don't use them. I need all the company's where none of the linked persons uses the report "xyz".

This was my "Solution", but this will also give you company's where one ore more Persons use the Report "xyz".

select   company.Company_Name
from Person  inner join Company on person.Id = company.Id
where (reportA not like '%xyz%' or reportA is null)
and (reportb not like '%xyz%' or reportb is null)
0
HelpdeskJBC
Asked:
HelpdeskJBC
  • 5
  • 3
  • 3
  • +5
2 Solutions
 
plusone3055Commented:
select   company.Company_Name
from Person  inner join Company on person.Id = company.Id
where ((reportA not like '%xyz%' or reportA is null) and (reportb not like '%xyz%' or reportb is null))
0
 
LowfatspreadCommented:
use not exists

and just query the company table using a correlated not exists sub select to determine the presence of xyz report usage...

e.g.

select   c.Company_Name 
from Company as c
where not exists (select p.id from person as p
   where p.id=c.id
     and (p.reporta like '%xyz%# or p.reportb like '%xyz%') 
)

Open in new window

0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
select   company.Company_Name, count(*)
from Person  inner join Company on person.Id = company.Id
where (reportA not like '%xyz%' or reportA is null)
and (reportb not like '%xyz%' or reportb is null)
group by company.company_name
having (count(*) = 0)
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
LowfatspreadCommented:
sorry you said its linked on comp_id...

select   c.Company_Name
from Company as c
where not exists (select p.comp_id from person as p
   where p.comp_id=c.id
     and (p.reporta like '%xyz%# or p.reportb like '%xyz%')
)
0
 
Ioannis ParaskevopoulosCommented:
Hi,

You may try the following:

SELECT company.Company_Name
FROM company
WHERE company.id IN
(
    SELECT company.id
    FROM Person 
    INNER JOIN Company ON person.id = company.id
    WHERE reportA LIKE '%xyz%' 
    OR reportB LIKE '%xyz%'
)

Open in new window


Giannis
0
 
Scott PletcherSenior DBACommented:
I think it's somewhat more involved than that, as below.

I used only the comany id on the inner code to keep the GROUP BY as efficient as possible.  Then use that company id to lookup all the details with a final join to the company table.



SELECT
    c2.*
FROM (
    SELECT
        c.Id
    FROM
        dbo.Person p
    INNER JOIN dbo.Company c ON
        c.Id = p.comp_id
    GROUP BY
        c.Id
    HAVING
        MAX(CASE WHEN p.reportA LIKE '%xyz%' THEN 1 ELSE 0 END) = 0 AND
        MAX(CASE WHEN p.reportB LIKE '%xyz%' THEN 1 ELSE 0 END) = 0
) compsNotXyz
INNER JOIN dbo.Company c2 ON
    c2.Id = compsNotXyz.Id
ORDER BY
    c2.Company_Name
0
 
awking00Commented:
select company.id
except
select person.comp_id
where reporta like '%xyz%' or reportb like '%xyz%';
0
 
HelpdeskJBCAuthor Commented:
ok 20 solutions with 20 different results

I try the solution with "not exists" so here is my sql string:

select   c.Company_Name
from Company as c
where company_state not like '%inactive%' and not exists
(
select *
from Person as p
where p.Id=c.Id
and (p.ReportA like '%xyz%' or p.ReportBt like '%xyz%')

)
      order by Company_Name

My Question:
I also need two more fields from the Person table "person_deleted" this must be Null
and "person_status" this must be 'activated'

How could i include this two parameters in the sql?
0
 
Ioannis ParaskevopoulosCommented:
Hi,

select   c.Company_Name
from Company as c
where company_state not like '%inactive%' and not exists 
(
select * 
from Person as p
where p.Id=c.Id 
and (p.ReportA like '%xyz%' or p.ReportBt like '%xyz%') 
and p.person_deleted IS NULL
and p.person_status = 'activated'
)
      order by Company_Name

Open in new window


Giannis
0
 
jayruizcoeCommented:
select company_name
 from company
 
 except


select   company.Company_Name
from Person  inner join Company on person.Id = company.Id
where (reportA  like '%xyz%'
 or reportb  like '%xyz%') and person_deleted IS NULL and person_status = 'activated'
0
 
HelpdeskJBCAuthor Commented:
@jyparask

i like to get the company list where every person in it has not the report xyz.

after "not exist" i search for the persons company's which have this report.
So as far as i understand the result is like:
[all company's  MINUS  all company's that have this report]

So there is one company that has only inactive persons (with report xyz)
All Comp minus Comp with active persons will show me also Comp with inactive P,
but I only need those Comp with no inactive and no Report xyz

I need active persons with report xyz so may i change  
and p.person_status = 'activated' to and p.person_status = 'not_active'
0
 
LowfatspreadCommented:
My Question:
I also need two more fields from the Person table "person_deleted" this must be Null
and "person_status" this must be 'activated'


i'm no longer sure what it is you are trying to achieve...

if you just want to consider the active persons having the xyz report for the company list then you put that condition in the (not) exists subquery,,,

select   c.Company_Name
from Company as c
where company_state not like '%inactive%' and not exists 
(
select * 
from Person as p
where p.comp_Id=c.Id
and p.person_deleted is null
and p.person_status ='activated'
and (p.ReportA like '%xyz%' or p.ReportBt like '%xyz%') 

)
      order by Company_Name

Open in new window



however it now appears you are actually wanting to use the result of this query in some update scenario....

could you please restate what it is you are actually attempting to do, and illustrate the problem with some example data before and after the desired action.
0
 
Ioannis ParaskevopoulosCommented:
Hi,

Hopefully this will help:

SELECT	* 
FROM	Company AS C
JOIN	Person AS P
		ON P.ID = C.ID 
WHERE	P.Person_deleted IS NULL
		AND P.Person_status = 'activated'
		AND NOT (P.ReportA LIKE '%xyz%' OR P.ReportB LIKE '%xyz%')

Open in new window

Giannis
0
 
HelpdeskJBCAuthor Commented:
I need only a Company List with each Comp where no active user has the report "xyz"

In your Query I do All Companies minus Companies with active xyz users right?
So the Result is a List of Firms with still users that are inactive and use xyz.

if we delete line 9 "and p.person_status ='activated'"
we get all Firms where nobody becomes this report "xyz" right?
This should work, but there are Companies where all people are inactive so this comp should not be in the list. Is it possible to search outside the "not exists" for companies that have active users?
0
 
Ioannis ParaskevopoulosCommented:
Hi,

In my previous example i forgot to add the company_state in the where clause.

SELECT	* 
FROM	Company AS C
JOIN	Person AS P
		ON P.ID = C.ID 
WHERE	P.Person_deleted IS NULL
		AND P.Person_status = 'activated'
		AND NOT (P.ReportA LIKE '%xyz%' OR P.ReportB LIKE '%xyz%')
                AND company_state not like '%inactive%'

Open in new window


Giannis
0
 
Ioannis ParaskevopoulosCommented:
I need to add that in Lowfatspread's example you could join this with the person table and remove all the inactives

select  distinct c.Company_Name
from Company as c
join Person p
on p.comp_Id=c.id
where company_state not like '%inactive%' and not exists 
(
select * 
from Person as p
where p.comp_Id=c.Id
and p.person_deleted is null
and (p.ReportA like '%xyz%' or p.ReportBt like '%xyz%') 

)
and p.person_status ='activated'

      order by Company_Name

Open in new window

Giannis
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.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 5
  • 3
  • 3
  • +5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now