I dont think this is too hard, but I cant figure..

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

Transact-SQL (2000)

I dont think this is too hard, but I cant figure..

Author

Topic

jobe
Starting Member

10 Posts

Posted - 2003-10-28 : 13:09:24

Two Tables

Speakers
--------
Speaker_ID
Name_Last
Name_First
(etc)

Training
--------
Speaker_ID
Date
By

I am selecting the speakers info joined with another lookup table and I want add ONLY the most recient training date and who they were trained by. The Date field may contain nulls..

Much thanks in advance!!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-28 : 13:11:55

SELECT t.Date, s.Name_Last, s.Name_First
FROM Training t
INNER JOIN Speakers s ON t.Speaker_ID = s.Speaker_ID

If this isn't what you want, then please provide more information such as sample data and the expected result set.

Tara

jobe
Starting Member

10 Posts

Posted - 2003-10-28 : 13:21:21

The Training table will have lots of training records per Speaker_ID, I only want the most recent one (as per the Date record in the training table).

I understand how to join the tables, but what I need is to get just one row per speaker that contains the most recent training date and who performed the training.

Thanks!

Jobe

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-28 : 13:24:05

Why not use MAX(t.Date) then?

SELECT MAX(t.Date), s.Name_Last, s.Name_First
FROM Training t
INNER JOIN Speakers s ON t.Speaker_ID = s.Speaker_ID
GROUP BY t.Speaker_ID

Tara

jobe
Starting Member

10 Posts

Posted - 2003-10-28 : 13:41:51

Will that work for the "By" Field as well?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-28 : 13:45:11

What does the data look like in the By column? It would really help if you provided sample data and the expected result set.

My last solution isn't going to work, but I can get it to work if provided the needed information.

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-28 : 14:24:18

Well, here ya go (last effort without sample data and expected result set):



SELECT t.Date, t.[By], s.Name_Last, s.Name_First
FROM Training t
INNER JOIN Speakers s ON t.Speaker_ID = s.Speaker_ID
INNER JOIN (SELECT MAX(Date) AS Date, Speaker_ID FROM Training GROUP BY Speaker_ID) AS t1 ON t.Date = t1.Date AND s.Speaker_ID = t1.Speaker_ID

Tara

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-10-28 : 14:32:11

... or:

select Speaker_ID, ... etc ...,

(select top 1 Date from Training
where Training.Speaker_ID=Speakers.Speaker_ID order by Date desc) as LastDate,

(select top 1 By from Training
where Training.Speaker_ID=Speakers.Speaker_ID order by Date desc) as ByWhom

from Speakers

jobe
Starting Member

10 Posts

Posted - 2003-10-28 : 16:12:33

Thank you both for your responses!

Tara,

I wasnt able to get your SELECT to work, it keeps kicking it out as bad syntax, but I cant seem to find any error.

Stoad,

I was just thinking about that approach at lunch. I tried it, but it isnt working beacause I am getting multiple rows returned when there are nulls in the Date field. The TOP 1 is not working.

I will try to provide some sample data and expected results, let me work something up.

jobe
Starting Member

10 Posts

Posted - 2003-10-28 : 16:23:15

Speakers Table

Speaker_ID First_Name
--------- ----------
1 John
2 Mike
3 Jane
4 Tom
5 Mary
6 Lisa

Training Table

Speaker_ID Date By
---------- ---- --
1 1/1/2003 District Manager
1 2/30/2003 TeleLearn
2 NULL District Manager
2 NULL Regional Manager
3 5/15/2002 Workbook
3 NULL District Manager
5 1/1/2003 TeleLearn

Results Set

Speaker_ID First_Name Date By
---------- ---------- ---- --
1 John 2/30/2003 TeleLearn
2 Mike NULL "District Manager" OR "Regional Manager"
3 Jane 5/15/2002 Workbook
4 Tom NULL NULL
5 Mary 1/1/2003 TeleLearn
6 Lisa NULL NULL

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-10-28 : 16:42:18

quote:
TOP 1 is not working...

LOL!! Is it MS Access?????????

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-28 : 17:08:02

quote:
Originally posted by jobe

Tara,

I wasnt able to get your SELECT to work, it keeps kicking it out as bad syntax, but I cant seem to find any error.

It worked fine when I did it. Here is the code that I used to verify it:



SET NOCOUNT ON

CREATE TABLE Training
(
Speaker_ID INT NOT NULL,
[Date] DATETIME NOT NULL,
[By] VARCHAR(50) NOT NULL
)

CREATE TABLE Speakers
(
Speaker_ID INT NOT NULL,
Name_Last VARCHAR(50) NOT NULL,
Name_First VARCHAR(50) NOT NULL
)

INSERT INTO Speakers VALUES(1,'Duggan', 'Tara')
INSERT INTO Speakers VALUES(2,'Smith', 'John')

INSERT INTO Training VALUES(1, GETDATE(), '')
INSERT INTO Training VALUES(2, 'Jan 01 2003', '')
INSERT INTO Training VALUES(2, 'Jan 02 2003', '')
INSERT INTO Training VALUES(1, 'Feb 01 2003', '')

SELECT t.Date, t.[By], s.Name_Last, s.Name_First
FROM Training t
INNER JOIN Speakers s ON t.Speaker_ID = s.Speaker_ID
INNER JOIN (SELECT MAX(Date) AS Date, Speaker_ID FROM Training GROUP BY Speaker_ID) AS t1 ON t.Date = t1.Date AND s.Speaker_ID = t1.Speaker_ID

DROP TABLE Training
DROP TABLE Speakers

If you could take a look at what is wrong with the sample data or DDL, then I can modify it and fix it for you. But there aren't any syntax problems when running on SQL Server 2000 as it stands now.

Tara

jobe
Starting Member

10 Posts

Posted - 2003-10-28 : 17:43:50

sorry, forget to mention it is Access.

It is such a pain in my butt. I have all the other DBs here on SQL7, but this one is a monster and I just havent been able to find a free couple of weeks to convert it to SQL. The UI is a nightmare, all written by someone who is basicly a desktop tech forced to to access work.

I have a work around, so thank you for all your help both of you, and I learned a lot too. Maybe i can build up a case to convert it to SQL, but somehow I fell like I am going to be living with Access for a long long time....

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-28 : 18:53:13

Access questions belong in the Access forum. This forum and most other ones here are for SQL Server. You should repost your question in the appropriate forum.

Tara

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-10-29 : 03:15:20

Use Tara's query above, only put before each of
table aliases keyword AS. And wrap field name Date
into square brackets: [Date].

jobe
Starting Member

10 Posts

Posted - 2003-10-29 : 09:31:44

Thanks again

Sorry, I am new to these forums, I didnt notice there was an Access forum or I would have posted there.

jobe
Starting Member

10 Posts

Posted - 2003-10-30 : 17:26:22

Hey, Quick update. I spent a few hours with DTS and got this DB moved over to SQL. The production copy will continue to live on in Access, but I think I can start writing reporting to run off a copy that I will schedule to run every 24 hours or so.

I tried both queries, and they worked great. Thanks again for the help.

Sitka
Aged Yak Warrior

571 Posts

Posted - 2003-11-01 : 18:13:09

quote:
sorry, forget to mention it is Access.
It is such a pain in my butt. I have all the other DBs here on SQL7, but this one is a monster and I just havent been able to find a free couple of weeks to convert it to SQL. The UI is a nightmare, all written by someone who is basicly a desktop tech forced to to access work.

Ah the horror of it all, non concurrent data in an access to sql transition has a way of sneaking up and biting you. Real time is often expect it and users build that in their process(s). Poll your users and explain/disclaim the validity of the report result as necessary.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources