April 21, 2008 at 6:24 pm
Hi,
here is my task
I need to fill form in vb6 that will show all rows from table_1 and only rows that date1 date2 will be gray out
I have 2 tables and I need to get result from SQL query
something like this:
SELECT table_1.id,table_1.Description, table_2.date1,table_2.date2
FROM table_1
LEFT OUTER JOIN table_2 ON table_1.id=TABLE_2.id
the result I need is all rows from table_1
and only date1 getdate()
table 1
--------------------------------
GO
/****** Object: Table [dbo].[Table_1] Script Date: 04/21/2008 16:23:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Table_1](
[id] [int] IDENTITY(1,1) NOT NULL,
[Description] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
--------------------------------------------
id Description
----------- --------------------------------------------------
1 a
2 b
3 c
4 d
5 e
6 f
and table2
GO
/****** Object: Table [dbo].[Table_2] Script Date: 04/21/2008 16:24:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Table_2](
[id] [int] NULL,
[date1] [datetime] NULL,
[date2] [datetime] NULL
) ON [PRIMARY]
id date1 date2
----------- ----------------------- -----------------------
1 2001-01-01 00:00:00.000 2001-01-02 00:00:00.000
1 2007-01-07 00:00:00.000 2007-02-07 00:00:00.000
1 2008-05-05 00:00:00.000 2008-05-08 00:00:00.000
1 2008-04-20 00:00:00.000 2008-04-25 00:00:00.000
2 2008-04-20 00:00:00.000 2008-04-28 00:00:00.000
April 21, 2008 at 6:49 pm
I'm not sure I'm understanding what you need, but lets see if this works!
SELECT table_1.id,table_1.Description, table_2.date1,table_2.date2
FROM table_1
LEFT OUTER JOIN (SELECT table_2.id, MAX(table_2.date1) AS MaxDate1 FROM table_2 GROUP BY table_2.id) AS xx
ON table_1.id=xx.id
LEFT OUTER JOIN table_2 ON table_1.id = table_2.id AND xx.MaxDate1 = table_2.date1
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply