A little SQL help, please?
-
I'm not a SQL expert-not that the following is too hard- but can someone help out please.
I'm user 'A'. And I want to 'copy' the report that I have to other users. I don't want to copy the report if they already have it.
TABLE_REPORTS-Before
user_id.....report_name.....descr
A............report_a..........first month report
A............report_b..........second month report
B............report_b..........second month report
C............report_a..........first month report
C............report_c..........third month report
D............report_b..........second month report
Now I want to 'copy' report_a to the users who don't already have it. Then my table should look like:
TABLE_REPORTS-After
user_id.....report_name.....descr
A............report_a..........first month report
A............report_b..........second month report
B............report_a..........first month report
B............report_b..........second month report
C............report_a..........first month report
C............report_c..........third month report
D............report_a..........first month report
D............report_b..........second month report
-
You'll need to use a subquery, or derived table.
EG:
SET NOCOUNT ON
CREATE TABLE #TABLE_REPORTS (
[user_id] char(1)
, report_name varchar(10)
, descr varchar(50)
)
INSERT INTO #TABLE_REPORTS
VALUES ('A', 'report_a', 'first month report')
INSERT INTO #TABLE_REPORTS
VALUES ('A', 'report_b', 'second month report')
INSERT INTO #TABLE_REPORTS
VALUES ('B', 'report_b', 'second month report')
INSERT INTO #TABLE_REPORTS
VALUES ('C', 'report_a', 'first month report')
INSERT INTO #TABLE_REPORTS
VALUES ('C', 'report_c', 'third month report')
INSERT INTO #TABLE_REPORTS
VALUES ('D', 'report_b', 'second month report')
SELECT * FROM #TABLE_REPORTS
INSERT INTO #TABLE_REPORTS
SELECT
rpt1.user_id
, rpta.report_name
, rpta.descr
FROM (SELECT DISTINCT
report_name, descr
FROM #TABLE_REPORTS
WHERE report_name = 'report_a') rpta
, #TABLE_REPORTS rpt1
LEFT JOIN (
SELECT user_id, report_name
FROM #TABLE_REPORTS
WHERE report_name = 'report_a'
) rpt2
ON rpt1.user_id = rpt2.user_id
WHERE rpt2.user_id is NUll
SELECT * FROM #TABLE_REPORTS
DROP TABLE #TABLE_REPORTS
--------------------
Colt 45 - the original point and click interface
-
Using the same table as in the above post ... How about the following?
INSERT INTO #TABLE_REPORTS
SELECT user_id, 'report_a', 'first month report'
FROM #TABLE_REPORTS
WHERE user_id NOT IN
(SELECT user_id
FROM #TABLE_REPORTS
WHERE report_name = 'report_a')
Could be wrapped in a stored procedure, with the report_name & descr coming in as variables...
greetings from Germany
nano
-
Assuming your table is named: table_reports, this SQL should insert into each other user_id the report_names in A's list that are not already in the other user_id's list:
INSERT INTO table_reports
SELECT DISTINCT u.user_id, a.report_name, a.descr
FROM table_reports AS u, table_reports AS a
WHERE u.user_id<>'A' AND a.user_id='A'
AND a.report_name NOT IN (
SELECT n.report_name FROM table_reports AS n
WHERE n.user_id = u.user_id)
The main join gives you a cross-product of all users NOT 'A' and all reports on A's list. The sub-query selects only those reports on A's list that are NOT on other user's list.
[Note: I added the DISTINCT option after testing to eliminate duplicate rows.]
Regards,Bob Monahon
-
Try this.....
insert into table_reports
select distinct t1.user_id,t2.report_name,t2.descr from table_reports t1 cross join table_reports t2
where t1.user_id+t2.report_name not in (select distinct user_id+report_name from table_reports)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply