Selecting Records that exist with noexistant

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Selecting Records that exist with noexistant

Author

Topic

Beast777
Starting Member

10 Posts

Posted - 2008-01-10 : 09:44:11

I have a qualification database and I have a report that shows the poeple qualified. I have another report that shows the people who have not qualified. How do I get a report showing both? So if each person has 3 qualifications, I would like it to show the name, the qualification name, and then whether completed or not.

I have this query to get all the pers who have not completed a qualification but it is only for the one (IBTSID).

Select Service_Number, Last_Name, Initials, Rank, Squadron, Element from Persdata
where not exists (Select * from IBTSQuals where IBTSID = '1' and Persdata.Service_Number = IBTSQuals.Service_Number)
and Status = 'Active' and Emp_Type = 'Staff' order by Last_Name

This gets everyones Service Number (Primary Key), name etc and the where not exists gets the records where there is a qualification and then gives me the names of who has not done it.

Is there a way to have both together?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-10 : 09:49:05

Can you post structure of your tables?

Beast777
Starting Member

10 Posts

Posted - 2008-01-10 : 10:17:26

The persdata table is pretty huge but really only need these

Persdata Table

Service_Number (primary key)
Last_Name
Initials
Rank
Stauts
Squadron
Element
Emp_Type

IBTSQuals

IBTSQualID (Primary key)
IBTSID
Service_Number
IBTSDate
Status

There is a third table called IBTS that lists what each IBTSID is. Just for info here is that table.

IBTS

IBTSID (Primary Key)
Name
Description
Notes

I don't think you really need the third table but for output I am hoping to see something like

Service_Number, Last_Name, Initials, Rank, Squadron, IBTSID, Status, IBTSDate (now there wont't be an entry for Status or IBTSDate where data does not exist)

Example:

N11 111 111, Smith, JP, Pte, 1, 1, Pass, 10-10-2007
N11 111 111, Smith, JP, Pte, 1, 2, Pass, 10-11-2007
N11 111 111, Smith, JP, Pte, 1, 3, Pass, 12-10-2007
N22 222 222, Brown, TF, Cpl, HQ, 1, Pass, 10-10-2007
N22 222 222, Brown, TF, Cpl, HQ, 2, ,
N22 222 222, Brown, TF, Cpl, HQ, 3, Pass, 10-09-2007
P33 333 333, Nightly, IP, Pte, 1, 1, ,
P33 333 333, Nightly, IP, Pte, 1, 2, ,
P33 333 333, Nightly, IP, Pte, 1, 3, Pass, 11-04-2005

Something like that.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-10 : 10:31:00

Thanks for the details.
Try this:-

SELECT
t1.Service_Number, 
t1.Last_Name, 
t1.Initials, 
t1.Rank, 
t1.Squadron, 
t1.IBTSID, 
ISNULL(t2.Status,''), 
ISNULL(t2.IBTSDate ,'')
FROM 
  ( SELECT p.Service_Number, 
        p.Last_Name, 
        p.Initials,
        p.Rank,
        p.Squadron,
        i.IBTSID
   FROM Persdata p
   CROSS JOIN 
   (SELECT DISTINCT IBTSID FROM IBTSQuals)i
  )t1
LEFT OUTER JOIN IBTSQuals t2
ON t2.Service_Number=t1.Service_Number

Beast777
Starting Member

10 Posts

Posted - 2008-01-10 : 10:37:42

Thank you ever so much

Beast777
Starting Member

10 Posts

Posted - 2008-01-10 : 10:39:41

Actually a couple last questions, if I wanted it to display the Name from the IBTS table instead of the IBTSID how much harder would that be? and if I wanted to add "where Persdata.Emp_Type = 'Staff'" where would that go.

Beast777
Starting Member

10 Posts

Posted - 2008-01-10 : 11:02:21

Actually there is something wrong with the query. It works okay if they have no qualifications but for every one they actually have a pass and date for, they get one entry in every qual. So if they pass 2 and dont do one, they get 6 passes and one no entry.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-10 : 11:25:17

quote:
Originally posted by Beast777

Actually there is something wrong with the query. It works okay if they have no qualifications but for every one they actually have a pass and date for, they get one entry in every qual. So if they pass 2 and dont do one, they get 6 passes and one no entry.

didnt understand this..Can you give an example?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-10 : 11:34:54

Ah! i think i got u. Try this and see if gives you expected result:-

SELECT
t1.Service_Number, 
t1.Last_Name, 
t1.Initials, 
t1.Rank, 
t1.Squadron,
t1.Emp_type, 
t1.IBTSID,
t1.Name as 'IBTSName', 
ISNULL(t2.Status,''), 
ISNULL(t2.IBTSDate ,'')
FROM 
  ( SELECT p.Service_Number, 
        p.Last_Name, 
        p.Initials,
        p.Rank,
        p.Squadron,p.Emp_Type,
        i.IBTSID,i.Name
   FROM Persdata p
   CROSS JOIN 
   (SELECT DISTINCT q.IBTSID,m.Name FROM IBTSQuals q
    INNER JOIN IBTS m
    ON m.IBTSID=q.IBTSID
     )i
   WHERE p.Emp_Type="Staff"
  )t1
LEFT OUTER JOIN IBTSQuals t2
ON t2.Service_Number=t1.Service_Number
AND t2.IBTSID = t1.IBTSID

Beast777
Starting Member

10 Posts

Posted - 2008-01-10 : 11:38:25

Here is a person from the database. There is actually more qualifications than I said because whether its 3 or 11 it really didnt matter. So here is there info from the query you made.

056 727 111
Lacroix MR Mrs Sp Sqn 7 Pass 2007-10-29 10:03:00
056 727 111
Lacroix MR Mrs Sp Sqn 6 Pass 2007-10-29 10:03:00
056 727 111
Lacroix MR Mrs Sp Sqn 5 Pass 2007-10-29 10:03:00
056 727 111
Lacroix MR Mrs Sp Sqn 12 Pass 2007-10-29 10:03:00
056 727 111
Lacroix MR Mrs Sp Sqn 4 Pass 2007-10-29 10:03:00
056 727 111
Lacroix MR Mrs Sp Sqn 11 Pass 2007-10-29 10:03:00
056 727 111
Lacroix MR Mrs Sp Sqn 3 Pass 2007-10-29 10:03:00
056 727 111
Lacroix MR Mrs Sp Sqn 18 Pass 2007-10-29 10:03:00
056 727 111
Lacroix MR Mrs Sp Sqn 10 Pass 2007-10-29 10:03:00
056 727 111
Lacroix MR Mrs Sp Sqn 2 Pass 2007-10-29 10:03:00
056 727 111
Lacroix MR Mrs Sp Sqn 1 Pass 2007-10-29 10:03:00
056 727 111
Lacroix MR Mrs Sp Sqn 16 Pass 2007-10-29 10:03:00
056 727 111
Lacroix MR Mrs Sp Sqn 8 Pass 2007-10-29 10:03:00

if I go to the IBTSQuals table and do a pull like this:

Select * from IBTSQuals where Service_Number = '056 727 111'

I get one record:

1292 056 727 111 2007-10-29 10:03:00 Pass

If you look at the data that came back from the query, you will see they all have the exact same date too. If this person had another pass in another qualification, the amount of data would double again.

Beast777
Starting Member

10 Posts

Posted - 2008-01-10 : 11:42:16

Oh My god, thats perfect! You are a freaking genius. I have been pulling my hair out for some time.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-10 : 11:47:15

quote:
Originally posted by Beast777

Oh My god, thats perfect! You are a freaking genius. I have been pulling my hair out for some time.

Cool.Cheers buddy.Please feel free to come here whenever you face problem.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources