Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

does not work with outer join or outer apply Expand / Collapse
Author
Message
Posted Monday, August 27, 2012 10:47 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 15, 2015 8:41 AM
Points: 7, Visits: 93
I am new sql server. I am trying to get the following data. I have 3 different tables

table1
table2
table3


table1

id location

1 CVG
2 DAY
3 CMH




table2
id cert certdate
1 cert1 01/01/2001
1 cert2 02/03/2008
1 cert3 01/01/2012
2 cert2 02/02/2012



table3
id cert
1 cert1
2 cert2
3 cert3
4 cert4


Now I need to retrieve all those ids who do not all certs in table2 ( there is no cert4 here) from table3 as follows

id location cert1date cert2date cert3date cert4date
1 CVG 01/01/2001 02/03/2008 01/01/2012 NULL
2 DAY NULL 02/02/2012 NULL NULL

This indicates that id 1 has to should still have cert4date and id 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.
Post #1350484
Posted Monday, August 27, 2012 10:54 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:47 PM
Points: 14,377, Visits: 14,123
Hi and welcome to SSC! Your post is so vague nobody can do much to help. We would need to see ddl (create table scripts), sample data (insert statements) and desired output. Take a look at the first link my signature for best practices when posting questions.

_______________________________________________________________

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1350489
Posted Monday, August 27, 2012 11:20 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 15, 2015 8:41 AM
Points: 7, Visits: 93
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.
Post #1350501
Posted Monday, August 27, 2012 11:45 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:47 PM
Points: 14,377, Visits: 14,123
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.


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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1350505
Posted Monday, August 27, 2012 11:51 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:44 AM
Points: 7,352, Visits: 16,706
Here's a static version of your "report" (this would be the data behind the report actually)

select conid,location,[cert1date],[cert2date],[cert3date],[cert4date]
from
(select #table1.conid, location, certdesc+'date' certname,certdate
from #table1
cross join #table3
left outer join #table2 on #table1.conid=#table2.conid and #table3.certdesc=#table2.[cert]
where exists (select null from #table2 where conid=#table1.conid)
) p
pivot
(max(certdate) for certname in ([cert1date],[cert2date],[cert3date],[cert4date])) pvt
order by conID



----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Post #1350509
Posted Monday, August 27, 2012 1:14 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 11:14 AM
Points: 5,061, Visits: 11,627
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.



Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


Forum Etiquette: How to post data/code on a forum to get the best help
Post #1350542
Posted Monday, August 27, 2012 1:53 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 10:23 PM
Points: 21,921, Visits: 34,685
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.


I don't know, I managed to quickly come up with the same query Matt did, and I am trying to figure out how you can do it with only 2 of the 3 tables.

Started with this to get the data needed for the pivot:


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





Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1350563
Posted Tuesday, August 28, 2012 6:53 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 11:14 AM
Points: 5,061, Visits: 11,627
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?



Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


Forum Etiquette: How to post data/code on a forum to get the best help
Post #1350895
Posted Tuesday, August 28, 2012 8:04 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 10:23 PM
Points: 21,921, Visits: 34,685
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?


Okay, I see now that I picked up the saw. If you make the change to the tables that Sean suggested, then you need all three tables. I'm glad I got a good nights sleep last night.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1350966
Posted Tuesday, August 28, 2012 8:45 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 11:14 AM
Points: 5,061, Visits: 11,627
OK, so here's the dynamic query. Simple and effective. As you see, the real query is just hitting 2 tables, the Certs table is just used to create the query.
But I'm not sure, is there a better solution?

--Generating Sample Data
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,certid int,certdate datetime)
insert into #table2 values(1,1,'01/01/2001')
insert into #table2 values(1,2,'02/03/2008')
insert into #table2 values(1,3,'01/01/2012')
insert into #table2 values(2,2,'02/02/2012')
go
create table #table3(certid int,certdesc varchar(5), PRIMARY KEY (certid))
insert into #table3 values(4,'cert4')
insert into #table3 values(1,'cert1')
insert into #table3 values(3,'cert3')
insert into #table3 values(2,'cert2')
go

--Building the dynamic query
DECLARE @SQL1 varchar(8000),
@SQL2 varchar(8000),
@SQL3 varchar(8000)
SET @SQL1 = 'SELECT location, ' + CHAR(10)

SELECT @SQL2 = COALESCE(@SQL2,'')
+ ' MAX( CASE WHEN certid = ' + CAST( certid AS varchar(10))
+ ' THEN certdate END) AS [' + certdesc + '],' + CHAR(10)
FROM #table3
ORDER BY certid

SET @SQL2 = LEFT( @SQL2, LEN(@SQL2) - 2) + CHAR(10)

SET @SQL3 = 'FROM #table1 t1
JOIN #table2 t2 ON t1.conid = t2.conid
GROUP BY t1.location'

--Test
PRINT @SQL1 + @SQL2 + @SQL3
--Execution
EXEC( @SQL1 + @SQL2 + @SQL3)

--Drop sample tables
DROP TABLE #table1
DROP TABLE #table2
DROP TABLE #table3




Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


Forum Etiquette: How to post data/code on a forum to get the best help
Post #1351000
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse