[firebird-support] Help with a query
I have two tables, for example TableA and TableB. Each row of TableA contains a foreign key to a row of TableB. This a 1 to 1 relation, so each row of TableA is linked to one different row of TableB. The foreign key field can also be NULL, in this case the TableA row is not connected to a TableB row. All TableB rows depend logically on TableA rows, so all TableB rows should be linked by a TableA row. I would like to check for program errors during development, so I would like to know if there are orphans TableB rows. May somebody suggest me a good way to find the rows in TableB that are not connected from any row in TableA? Thanks in advance Fulvio Senore ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo Groups Links * To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ * Your email settings: Individual Email | Traditional * To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) * To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com * To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com * Your use of Yahoo Groups is subject to: http://info.yahoo.com/legal/us/yahoo/utos/terms/
Re: [firebird-support] Help with a query
Hi Fulvio, I have two tables, for example TableA and TableB. Each row of TableA contains a foreign key to a row of TableB. This a 1 to 1 relation, so each row of TableA is linked to one different row of TableB. The foreign key field can also be NULL, in this case the TableA row is not connected to a TableB row. All TableB rows depend logically on TableA rows, so all TableB rows should be linked by a TableA row. I would like to check for program errors during development, so I would like to know if there are orphans TableB rows. May somebody suggest me a good way to find the rows in TableB that are not connected from any row in TableA? Why not reverse the link then, and make sure the foreign key field in TableB is NOT NULL? But if that's not an option, you can periodically check for orphans with select columns from TableB b where not exists (select * from TableA a where a.fk = b.target) Kind regards, Paul Vinkenoog ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo Groups Links * To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ * Your email settings: Individual Email | Traditional * To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) * To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com * To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com * Your use of Yahoo Groups is subject to: http://info.yahoo.com/legal/us/yahoo/utos/terms/
Re: [firebird-support] Help with a query
Try something like this: Select b.* from TableB b where not exists(select 1 from TableA a where a.KeyField = b.KeyField) HTH Woody (TMW) -Original Message- From: Fulvio Senore Sent: Friday, December 20, 2013 7:15 AM To: firebird-support@yahoogroups.com Subject: [firebird-support] Help with a query I have two tables, for example TableA and TableB. Each row of TableA contains a foreign key to a row of TableB. This a 1 to 1 relation, so each row of TableA is linked to one different row of TableB. The foreign key field can also be NULL, in this case the TableA row is not connected to a TableB row. All TableB rows depend logically on TableA rows, so all TableB rows should be linked by a TableA row. I would like to check for program errors during development, so I would like to know if there are orphans TableB rows. May somebody suggest me a good way to find the rows in TableB that are not connected from any row in TableA? Thanks in advance Fulvio Senore ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo Groups Links ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo Groups Links * To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ * Your email settings: Individual Email | Traditional * To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) * To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com * To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com * Your use of Yahoo Groups is subject to: http://info.yahoo.com/legal/us/yahoo/utos/terms/
Re: [firebird-support] Help with a query
On Fri, 20 Dec 2013 14:15:38 +0100, Fulvio Senore mail...@fsoft.it wrote: I have two tables, for example TableA and TableB. Each row of TableA contains a foreign key to a row of TableB. This a 1 to 1 relation, so each row of TableA is linked to one different row of TableB. The foreign key field can also be NULL, in this case the TableA row is not connected to a TableB row. All TableB rows depend logically on TableA rows, so all TableB rows should be linked by a TableA row. I would like to check for program errors during development, so I would like to know if there are orphans TableB rows. May somebody suggest me a good way to find the rows in TableB that are not connected from any row in TableA? To me it sounds like you have the dependencies reversed: TableB should have a foreign key to TableA, and not the other way around. Mark
Re: [firebird-support] Help with a query
Il 20/12/2013 16:32, Mark Rotteveel ha scritto: On Fri, 20 Dec 2013 14:15:38 +0100, Fulvio Senore mail...@fsoft.it wrote: I have two tables, for example TableA and TableB. Each row of TableA contains a foreign key to a row of TableB. This a 1 to 1 relation, so each row of TableA is linked to one different row of TableB. The foreign key field can also be NULL, in this case the TableA row is not connected to a TableB row. All TableB rows depend logically on TableA rows, so all TableB rows should be linked by a TableA row. I would like to check for program errors during development, so I would like to know if there are orphans TableB rows. May somebody suggest me a good way to find the rows in TableB that are not connected from any row in TableA? To me it sounds like you have the dependencies reversed: TableB should have a foreign key to TableA, and not the other way around. Many thanks to everybody who helped with my question. I was also wondering if I can find a better design, but it's good to know how to solve my problem. Fulvio Senore
SV: [firebird-support] help for a query where i need percentage calculation
hi all, i have this table TABLE DATA ( ID bigint NOT NULL, TIME timestamp NOT NULL, SOURCE_ID bigint, VALUE double precision NOT NULL, ); i need to do a query or a procedure to group data by SOURCE_ID and to get SOURCE_ID, Max(time), percentage of rows where value 5, percentage of rows where value 10 WITH TMP(SOURCE_ID, MAX_TIME, VALUE5, VALUE10, COUNT_ALL) AS (SELECT SOURCE_ID, MAX(TIME), SUM(CASE WHEN VALUE 5 THEN 1 ELSE 0 END), SUM(CASE WHEN VALUE 10 THEN 1 ELSE 0 END), COUNT(*) FROM DATA GROUP BY 1) SELECT SOURCE_ID, MAX_TIME, (VALUE5*100)/COUNT_ALL, (VALUE10*100)/COUNT_ALL FROM TMP my table have a lot of data (about 100 milions of record) so i need also that the query is fastest The only way to make a query quick is to ascertain that it doesn't involve a lot of rows. It doesn't matter if the table contains 100 million rows if there's only 10 rows with SOURCE_ID 100 and you put WHERE SOURCE_ID = 100 in the where clause of the CTE above (provided SOURCE_ID is indexed, of course), but without such a where clause things will be slow (Firebird has to examine every row that contributes to the answer regardless of the value of VALUE, possibly even several versions of each row). HTH, Set
