Board index » delphi » Cascading SQL-Query

Cascading SQL-Query

I want to build a query, that shows data from one table, that are not in
another table. The problem is, that i must filter both table. In MS
Access i would build one query with the one selection and build another
query which use the first query as a table. But in Delphi i dont have
this possibility. I tried to cascade the query like this on the bottom
of this message, but it is wrong. Is there a way to use SQL to do this
or is there a workaround ?

The SQL:

SELECT TBL_Rubriken.R_ID, TBL_Rubriken.Rubrik,
TBL_Hauptrubriken.R_Typ_ID, TBL_Hauptrubriken.Rubriktyp,
[TBL_Hauptrubriken].[Rubriktyp] & " - " & [TBL_Rubriken].[Rubrik] AS
RubrikBez FROM (TBL_Hauptrubriken INNER JOIN TBL_Rubriken ON
TBL_Hauptrubriken.R_Typ_ID = TBL_Rubriken.R_Typ) LEFT JOIN (SELECT
TBL_Objekt_Rubrik.* FROM TBL_Objekt_Rubrik WHERE
((TBL_Objekt_Rubrik.O_ID)=2)) AS TBL_Objekt_Rubrik2 ON TBL_Rubriken.R_ID
= TBL_Objekt_Rubrik2.R_ID WHERE (((TBL_Objekt_Rubrik2.R_ID) Is Null) AND
((TBL_Rubriken.M_ID)=1)) ORDER BY [TBL_Hauptrubriken].[Rubriktyp] & " -
" & [TBL_Rubriken].[Rubrik];

With Params (I need this) myO_ID and myM_ID:

SELECT TBL_Rubriken.R_ID, TBL_Rubriken.Rubrik,
TBL_Hauptrubriken.R_Typ_ID, TBL_Hauptrubriken.Rubriktyp,
[TBL_Hauptrubriken].[Rubriktyp] & " - " & [TBL_Rubriken].[Rubrik] AS
RubrikBez FROM (TBL_Hauptrubriken INNER JOIN TBL_Rubriken ON
TBL_Hauptrubriken.R_Typ_ID = TBL_Rubriken.R_Typ) LEFT JOIN (SELECT
TBL_Objekt_Rubrik.* FROM TBL_Objekt_Rubrik WHERE
((TBL_Objekt_Rubrik.O_ID)=:myO_ID)) AS TBL_Objekt_Rubrik2 ON
TBL_Rubriken.R_ID = TBL_Objekt_Rubrik2.R_ID WHERE
(((TBL_Objekt_Rubrik2.R_ID) Is Null) AND ((TBL_Rubriken.M_ID)=:myM_ID))
ORDER BY [TBL_Hauptrubriken].[Rubriktyp] & " - " &
[TBL_Rubriken].[Rubrik];

 

Re:Cascading SQL-Query


Are you trying to find records in one table that do not exist in another table?
If so try something like this

SELECT DISTINCT d.SSN
FROM  "Clients.db" d
WHERE  (d.SSN Not in  (Select SSN from "Register.db"))

Quote
>I want to build a query, that shows data from one table, that are not in
>another table. The problem is, that i must filter both table. In MS
>Access i would build one query with the one selection and build another
>query which use the first query as a table. But in Delphi i dont have
>this possibility. I tried to cascade the query like this on the bottom
>of this message, but it is wrong. Is there a way to use SQL to do this
>or is there a workaround ?

--
Brian Bushay (TeamB)
Bbus...@DataGuidance.com

Other Threads