Søgning i 2 tabeller

Hvorfor bruger du UNION ALL i stedet for UNION? Forskellen er netop at den sidste selv implicit laver en DISTINCT mens at den første kan resultere i dubletter. Jeg mener ... du havde jo en DISTINCT i det foregående.

For at UNION (m/u ALL) skal fungere så skal de to subselects:

(SELECT ...) UNION (SELECT ...)

- returnere de same antal søjler, med de same typer og navne. Hvis navnet på en af søjlerne i den ene afviger fra navnet i den anden, så kan man bruge AS til at omdøbe den.

For at være mere specifik, så er det en tabel for alm. sider og en for nyheder.

Siderne er lagt i en tabel for sig, hvor afsnittene er lagt i en anden. Jeg benytter RIGHT JOIN for at forbinde disse.

Min idé var at kopiere disse 2 tabeller og kalde dem noget anden, og så forbinde den med UNION ALL - det virker bare ikke.

Det er såmænd ikke så lang tid siden at noget lignende netop den problemstilling var oppe. Måske kan du blive lidt inspireret der:

http://www.eksperten.dk/spm/619854

Hvis jeg gør sådan:

StrSQL = "SELECT DISTINCT Paragraph.ParagraphPageID, Page.PageParentPageID, Page.PageMenuText, Page.PageTitle, Page.PageDescription, Page.PageCreatedDate, Page.PageUpdatedDate, Page.PageActive, Page.PageAllowsearch, Page.PageActiveFrom, Page.PageActiveTo, Page.PageAreaID, Page.URL FROM Page RIGHT JOIN Paragraph ON Page.PageID = Paragraph.ParagraphPageID WHERE Page.PageActive = true AND Page.PageAllowsearch = true AND Page.PageActiveFrom <= #" & FormatDateTime(Now) & "# AND Page.PageActiveTo >= #" & FormatDateTime(Now) & "#  AND ParagraphText Like '%" & ArraySTR(0) & "%' OR ParagraphHeader Like '%" & ArraySTR(0) & "%' "

StrSQL = "(" & StrSQL & ") UNION("

...og så skriver det hele en gang til herunder med navnene på de nye tabeller, så tager den kun den sidstnævnte....?

Prøv lige at poste hele din SQL.

StrSQL = "SELECT DISTINCT Paragraph.ParagraphPageID, Page.PageParentPageID, Page.PageMenuText, Page.PageTitle, Page.PageDescription, Page.PageCreatedDate, Page.PageUpdatedDate, Page.PageActive, Page.PageAllowsearch, Page.PageActiveFrom, Page.PageActiveTo, Page.PageAreaID, Page.URL FROM Page RIGHT JOIN Paragraph ON Page.PageID = Paragraph.ParagraphPageID WHERE Page.PageActiveFrom <= #" & FormatDateTime(Now) & "# AND Page.PageActiveTo >= #" & FormatDateTime(Now) & "#  AND Page.PageActive = true AND Page.PageAllowsearch = true AND ParagraphText Like '%" & ArraySTR(0) & "%' OR Page.PageActiveFrom <= #" & FormatDateTime(Now) & "# AND Page.PageActiveTo >= #" & FormatDateTime(Now) & "#  AND Page.PageActive = true AND Page.PageAllowsearch = true AND ParagraphHeader Like '%" & ArraySTR(0) & "%' "

StrSQL = "(" & StrSQL & ") UNION("

StrSQL = "SELECT DISTINCT NewsParagraph.ParagraphPageID, NewsPage.PageParentPageID, NewsPage.PageMenuText, NewsPage.PageTitle, NewsPage.PageDescription, NewsPage.PageCreatedDate, NewsPage.PageUpdatedDate, NewsPage.PageActive, NewsPage.PageActiveFrom, NewsPage.PageActiveTo, NewsPage.PageAreaID, NewsPage.URL FROM NewsPage RIGHT JOIN NewsParagraph ON NewsPage.PageID = NewsParagraph.ParagraphPageID WHERE ParagraphText Like '%" & ArraySTR(0) & "%' OR ParagraphHeader Like '%" & ArraySTR(0) & "%' AND PageActiveFrom <= #" & FormatDateTime(Now) & "# AND PageActiveTo >= #" & FormatDateTime(Now) & "#  AND PageAllowsearch = True AND PageActive = True"

Prøv med:

StrSQL1 = "SELECT DISTINCT Paragraph.ParagraphPageID, Page.PageParentPageID, Page.PageMenuText, Page.PageTitle, Page.PageDescription, Page.PageCreatedDate, Page.PageUpdatedDate, Page.PageActive, Page.PageAllowsearch, Page.PageActiveFrom, Page.PageActiveTo, Page.PageAreaID, Page.URL FROM Page RIGHT JOIN Paragraph ON Page.PageID = Paragraph.ParagraphPageID WHERE Page.PageActiveFrom <= #" & FormatDateTime(Now) & "# AND Page.PageActiveTo >= #" & FormatDateTime(Now) & "#  AND Page.PageActive = true AND Page.PageAllowsearch = true AND ParagraphText Like '%" & ArraySTR(0) & "%' OR Page.PageActiveFrom <= #" & FormatDateTime(Now) & "# AND Page.PageActiveTo >= #" & FormatDateTime(Now) & "#  AND Page.PageActive = true AND Page.PageAllowsearch = true AND ParagraphHeader Like '%" & ArraySTR(0) & "%' "

StrSQL2 = "SELECT DISTINCT NewsParagraph.ParagraphPageID, NewsPage.PageParentPageID, NewsPage.PageMenuText, NewsPage.PageTitle, NewsPage.PageDescription, NewsPage.PageCreatedDate, NewsPage.PageUpdatedDate, NewsPage.PageActive, NewsPage.PageActiveFrom, NewsPage.PageActiveTo, NewsPage.PageAreaID, NewsPage.URL FROM NewsPage RIGHT JOIN NewsParagraph ON NewsPage.PageID = NewsParagraph.ParagraphPageID WHERE ParagraphText Like '%" & ArraySTR(0) & "%' OR ParagraphHeader Like '%" & ArraySTR(0) & "%' AND PageActiveFrom <= #" & FormatDateTime(Now) & "# AND PageActiveTo >= #" & FormatDateTime(Now) & "#  AND PageAllowsearch = True AND PageActive = True"

StrSQL = "(" & StrSQL1 & ") UNION (" & StrSQL2 & ")"

Så opstår der en fejl:

Fejltype:
Microsoft JET Database Engine (0x80040E14)
Antallet af kolonner i de to markerede tabeller eller forespørgsler i en foreningsforespørgsel stemmer ikke overens.

De to subselects returnere heller ikke det samme antal felter:

Subquery 1 - Subquery 2

Paragraph.ParagraphPageID, - NewsParagraph.ParagraphPageID,
Page.PageParentPageID, - NewsPage.PageParentPageID,
Page.PageMenuText, - NewsPage.PageMenuText,
Page.PageTitle, - NewsPage.PageTitle,
Page.PageDescription, - NewsPage.PageDescription,
Page.PageCreatedDate, - NewsPage.PageCreatedDate,
Page.PageUpdatedDate, - NewsPage.PageUpdatedDate,
Page.PageActive, - NewsPage.PageActive,
Page.PageAllowsearch, - !!! MANGLER !!!
Page.PageActiveFrom, - NewsPage.PageActiveFrom,
Page.PageActiveTo, - NewsPage.PageActiveTo,
Page.PageAreaID, - NewsPage.PageAreaID,
Page.URL - NewsPage.URL

Super! Rart med nogle friske øjne. Du skal have stor tak for hjælpen.
Så læg blot et svar :)

Svar :^)