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: Wednesday, June 25, 2014 11:23 AM
Points: 5, Visits: 87
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: Today @ 9:38 AM
Points: 13,094, Visits: 11,925
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: Wednesday, June 25, 2014 11:23 AM
Points: 5, Visits: 87
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: Today @ 9:38 AM
Points: 13,094, Visits: 11,925
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 @ 8:32 AM
Points: 7,119, Visits: 15,002
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


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 9:58 AM
Points: 3,349, Visits: 7,236
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.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

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: Today @ 9:31 AM
Points: 23,023, Visits: 31,543
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 Monday, August 27, 2012 3:52 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, July 20, 2014 11:55 AM
Points: 1,945, Visits: 2,860
>> I am new SQL server. I am trying to get the following data. I have 3 different tables <<

In any SQL forum, you need to post your DDL (do you know what that is?), specs and sample data. What you posted is a narrative so vague as to be useless. You also believe in a magical, generic 'id' that non-RDBMS use to replace the old record seq numbers that they had in mag tapes.

CREATE TABLE Locations
(location_code CHAR(3) NOT NULL PRIMARY KEY);

INSERT INTO Locations
VALUES ('CVG'), ('DAY'), ('CMH');

You also need to learn the ISO-8601 date formats required by ANSI

INSERT INTO Certificates
VALUES ('CVG', 'cert1', '2001-01-01'),
('CVG', 'cert2' '2008-02-03'),
('CVG', 'cert3', '2012-01-01'),
('DAY', 'cert2', '2012-02-02);

CREATE TABLE Certificates
(cert_name CHAR(10) NOT NULL PRIMARY KEY);
INSERT INTO Certificates
VALUES ('cert1'), ('cert2'), ('cert3'), ('cert4');

All I can think of for the last table is that it holds the ceremony locations. But it makes no sense and the design is flawed.

CREATE TABLE Ceremonies
(location_code CHAR(3) NOT NULL
REFERENCES Locations,
cert_name CHAR(10) NOT NULL
REFERENCES Certificates,
PRIMARY KEY (location_code, cert_name),
cert_dare DATE DEFAULT CURRENT_TIMESTAMP NOT NULL);

INSERT INTO Ceremonies
VALUES ('CVG', 'cert1', '2001-01-01'),
('CVG', 'cert2' '2008-02-03'),
('CVG', 'cert3', '2012-01-01'),
('DAY', 'cert2', '2012-02-02);

>> Now I need to retrieve all those ids [sic: the magical “id” is ambiguous] who do not all certs in Ceremonies (there is no cert4 here) from Certificates <<

SELECT C.certificate_name, L.location_code
FROM Certificates AS C
LEFT OUTER JOIN
Locations AS L
ON C.certificate_name = L.certificate_name;

This simple query gives you a valid table that ias in First Normal form; you asked for an ugly non-normal output with a repeated group.

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

Reports? We do not care. Reports are done in the presentation layers, NEVER in the query on the database side. Read any book on RDBMS and pay attention to NORMAL FORMs.

Your mindset is still in file systems and you are missing the fundamental concepts of RDBMS. This will take a few years of education and work. You can keep going to forums for kludges or you can take the time to learn to do it right. SQL is nothing like what you have done before.



Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1350631
Posted Tuesday, August 28, 2012 6:53 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 9:58 AM
Points: 3,349, Visits: 7,236
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.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

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: Today @ 9:31 AM
Points: 23,023, Visits: 31,543
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
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse