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
September 10, 2001 at 7:17 am
Thanks for the replay. The result of that query is
168130011997-07-31
270915002001-01-01
270920052001-01-01
371520501997-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 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy