Help with SQL query

Please bare with me in trying to get my point across, I'm new to SQL and
would appreciate the help so much.
I have two table Table1 and Table2. There is a one to many relationship
between Table1 and Table2. Table2 has a list of widgets, I need to create a
query that would show the records from Table1 along with it's related
records and all non related records from Table2. So if I have 5 widgets and
only two have related records in Table1 results should be
Table1ID(2) fkTable2 Table2(widgetname1)
Table1ID(2) fkTable2 Table2(widgetname2)
null null Table2(widgetname3)
null null Table2(widgetname4)
null null Table2(widgetname5)
When I filter the query on another Table1ID, I would need the same sort of
result for each Table1ID.try this
SELECT TABLE1.COL1,TABLE1.COL2,TABLE2.COL1,TABLE2.COL2 FROM TABLE1 RIGHT
OUTER JOIN TABLE2 ON TABLE1.COL1 = TABLE2.COL1
--
Regards
R.D
--Knowledge gets doubled when shared
"Tim Harvey" wrote:

> Please bare with me in trying to get my point across, I'm new to SQL and
> would appreciate the help so much.
> I have two table Table1 and Table2. There is a one to many relationship
> between Table1 and Table2. Table2 has a list of widgets, I need to create
a
> query that would show the records from Table1 along with it's related
> records and all non related records from Table2. So if I have 5 widgets an
d
> only two have related records in Table1 results should be
> Table1ID(2) fkTable2 Table2(widgetname1)
> Table1ID(2) fkTable2 Table2(widgetname2)
> null null Table2(widgetname3)
> null null Table2(widgetname4)
> null null Table2(widgetname5)
> When I filter the query on another Table1ID, I would need the same sort of
> result for each Table1ID.
>
>
>

Help with sequel statement please. Thanks.

Sorry for dup. Please ignore the previous one. Thanks.
Hi all,
How can I return the results below. Any help would greatly appreciate. The
business rule is show below.
IF OBJECT_ID('Tempdb.dbo.#Policy_nb', 'u') IS NOT NULL
DROP TABLE #Policy_nb
GO
CREATE TABLE #Policy_nb
(
Policy_Id INT NULL,
CurrAgent_id INT NULL,
UploadTp_id INT NULL
)
GO
INSERT #Policy_nb (Policy_Id, CurrAgent_id, UploadTp_id) VALUES (382099,
4894, 3)
INSERT #Policy_nb (Policy_Id, CurrAgent_id, UploadTp_id) VALUES (374943,
614, 3)
INSERT #Policy_nb (Policy_Id, CurrAgent_id, UploadTp_id) VALUES (376279,
4710, 2)
GO
IF OBJECT_ID('Tempdb.dbo.#HuonUpload_nb', 'u') IS NOT NULL
DROP TABLE #HuonUpload_nb
GO
CREATE TABLE #HuonUpload_nb
(
Policy_id INT NULL,
UploadTp_Id INT NULL,
UploadStatus_dt DATETIME NULL
)
GO
INSERT #HuonUpload_nb VALUES (382099, 3, '10/03/2005 11:27AM')
INSERT #HuonUpload_nb VALUES (382099, 2, '10/03/2005 11:25AM')
INSERT #HuonUpload_nb VALUES (382099, 1, '10/03/2005 11:21AM')
INSERT #HuonUpload_nb VALUES (382099, 4, '09/30/2005 9:24AM')
INSERT #HuonUpload_nb VALUES (382099, 1, '09/30/2005 9:22AM')
INSERT #HuonUpload_nb VALUES (382099, 2, '09/30/2005 9:21AM')
INSERT #HuonUpload_nb VALUES (382099, 2, '09/30/2005 9:12AM')
INSERT #HuonUpload_nb VALUES (382099, 1, '09/29/2005 11:36PM')
INSERT #HuonUpload_nb VALUES (382099, 4, '09/29/2005 09:45AM')
INSERT #HuonUpload_nb VALUES (382099, 2, '09/28/2005 01:18PM')
INSERT #HuonUpload_nb VALUES (374943, 3, '10/03/2005 11:26AM')
INSERT #HuonUpload_nb VALUES (374943, 2, '10/03/2005 10:20AM')
INSERT #HuonUpload_nb VALUES (374943, 1, '10/03/2005 10:15AM')
INSERT #HuonUpload_nb VALUES (376279, 1, '09/13/2005 2:19PM')
INSERT #HuonUpload_nb VALUES (376279, 2, '09/13/2005 2:36PM')
go
SELECT *
FROM #Policy_nb
GO
Policy_Id CurrAgent_id UploadTp_id
-- -- --
382099 4894 3
374943 614 3
376279 4710 2
SELECT *
FROM #HuonUpload_nb
GO
Policy_id UploadTp_Id UploadStatus_dt
-- -- --
382099 3 2005-10-03 11:27:00.000
382099 2 2005-10-03 11:25:00.000
382099 1 2005-10-03 11:21:00.000
382099 4 2005-09-30 09:24:00.000
382099 1 2005-09-30 09:22:00.000
382099 2 2005-09-30 09:21:00.000
382099 2 2005-09-30 09:12:00.000
382099 1 2005-09-29 23:36:00.000
382099 4 2005-09-29 09:45:00.000
382099 2 2005-09-28 13:18:00.000
374943 3 2005-10-03 11:26:00.000
374943 2 2005-10-03 10:20:00.000
374943 1 2005-10-03 10:15:00.000
376279 1 2005-09-13 14:19:00.000
376279 2 2005-09-13 14:36:00.000
-- Rules: Return only these rows which has UploadTp_id = 1, 2 and 3.
--Testing... Not working...
SELECT a.CurrAgent_id,
b.Policy_id,
b.UploadTp_Id,
b.UploadStatus_dt
FROM #Policy_nb AS a
JOIN #HuonUpload_nb AS b
ON a.Policy_id = b.Policy_id
JOIN (SELECT TOP 100 PERCENT Policy_id, UploadTp_id,
MAX(UploadStatus_dt) AS 'UploadStatus_dt'
FROM #HuonUpload_nb
WHERE UploadTp_id IN (1, 2, 3)
GROUP BY Policy_id, UploadTp_id
ORDER BY Policy_id ASC, UploadStatus_dt DESC) AS c
ON c.Policy_id = b.Policy_id
AND c.UploadTp_id = b.UploadTp_id
AND c.UploadStatus_dt = b.UploadStatus_dt
ORDER BY b.Policy_id DESC, b.UploadStatus_dt DESC
GO
--Result want:
CurrAgent_id Policy_id UploadTp_Id UploadStatus_dt
-- -- -- --
4894 382099 3 2005-10-03 11:27:00.000
4894 382099 2 2005-10-03 11:25:00.000
4894 382099 1 2005-10-03 11:21:00.000
614 374943 3 2005-10-03 11:26:00.000
614 374943 2 2005-10-03 10:20:00.000
614 374943 1 2005-10-03 10:15:00.000There is no duplicate (unless you mean your post from 8/29).
Please consider using a newsreader, which doesn't have as many
synchronization issues as the web-based interfaces.
http://www.aspfaq.com/5007|||Try this,
SELECT p.CurrAgent_id, h.Policy_id, h.UploadTp_Id, max(h.UploadStatus_dt) AS
UploadStatus_dt
FROM HuonUpload_nb h inner join Policy_nb p on h.policy_id = p.policy_id
WHERE h.UploadTp_Id <=3 AND p.CurrAgent_id IN
(SELECT currAgent_id
FROM (SELECT PN.currAgent_id
FROM HuonUpload_nb NB INNER JOIN POLICY_NB PN ON NB.policy_ID =
PN.policy_ID
WHERE (nb.UploadTp_Id <=3)
GROUP BY pn.currAgent_id, nb.Policy_id, nb.UploadTp_id) AS X
GROUP BY currAgent_id
HAVING count(currAgent_id) = 3)
GROUP BY p.CurrAgent_id, h.Policy_id, h.UploadTp_Id
ORDER BY h.Policy_id DESC, h.UploadTP_Id DESC
Note: # were removed
Regards,
David
"Lam Nguyen" wrote:

> Sorry for dup. Please ignore the previous one. Thanks.
> Hi all,
> How can I return the results below. Any help would greatly appreciate. T
he
> business rule is show below.
>
> IF OBJECT_ID('Tempdb.dbo.#Policy_nb', 'u') IS NOT NULL
> DROP TABLE #Policy_nb
> GO
> CREATE TABLE #Policy_nb
> (
> Policy_Id INT NULL,
> CurrAgent_id INT NULL,
> UploadTp_id INT NULL
> )
> GO
> INSERT #Policy_nb (Policy_Id, CurrAgent_id, UploadTp_id) VALUES (382099,
> 4894, 3)
> INSERT #Policy_nb (Policy_Id, CurrAgent_id, UploadTp_id) VALUES (374943,
> 614, 3)
> INSERT #Policy_nb (Policy_Id, CurrAgent_id, UploadTp_id) VALUES (376279,
> 4710, 2)
> GO
> IF OBJECT_ID('Tempdb.dbo.#HuonUpload_nb', 'u') IS NOT NULL
> DROP TABLE #HuonUpload_nb
> GO
> CREATE TABLE #HuonUpload_nb
> (
> Policy_id INT NULL,
> UploadTp_Id INT NULL,
> UploadStatus_dt DATETIME NULL
> )
> GO
> INSERT #HuonUpload_nb VALUES (382099, 3, '10/03/2005 11:27AM')
> INSERT #HuonUpload_nb VALUES (382099, 2, '10/03/2005 11:25AM')
> INSERT #HuonUpload_nb VALUES (382099, 1, '10/03/2005 11:21AM')
> INSERT #HuonUpload_nb VALUES (382099, 4, '09/30/2005 9:24AM')
> INSERT #HuonUpload_nb VALUES (382099, 1, '09/30/2005 9:22AM')
> INSERT #HuonUpload_nb VALUES (382099, 2, '09/30/2005 9:21AM')
> INSERT #HuonUpload_nb VALUES (382099, 2, '09/30/2005 9:12AM')
> INSERT #HuonUpload_nb VALUES (382099, 1, '09/29/2005 11:36PM')
> INSERT #HuonUpload_nb VALUES (382099, 4, '09/29/2005 09:45AM')
> INSERT #HuonUpload_nb VALUES (382099, 2, '09/28/2005 01:18PM')
> INSERT #HuonUpload_nb VALUES (374943, 3, '10/03/2005 11:26AM')
> INSERT #HuonUpload_nb VALUES (374943, 2, '10/03/2005 10:20AM')
> INSERT #HuonUpload_nb VALUES (374943, 1, '10/03/2005 10:15AM')
> INSERT #HuonUpload_nb VALUES (376279, 1, '09/13/2005 2:19PM')
> INSERT #HuonUpload_nb VALUES (376279, 2, '09/13/2005 2:36PM')
> go
> SELECT *
> FROM #Policy_nb
> GO
> Policy_Id CurrAgent_id UploadTp_id
> -- -- --
> 382099 4894 3
> 374943 614 3
> 376279 4710 2
> SELECT *
> FROM #HuonUpload_nb
> GO
> Policy_id UploadTp_Id UploadStatus_dt
> -- -- --
> 382099 3 2005-10-03 11:27:00.000
> 382099 2 2005-10-03 11:25:00.000
> 382099 1 2005-10-03 11:21:00.000
> 382099 4 2005-09-30 09:24:00.000
> 382099 1 2005-09-30 09:22:00.000
> 382099 2 2005-09-30 09:21:00.000
> 382099 2 2005-09-30 09:12:00.000
> 382099 1 2005-09-29 23:36:00.000
> 382099 4 2005-09-29 09:45:00.000
> 382099 2 2005-09-28 13:18:00.000
> 374943 3 2005-10-03 11:26:00.000
> 374943 2 2005-10-03 10:20:00.000
> 374943 1 2005-10-03 10:15:00.000
> 376279 1 2005-09-13 14:19:00.000
> 376279 2 2005-09-13 14:36:00.000
>
> -- Rules: Return only these rows which has UploadTp_id = 1, 2 and 3.
> --Testing... Not working...
> SELECT a.CurrAgent_id,
> b.Policy_id,
> b.UploadTp_Id,
> b.UploadStatus_dt
> FROM #Policy_nb AS a
> JOIN #HuonUpload_nb AS b
> ON a.Policy_id = b.Policy_id
> JOIN (SELECT TOP 100 PERCENT Policy_id, UploadTp_id,
> MAX(UploadStatus_dt) AS 'UploadStatus_dt'
> FROM #HuonUpload_nb
> WHERE UploadTp_id IN (1, 2, 3)
> GROUP BY Policy_id, UploadTp_id
> ORDER BY Policy_id ASC, UploadStatus_dt DESC) AS c
> ON c.Policy_id = b.Policy_id
> AND c.UploadTp_id = b.UploadTp_id
> AND c.UploadStatus_dt = b.UploadStatus_dt
> ORDER BY b.Policy_id DESC, b.UploadStatus_dt DESC
> GO
> --Result want:
> CurrAgent_id Policy_id UploadTp_Id UploadStatus_dt
> -- -- -- --
> 4894 382099 3 2005-10-03 11:27:00.000
> 4894 382099 2 2005-10-03 11:25:00.000
> 4894 382099 1 2005-10-03 11:21:00.000
> 614 374943 3 2005-10-03 11:26:00.000
> 614 374943 2 2005-10-03 10:20:00.000
> 614 374943 1 2005-10-03 10:15:00.000
>

Help With Query

I am new at this so your patience and help is appreciate in advance. I have
5
tables that are updated with call center stats. The common field across all
tables is the associate. Some of the tables are updated daily while others
may be updated once a w.
I want to sum all the stats by associate from these tables and using weighte
d
formulas report on their performance. I need to be able to provide the user
with reporting for a date range that will be entered through a Web page
(begin date / end date).
How do I go about doing this? Link all the tables by associate, do the sums
and then the formulas within the same query or run separate queries for each
table and then link results via associate? My main problem is being able to
pass the date range. I've done this in ACCESS with a form as the central
input for the date range, but don't know how in MS-SQL.
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200606/1If you post DDL we can give more specific advice. Please see this link for
details...
http://www.aspfaq.com/etiquette.asp?id=5006
As for the general process for doing this...
Start by joining the necessary tables together and making sure you are
retrieving the rows you want.
The add in the date criteria. Again, make sure these are the rows you want.
Then add in the summaries. Make sure these are the results you want.
"Chamark via webservertalk.com" <u21870@.uwe> wrote in message
news:6202cfc0a9dd4@.uwe...
> I am new at this so your patience and help is appreciate in advance. I
have 5
> tables that are updated with call center stats. The common field across
all
> tables is the associate. Some of the tables are updated daily while others
> may be updated once a w.
> I want to sum all the stats by associate from these tables and using
weighted
> formulas report on their performance. I need to be able to provide the
user
> with reporting for a date range that will be entered through a Web page
> (begin date / end date).
> How do I go about doing this? Link all the tables by associate, do the
sums
> and then the formulas within the same query or run separate queries for
each
> table and then link results via associate? My main problem is being able
to
> pass the date range. I've done this in ACCESS with a form as the central
> input for the date range, but don't know how in MS-SQL.
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...amming/200606/1