June 5, 2003 at 8:59 am
This is very embarrassing to present, but it was the only brute force method I found to select distinct project name from the first table and then join with it project names from the second table that did not aleady exist in the first. There has to be a better way to code this up. The following are the 4 steps that accomplish this task in a miserable attempt that I hate even having my name associated with.
Step 1:
INSERT INTO nontouch.projects_lookup
SELECT distinct project_name,project_descrip,budget_version
FROM nontouch.projects_600
Step 2:
INSERT nontouch.projects_lookup (project_name)
SELECT distinct nontouch.projects_500.project_name
FROM nontouch.projects_500 JOIN nontouch.projects
ON (nontouch.projects_500.project_name <>
nontouch.projects.project_name)
Step 3:
update nontouch.projects_lookup
set nontouch.projects_lookup.project_descrip =
nontouch.projects_500.project_descrip
FROM nontouch.projects_500
where
(nontouch.projects_500.project_name =
nontouch.projects_lookup.project_name)
Step 4:
update nontouch.projects_lookup
set nontouch.projects_lookup.budget_version =
nontouch.projects_500.budget_version
FROM nontouch.projects_500
where
(nontouch.projects_500.project_name =
nontouch.projects_lookup.project_name)
June 5, 2003 at 9:44 am
Try this ... (without the INSERT INTO)
SELECT name, descrip, version
FROM projects_600
GROUP BY name, descrip, version
UNION
SELECT name, descrip, version
FROM projects_500 p5
WHERE NOT EXISTS
(SELECT p6.name
FROM projects_600 p6
WHERE p6.name = p5.name)
GROUP BY name, descrip, version
June 5, 2003 at 10:25 am
SELECT project_name, project_descrip, budget_version
FROM nontouch.projects_600
GROUP BY project_name, project_descrip, budget_version
UNION SELECT project_name, project_descrip, budget_version
FROM nontouch.projects_500 p5
WHERE NOT EXISTS
(SELECT p6.project_name
FROM nontouch.projects_600 p6
WHERE p6.project_name = p5.project_name)
GROUP BY project_name, project_descrip, budget_version
Syntatically this works but now I am getting the incorrect results. I need the select distinct from _600 first table and only the project names from second table _500 that do not match any entries from the first table. When ever I start referring to the other 2 table values (descrip & version) with the name I get skewed results because the 3 values are looked at as a team. I only want the selects made based upon the name and the other two values dragged around as supporting data.
June 6, 2003 at 1:47 am
Yep, your analysis is correct.
There are two ways depending on the data.
1. Is description and budget_version always the same for one project?
In this case, you can use
SELECT name, min(descrip), min(budget_version)
FROM ...
GROUP BY name
This is in fact tricking SQLServer since you have denormalised tables...
2. The other way is substituting your select queries in the frame I posted before. You get something like
SELECT distinct name, descrip, version
FROM projects_600
UNION
SELECT distinct name, descrip, version
FROM projects_500 p5
WHERE NOT EXISTS
(SELECT p6.name
FROM projects_600 p6
WHERE p6.name = p5.name)
This should do the trick...
June 17, 2003 at 4:47 pm
SELECT distinct project_name, project_descrip, budget_version
FROM nontouch.projects_600
UNION
SELECT distinct project_name, project_descrip, budget_version
FROM nontouch.projects_500 p5
WHERE NOT EXISTS
(SELECT project_name, project_descrip, budget_version
FROM nontouch.projects_600 p6
WHERE p6.project_name = p5.project_name)
WOW - excellent logic. I did not think of this at all. What a break through after all that I had tried. Thank you very much!!!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply