25. februar 2005 - 07:57Der er
4 kommentarer og 1 løsning
Join på union
jeg har fundet frem til dette databasekald:
SELECT (SELECT Sum(hours) FROM idle WHERE date >= #" & from_date & "# AND date <= #" & to_date & "# AND testsystem = singlesystem.name) AS SumOfhours, singlesystem.location, testing.[Time spend], singlesystem.hours, testing.[Done by], testing.Status, testing.[Test platform], testing.ID FROM singlesystem LEFT JOIN testing ON singlesystem.name = testing.[Test platform] WHERE [testing].[Done by] >= #" & from_date & "# AND [testing].[Done by] <= #" & to_date & "# AND testing.Status = 'DONE' ORDER BY singlesystem.location, singlesystem.name, testing.ID;
jeg har en tabel [closed entities], som har samme felter som tabellen [testing].. data fra disse tabeller skal håndteres som data fra 1 tabel i min query..
Not 100% sure what your after but if you want to make UNION select where you have the exact same select as above but replacing the table testing with [closed entities] then this cant be far wrong
SELECT (SELECT Sum(hours) FROM idle WHERE date >= #" & from_date & "# AND date <= #" & to_date & "# AND testsystem = singlesystem.name) AS SumOfhours, singlesystem.location, testing.[Time spend], singlesystem.hours, testing.[Done by], testing.Status, testing.[Test platform], testing.ID FROM singlesystem LEFT JOIN testing ON singlesystem.name = testing.[Test platform] WHERE [testing].[Done by] >= #" & from_date & "# AND [testing].[Done by] <= #" & to_date & "# AND testing.Status = 'DONE' ORDER BY singlesystem.location, singlesystem.name, testing.ID UNION ALL SELECT (SELECT Sum(hours) FROM idle WHERE date >= #" & from_date & "# AND date <= #" & to_date & "# AND testsystem = singlesystem.name) AS SumOfhours, singlesystem.location, [closed entities].[Time spend], singlesystem.hours, [closed entities].[Done by], [closed entities].Status, [closed entities].[Test platform], [closed entities].ID FROM singlesystem LEFT JOIN [closed entities]ON singlesystem.name = [closed entities].[Test platform] WHERE [[closed entities]].[Done by] >= #" & from_date & "# AND [[closed entities]].[Done by] <= #" & to_date & "# AND [closed entities].Status = 'DONE' ORDER BY singlesystem.location, singlesystem.name, [closed entities].ID
Synes godt om
Slettet bruger
25. februar 2005 - 11:18#2
hvad så med ORDER BY? skal det ikke være uden for UNION?
Order By comes at the end, field names are taken from the first SELECT fields
Synes godt om
Slettet bruger
25. februar 2005 - 12:57#4
jeg har nu fået det til at virke.. jeg slettede den første ORDER BY og brugte synonymer for tabelnavnene:
SELECT (SELECT Sum(hours) FROM idle WHERE date >= #" & from_date & "# AND date <= #" & to_date & "# AND testsystem = singlesystem.name) AS SumOfhours, singlesystem.location, T.[Time spend], singlesystem.hours, T.[Done by], T.Status, T.[Test platform], T.ID FROM singlesystem LEFT JOIN testing AS T ON singlesystem.name = T.[Test platform] WHERE T.[Done by] >= #" & from_date & "# AND T.[Done by] <= #" & to_date & "# AND T.Status = 'DONE' UNION ALL SELECT (SELECT Sum(hours) FROM idle WHERE date >= #" & from_date & "# AND date <= #" & to_date & "# AND testsystem = singlesystem.name) AS SumOfhours, singlesystem.location, T.[Time spend], singlesystem.hours, T.[Done by], T.Status, T.[Test platform], T.ID FROM singlesystem LEFT JOIN [closed entities] AS T ON singlesystem.name = T.[Test platform] WHERE T.[Done by] >= #" & from_date & "# AND T.[Done by] <= #" & to_date & "# AND T.Status = 'DONE' ORDER BY singlesystem.location, singlesystem.name, T.ID
Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.
