September 7, 2001 at 4:10 pm
Hello,
I have 2 following tables:
CREATE TABLE MAIN_TABLE
(record_id int PRIMARY KEY,sol_number varchar(7) NOT NULL)
INSERT INTO MAIN_TABLE VALUES (1, 681)
INSERT INTO MAIN_TABLE VALUES (2, 709)
INSERT INTO MAIN_TABLE VALUES (3, 715)
INSERT INTO MAIN_TABLE VALUES (4, 716)
CREATE TABLE DROPS
(drop_id int PRIMARY KEY ,
rec_id int NOT NULL REFERENCES MAIN_TABLE(record_id),
drop_name varchar(5) NULL,
drop_date smalldatetime NULL)
INSERT INTO DROPS VALUES (2000,1,'A','10/10/97')
INSERT INTO DROPS VALUES (3001,1,NULL,'7/31/97')
INSERT INTO DROPS VALUES (1999,1,'B','8/3/99')
INSERT INTO DROPS VALUES (1499,2,NULL,NULL)
INSERT INTO DROPS VALUES (1500,2,'A','1/1/2001')
INSERT INTO DROPS VALUES (2005,2,'B','1/1/2001')
INSERT INTO DROPS VALUES (1200,3,'A',NULL)
INSERT INTO DROPS VALUES (2050,3,'B','7/1/97')
INSERT INTO DROPS VALUES (2007,3,'B','7/31/97')
INSERT INTO DROPS VALUES (2008,4,NULL,NULL)
I would like to create the view which returns record_id and minimum drop_date associated with that record_id. Do not include record if drop_date=NULL.
The result set should look like this:
record_id sol_number drop_id min_drop_date
1 681 3001 7/31/97
2 709 1500 1/1/01
3 715 2050 7/1/97
I need to do it in single query. I would really appreciate any help.
September 7, 2001 at 7:44 pm
Could you post what you've come up with so far?
Andy
September 10, 2001 at 7:17 am
Thanks for the replay. The result of that query is
1 681 3001 1997-07-31
2 709 1500 2001-01-01
2 709 2005 2001-01-01
3 715 2050 1997-07-01
So, we get dupes.
September 10, 2001 at 9:06 am
I got it. Here is the answer:
SELECT
Y.record_id,
Y.sol_number,
Y.drop_id,
Y.drop_date FROM
(
SELECT
a.record_id, MIN(a.drop_id) as min_drop_id FROM
(SELECT m.record_id, d.drop_id
FROM dbo.MAIN_TABLE m
INNER JOIN
(SELECT rec_id, MIN(drop_date) AS drop_date FROM DROPS
WHERE Drop_Date IS NOT NULL
GROUP BY rec_id) DROPS_Filter
ON m.record_id = DROPS_Filter.rec_id INNER JOIN
dbo.DROPS d ON DROPS_Filter.drop_date = d.drop_date AND DROPS_Filter.rec_id = d.rec_id) a
GROUP BY a.record_id
) X
INNER JOIN
(
SELECT dbo.MAIN_TABLE.record_id, dbo.MAIN_TABLE.sol_number, dbo.DROPS.drop_id, dbo.DROPS.drop_date
FROM dbo.MAIN_TABLE INNER JOIN
(SELECT rec_id, MIN(drop_date) AS drop_date
FROM DROPS
WHERE Drop_Date IS NOT NULL
GROUP BY rec_id) DROPS_Filter ON dbo.MAIN_TABLE.record_id = DROPS_Filter.rec_id INNER JOIN
dbo.DROPS ON DROPS_Filter.drop_date = dbo.DROPS.drop_date AND DROPS_Filter.rec_id = dbo.DROPS.rec_id
) Y
ON X.record_id=Y.record_id and X.min_drop_id=Y.drop_id
September 10, 2001 at 4:37 pm
How about this instead?
select * from main_table a
inner join (select rec_id, min(drop_date) as DropDate from drops where drop_date is not null group by rec_id) b
on a.record_id=b.rec_id
Andy
September 11, 2001 at 8:10 am
Andy, it works just fine, but you did not include drop_id field in the query. That's where the problems begin...
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply