ylsvani (8/27/2012)
Thanks for the response. Here are the scripts for the aboveI 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.
A couple suggestions. First you should use the datetime datatype for dates not varchar. Secondly, you should use RI on table2 when referencing table3. What I mean by that is you should have a FK not a copy of the text description of the cert.
In general your code has the appearance of a rushed person or one who doesn't take much pride in what they do. All your column names are abbreviations and don't have any capitalization to help you read them. Typically column names are Pascal cased. So things like certdesc become CertDescription. You can never go wrong having a few characters in your column names. We have intellisense these days so it doesn't save keystrokes to make hard to figure out names.
I took the liberty of modifying Table2 with RI and proper datatypes.
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')
So to get your desired output I have a question. Is the number of certs required ALWAYS going to be the same? What you are looking at here is a crosstab query. If the number of certs will always be 4 it is a lot easier. If the number of certs can change then you are looking at a dynamic crosstab. Both techniques are described in detail in articles referenced in my signature. Take a shot at it and post back if you run into any issues.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/