SQL Single Query for Multiple table

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

General SQL Server Forums

New to SQL Server Programming

SQL Single Query for Multiple table

Author

Topic

dhinasql
Posting Yak Master

195 Posts

Posted - 2008-04-15 : 06:09:26

Hi friends,
I have three table named as Eventsmgmt,blogmgmt,forummgmt..
Each table contain the common column named as CreatedDateTime..

I want to get the most recent CreationDateTime from these three table in single query..

Plzz help me its urgent

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-15 : 06:13:15

SELECT MAX(theDate) FROM
(
SELECT MAX(CreationDateTime) AS theDate FROM Eventsmgmt
UNION ALL
SELECT MAX(CreationDateTime) FROM blogmgmt
UNION ALL
SELECT MAX(CreationDateTime) FROM forummgmt
) AS d

E 12°55'05.25"
N 56°04'39.16"

pravin14u
Posting Yak Master

246 Posts

Posted - 2008-04-15 : 06:34:08

Peso - will the following be better by any means?

SELECT MAX(theDate) FROM
(
SELECT CreationDateTime AS theDate FROM Eventsmgmt
UNION ALL
SELECT CreationDateTime FROM blogmgmt
UNION ALL
SELECT CreationDateTime FROM forummgmt
) AS d

Prakash.P
The secret to creativity is knowing how to hide your sources!

dhinasql
Posting Yak Master

195 Posts

Posted - 2008-04-15 : 06:41:31

Thanks Peso

Now i can get the recent date, i want to display the ID and name of the table for that corresponding date.

My expected result is ,

The most recent content is added in

for example if its event
Eventsmgmt EventsID and DateTime

if it is Forum
Forummgmt ForumId and DateTime

Please its urgent

Thanks in Advance

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-15 : 06:46:01

Why didn't you tell us from the beginning what you REALLY want?
Are you shy?

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-15 : 06:49:25

Next time, please post directlt what you want.
We volunteer here to help you on our spare time for free.
Instead of helping you twice with same problem because you didn't tell us what you really wanted the first time, we could spend this extra time to assist other people.

SELECT TOP 1	CreationDateTime,
		TableName,
		RecordID
FROM		(
			SELECT TOP 1	CreationDateTime,
					'EventsMgmt' AS TableName,
					EventID AS RecordID
			FROM		EventsMgmt
			ORDER BY	CreationDateTime DESC

			UNION ALL

			SELECT TOP 1	CreationDateTime,
					'BlogMgmt',
					BlogID
			FROM		BlogMgmt
			ORDER BY	CreationDateTime DESC

			UNION ALL

			SELECT TOP 1	CreationDateTime,
					'ForumMgmt',
					ForumID
			FROM		ForumMgmt
			ORDER BY	CreationDateTime DESC
		) AS d
ORDER BY	CreationDateTime DESC

E 12°55'05.25"
N 56°04'39.16"

dhinasql
Posting Yak Master

195 Posts

Posted - 2008-04-15 : 07:12:46

Ok Pesco, Im new for this, and the required result is got changed at this time only from my higher ppls..
Next time i dnt repeat this.... Thanks for your help

dhinasql
Posting Yak Master

195 Posts

Posted - 2008-04-15 : 07:44:52

Hi Peso,

I dnt shy , i got some mental pressure, so first i dnt tell what exactly i need..

I worked out ur query, But its fetching someother date.

Example

I will display some data from each table

EventsMgmt

EventId - CreationDate
-----------------------
1 2008-04-01 01:09:14.653
2 2008-04-01 01:13:56.310
3 2008-04-01 23:11:17.217

BlogMgmt
========

BlogId - CreationDate

3 2007-03-04 00:00:00.000
4 2007-03-05 00:00:00.000
5 2009-04-06 00:00:00.000 <--- My Expected Result

ForumMgmt
=========
10 2008-03-04 19:31:26.437
11 2008-03-12 12:42:20.043
12 2008-03-12 12:42:43.293

Please modify the code, Im not getting the expected result

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-15 : 08:11:37

[code]-- Prepare sample data
DECLARE @EventsMgmt TABLE (EventID INT, CreationDate DATETIME)

INSERT @EventsMgmt
SELECT 1, '2008-04-01 01:09:14.653' UNION ALL
SELECT 2, '2008-04-01 01:13:56.310' UNION ALL
SELECT 3, '2008-04-01 23:11:17.217'

DECLARE @BlogMgmt TABLE (BlogID INT, CreationDate DATETIME)

INSERT @BlogMgmt
SELECT 3, '2007-03-04 00:00:00.000' UNION ALL
SELECT 4, '2007-03-05 00:00:00.000' UNION ALL
SELECT 5, '2009-04-06 00:00:00.000' -- My Expected Result

DECLARE @ForumMgmt TABLE (ForumID INT, CreationDate DATETIME)

INSERT @ForumMgmt
SELECT 10, '2008-03-04 19:31:26.437' UNION ALL
SELECT 11, '2008-03-12 12:42:20.043' UNION ALL
SELECT 12, '2008-03-12 12:42:43.293'

-- Query as posted 04/15/2008 : 06:49:25
SELECT TOP 1 CreationDate,
TableName,
RecordID
FROM (
SELECT TOP 1 CreationDate,
'EventsMgmt' AS TableName,
EventID AS RecordID
FROM @EventsMgmt
ORDER BY CreationDate DESC

UNION ALL

SELECT TOP 1 CreationDate,
'BlogMgmt',
BlogID
FROM @BlogMgmt
ORDER BY CreationDate DESC

UNION ALL

SELECT TOP 1 CreationDate,
'ForumMgmt',
ForumID
FROM @ForumMgmt
ORDER BY CreationDate DESC
) AS d
ORDER BY CreationDate DESC[/code]The output from above sample data and suggested code is[code]CreationDate TableName RecordID
2009-04-06 00:00:00.000 BlogMgmt 5[/code]

E 12°55'05.25"
N 56°04'39.16"

dhinasql
Posting Yak Master

195 Posts

Posted - 2008-04-15 : 08:24:26

Hi Peso,

Sorry for distrubing you again.

Im getting the following result for your code

CreationDate TableName RecordId
2008-04-01 01:09:14.653 EventsMgmt 1

But the expected result is
2009-04-06 00:00:00.000 BlogMgmt 5

Please help what im wrong...

Sorry for the distrubance... Please help me to get the right result

Thanks in Advance

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-15 : 08:36:26

Before you next time yell that the code do not work, please COPY & PASTE complete code AS IS and run in your query window.
Do not substitute anything! Just run the code...

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-15 : 08:37:32

Are you REALLY using MICROSOFT SQL Server?

E 12°55'05.25"
N 56°04'39.16"

dhinasql
Posting Yak Master

195 Posts

Posted - 2008-04-15 : 08:49:27

Hi Peso,

Im using SQL Server 2000, I dnt made any change in your code, I just copy and paste your code in Query analyser, But its not showing the expected result.

DECLARE @EventsMgmt TABLE (EventID INT, CreationDate DATETIME)

INSERT @EventsMgmt
SELECT 1, '2008-04-01 01:09:14.653' UNION ALL
SELECT 2, '2008-04-01 01:13:56.310' UNION ALL
SELECT 3, '2008-04-01 23:11:17.217'

DECLARE @BlogMgmt TABLE (BlogID INT, CreationDate DATETIME)

INSERT @BlogMgmt
SELECT 3, '2007-03-04 00:00:00.000' UNION ALL
SELECT 4, '2007-03-05 00:00:00.000' UNION ALL
SELECT 5, '2009-04-06 00:00:00.000' -- My Expected Result

DECLARE @ForumMgmt TABLE (ForumID INT, CreationDate DATETIME)

INSERT @ForumMgmt
SELECT 10, '2008-03-04 19:31:26.437' UNION ALL
SELECT 11, '2008-03-12 12:42:20.043' UNION ALL
SELECT 12, '2008-03-12 12:42:43.293'

SELECT TOP 1 CreationDate,
TableName,
RecordID
FROM (
SELECT TOP 1 CreationDate,
'EventsMgmt' AS TableName,
EventID AS RecordID
FROM @EventsMgmt
ORDER BY CreationDate DESC

UNION ALL

SELECT TOP 1 CreationDate,
'BlogMgmt',
BlogID
FROM @BlogMgmt
ORDER BY CreationDate DESC

UNION ALL

SELECT TOP 1 CreationDate,
'ForumMgmt',
ForumID
FROM @ForumMgmt
ORDER BY CreationDate DESC
) AS d
ORDER BY CreationDate DESC

OutPut
======
CreationDate TableName RecordId
2008-04-01 01:09:14.653 EventsMgmt 1

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-15 : 08:51:30

I get this result every time

CreationDate	TableName	RecordID
2009-04-06	BlogMgmt	5

Anyone else getting same strange result as OP?

E 12°55'05.25"
N 56°04'39.16"

dhinasql
Posting Yak Master

195 Posts

Posted - 2008-04-15 : 08:58:02

Hi Peso,

But everytime im getting the following result, i have check this query in another collegue system also, there also im getting this result

CreationDate TableName RecordId
2008-04-01 01:09:14.653 EventsMgmt 1

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-04-15 : 09:01:49

quote:
Originally posted by Peso

I get this result every time
CreationDate TableName RecordID
2009-04-06 BlogMgmt 5
Anyone else getting same strange result as OP?

E 12°55'05.25"
N 56°04'39.16"

Nope, I get same as you everytime

Em

vidhya
Posting Yak Master

108 Posts

Posted - 2008-04-15 : 09:11:08

hi friends,

i m getting the following result.

CreationDate TableName RecordId
2008-04-01 01:09:14.653 EventsMgmt 1

dhinasql
Posting Yak Master

195 Posts

Posted - 2008-04-15 : 09:14:45

Hi peso and elance,

But why im getting the wrong result..

Im using the same code what peso send to me....

Whats the wrong ? please help me

dhinasql
Posting Yak Master

195 Posts

Posted - 2008-04-15 : 09:35:24

Hi Anyone please check the code, and post the Output, Bcoz two person are geting the different result for same Code...

Please its Urgent

Use the following code

DECLARE @EventsMgmt TABLE (EventID INT, CreationDate DATETIME)

INSERT @EventsMgmt
SELECT 1, '2008-04-01 01:09:14.653' UNION ALL
SELECT 2, '2008-04-01 01:13:56.310' UNION ALL
SELECT 3, '2008-04-01 23:11:17.217'

DECLARE @BlogMgmt TABLE (BlogID INT, CreationDate DATETIME)

INSERT @BlogMgmt
SELECT 3, '2007-03-04 00:00:00.000' UNION ALL
SELECT 4, '2007-03-05 00:00:00.000' UNION ALL
SELECT 5, '2009-04-06 00:00:00.000' -- My Expected Result

DECLARE @ForumMgmt TABLE (ForumID INT, CreationDate DATETIME)

INSERT @ForumMgmt
SELECT 10, '2008-03-04 19:31:26.437' UNION ALL
SELECT 11, '2008-03-12 12:42:20.043' UNION ALL
SELECT 12, '2008-03-12 12:42:43.293'

SELECT TOP 1 CreationDate,
TableName,
RecordID
FROM (
SELECT TOP 1 CreationDate,
'EventsMgmt' AS TableName,
EventID AS RecordID
FROM @EventsMgmt
ORDER BY CreationDate DESC

UNION ALL

SELECT TOP 1 CreationDate,
'BlogMgmt',
BlogID
FROM @BlogMgmt
ORDER BY CreationDate DESC

UNION ALL

SELECT TOP 1 CreationDate,
'ForumMgmt',
ForumID
FROM @ForumMgmt
ORDER BY CreationDate DESC
) AS d
ORDER BY CreationDate DESC

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-15 : 09:36:53

quote:
Originally posted by dhinasql

Hi peso and elance,

But why im getting the wrong result..

Im using the same code what peso send to me....

Whats the wrong ? please help me

It is becuase you are using SQL Server 2000 and Peso is using SQL Server 2005

Madhivanan

Failing to plan is Planning to fail

dhinasql
Posting Yak Master

195 Posts

Posted - 2008-04-15 : 09:44:46

Yes Madhivanan, I have checked the same code in SQL Server 2005. Now its showing the different result...

Im in damn confuse, the query is same but why we are getting the two different results in SQl Server 2000 and SQL Server 2005 ?

How can i solve this is 2000?

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources