Solved

MS SQL Question select question

Posted on 2013-02-04
16
243 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
  • 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
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 400 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 69

Expert Comment

by:ScottPletcher
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 31

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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 100 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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Help with SQL Query 23 39
Contained Database Collations 6 20
using t-sql EXISTS 8 24
t-sql splitting name column 5 23
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

743 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now