Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

MS SQL Question select question

Posted on 2013-02-04
16
Medium Priority
?
300 Views
Last Modified: 2013-02-05
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
Comment
Question by:HelpdeskJBC
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 3
  • +5
16 Comments
 
LVL 22

Expert Comment

by:plusone3055
ID: 38851150
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
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 38851155
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
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 38851156
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
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
LVL 50

Accepted Solution

by:
Lowfatspread earned 1600 total points
ID: 38851162
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
 
LVL 23

Expert Comment

by:Ioannis Paraskevopoulos
ID: 38851202
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
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 38851717
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
 
LVL 32

Expert Comment

by:awking00
ID: 38852481
select company.id
except
select person.comp_id
where reporta like '%xyz%' or reportb like '%xyz%';
0
 

Author Comment

by:HelpdeskJBC
ID: 38854068
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
 
LVL 23

Expert Comment

by:Ioannis Paraskevopoulos
ID: 38854085
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
 

Expert Comment

by:jayruizcoe
ID: 38854128
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
 

Author Comment

by:HelpdeskJBC
ID: 38854213
@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
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 38854374
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
 
LVL 23

Expert Comment

by:Ioannis Paraskevopoulos
ID: 38854379
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
 

Author Comment

by:HelpdeskJBC
ID: 38854415
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
 
LVL 23

Expert Comment

by:Ioannis Paraskevopoulos
ID: 38854430
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
 
LVL 23

Assisted Solution

by:Ioannis Paraskevopoulos
Ioannis Paraskevopoulos earned 400 total points
ID: 38854437
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

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

704 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question