combing two multi-table queries

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

combing two multi-table queries

Author

Topic

cranky franky

12 Posts

Posted - 2009-09-08 : 18:07:30

Need help combining two working queries into one for a report: Below is an attempt to supply the datastructure and example output. I do NOT have full access to this DB so could not test the DDL statements, hence, I made up the data and results so hopefully they work. let me know if they are wacked and I'll try to fix them. I've attempted many versions of combining and either get some aggregate function error, or the wrong output. I'm convinced this is easy, but can't figure it out. ms sql server 08.

CREATE DATABASE test1

CREATE TABLE guests(
guest_no numeric(17, 0) NOT NULL,
parent_no numeric(17, 0) NOT NULL,
last_name varchar(20) NOT NULL,
birth_date datetime NULL)

CREATE TABLE b_sched(
start_time datetime NULL,
resource1 varchar(12) NOT NULL,
guest_no numeric(17, 0) NOT NULL)

CREATE TABLE address(
guest_no numeric(17, 0) NOT NULL,
phone char(8) NOT NULL)

GO
INSERT INTO test1.guests
(guest_no, parent_no, last_name, birth_date)
VALUES
(11,110,WIDDISON,2006-11-20 00:00:00.000
12,120,STRAZDINS,2007-01-12 00:00:00.000
13,130,SOUZA,2005-12-21 00:00:00.000 )

INSERT INTO test1.b_sched
(start_time,resource1,guest_no)
VALUES (
2008-12-20 08:30:00, daycare, 11
2008-12-25 08:30:00, daycare, 11
2008-12-25 08:30:00, daycare, 12
2008-12-19 08:30:00, daycare, 13
2008-12-25 08:30:00, daycare, 13)

INSERT INTO test1.address
(guest_no, phone)
VALUES(
110,111-1111
120,222-2222
130,333-3333)

--First Query: tests if christmas day was the first day they were in the daycare, and it worked.
SELECT s.resource1, g.last_name as LastName,
CONVERT(varchar(12), min(s.start_time), 107)AS FirstDay,
CASE WHEN (CONVERT(varchar(12), min(s.start_time), 107) = 'Dec 25, 2008')
THEN 'y'
ELSE 'n'
END AS first_day_test
FROM b_sched as s
LEFT JOIN guests AS g ON s.guest_no = g.guest_no
WHERE s.resource1 LIKE '%daycare%'AND s.start_time BETWEEN '2008-12-08 07:00:00'
AND '2008-12-25 07:00:00'
GROUP BY s.resource1, g.last_name

result
resource1, LastName, FirstDay, first_day_test
daycare, SOUZA, Dec 19 2008, n
daycare, STRAZDINS, Dec 25 2008, y
daycare, WIDDISON, Dec 20 2008, n

--Second Query: shortened version of an existing daycare report.
SELECT distinct s.resource1, g.last_name AS LastName, g.birth_date, a.phone AS Phone1
FROM b_sched AS s LEFT JOIN
guests AS g ON s.guest_no = g.guest_no LEFT JOIN
address AS a ON g.parent_no = a.guest_no
WHERE s.resource1 LIKE '%daycare%'
AND s.start_time BETWEEN '2009-08-02 07:00:00' AND '2009-08-04 15:00:00'
ORDER BY s.resource1, LastName

Result
DAYCARE,SOUZA,2005-12-21 00:00:00.000, 333-3333
DAYCARE,STRAZDINS,2007-01-12 00:00:00.000,222-2222
DAYCARE,WIDDISON,2006-11-20 00:00:00.000, 111-1111

--how do I combine these two? I need ALL of the second one with the addition of
--the first_day_test column from the first query.

I would like:
resource1, LastName, birth_date, phone, first_day_test
daycare, SOUZA, 2005-12-21, 333-3333, n
daycare, STRAZDINS, 2007-01-12, 222-2222, y
daycare, WIDDISON, 2006-11-20, 111-1111, n

"Let a complex system run long enough and something interesting is bound to happen."

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-13 : 14:19:58

will second always return 1 record per lastname?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources