ylsvani (8/27/2012)

---

Thanks for the response. Here are the scripts for the above
I am new sql server. I am trying to get the following data. I have 3 different tables

create table table1(conid int,location varchar(3))
insert into table1 values(1,'CVG')
insert into table1 values(2,'DAY')
insert into table1 values(3,'CMH')
go
create table table2(conid int,cert varchar(5),certdate varchar(10))
insert into table2 values(1,'cert1','01/01/2001')
insert into table2 values(1,'cert2','02/03/2008')
insert into table2 values(1,'cert3','01/01/2012')
insert into table2 values(2,'cert2','02/02/2012')
go
create table table3(certid int,certdesc varchar(5))
insert into table3 values(1,'cert1')
insert into table3 values(2,'cert2')
insert into table3 values(3,'cert3')
insert into table3 values(4,'cert4')
go


I neeed to find all those conids who do not have cert1, cert2, cert3, cert4 in table2. The total required certs are in table3.

Here is the sample data, I am looking for
conid location certdate certdate certdate certdate
1 CVG 01/01/2001 02/03/2008 01/01/2012 NULL
2 DAY NULL 02/02/2012 NULL NULL

This indicates that conid 1 should still have cert4 and conid = 2 should have cert1date, cert3date and cert4date.

I need to create a report that have a date and also those ids who still do not have a date in the system.

Any help on this is greatly appreciated.

create table table2
(
	ConID int, 
	CertID int, 
	CertDate datetime
)

insert into table2 values(1,1, '1/1/2001')
insert into table2 values(1,2, '2/3/2008')
insert into table2 values(1,3, '1/1/2012')
insert into table2 values(2,2, '2/2/2012')

Luis Cazares (8/27/2012)

---

I'm sorry Matt, but your query looks complicated and more expensive than a simple Cross Tab that would require only 2 tables and an inner join.
I have the query but I don't want to post it so the OP can practice.

select
    t1.conid,
    t1.location,
    t3.certdesc + 'date' as certname,
    t2.certdate
from
    table1 t1
    cross join table3 t3
    left outer join table2 t2
        on t2.cert = t3.certdesc and
           t1.conid = t2.conid

Luis Cazares (8/28/2012)

---

I'm sorry I didn't replied yesterday but we had to leave the office because of Isaac.

Here's a query that will work as a static report. It can be easily transformed to a dynamic query to allow more certifications.

SELECT location,
	MAX( CASE WHEN cert = 'cert1' THEN certdate END) AS cert1,
	MAX( CASE WHEN cert = 'cert2' THEN certdate END) AS cert2,
	MAX( CASE WHEN cert = 'cert3' THEN certdate END) AS cert3,
	MAX( CASE WHEN cert = 'cert4' THEN certdate END) AS cert4
FROM	#table1 t1
JOIN	#table2 t2 ON t1.conid = t2.conid
GROUP BY t1.location

PS. I can't stop laughing at all the nonesense from the previous poster. Certificates with a location column? Seriously?