Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 4 of 4 total |
How combine tre different table ? |
Wed, May 26 2010 9:30 AM | Permanent Link |
Carlos | I have three tables with different fields;for every table I must extract
only the row where the field fxx='value' and combine them in a fourth table. Example Table A: f1, f2, f3, fxx Table B: f4, f5, fxx Table C: f6, f7, fxx WHERE xx='value' the fxx field is the same for all tables The result tables is Table RESULT: f1, f2, f3, f4, f5, f6, f7,fxx every time I empty the table result.. The table name and fielsd name are constants. Is possible to use a single SQL statement ? Delphi 7 Ent + Dbisam 3.30 Thank you Carlos |
Wed, May 26 2010 10:34 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Carlos
I don't know if UNION is supported by DBISAM 3.30 but if it does you should be able to do something like SELECT f1, f2, f3, '' AS f4, '' AS f5, '' AS f6, '' AS f7, fxx FROM A WHERE fxx='value' UNION SELECT '' AS f1, '' AS f2, '' AS f3, f4, f5, '' AS f6, '' AS f7, fxx FROM A WHERE fxx='value' UNION SELECT '' AS f1, '' AS f2, '' AS f3, '' AS f4, '' AS f5,f6,f7, fxx FROM A WHERE fxx='value' assuming the fields are character, if integer use 0 etc Roy Lambert [Team Elevate] |
Wed, May 26 2010 11:25 AM | Permanent Link |
Carlos | "Roy Lambert" <roy.lambert@skynet.co.uk> ha scritto nel messaggio news:041A20F9-3F0B-418A-8BB6-9CFA3C5F714B@news.elevatesoft.com... > Carlos > > > I don't know if UNION is supported by DBISAM 3.30 but if it does you > should be able to do something like > > SELECT f1, f2, f3, '' AS f4, '' AS f5, '' AS f6, '' AS f7, fxx FROM A > WHERE fxx='value' > UNION > SELECT '' AS f1, '' AS f2, '' AS f3, f4, f5, '' AS f6, '' AS f7, fxx FROM > A WHERE fxx='value' > UNION > SELECT '' AS f1, '' AS f2, '' AS f3, '' AS f4, '' AS f5,f6,f7, fxx FROM A > WHERE fxx='value' > > assuming the fields are character, if integer use 0 etc > > Roy Lambert [Team Elevate] > I Roy, thank you ! I translate the SQL in my test case: SELECT NUMFATTURA, "NUMDDT", "NUMREG", CODINT FROM FATDET WHERE CODINT='AP1200RP15' UNION SELECT "NUMFATTURA " , NUMDDT, "NUMREG", CODINT FROM DDT WHERE CODINT='AP1200RP15' UNION SELECT "NUMFATTURA " , "NUMDDT", NUMREG, CODINT FROM MM0MOVI WHERE CODINT='AP1200RP15' and I obtain EXACTLY the following result: +-------------+-------------+--------------+--------------+ NUMFATTURA NUMDDT EXPRESSION CODINT + +-------------+-------------+--------------+--------------+ 00056 + + NUMREG +AP1200RP15+ 00058 + + NUMREG +AP1200RP15+ 00062 + + NUMREG +AP1200RP15+ 00068 + + NUMREG +AP1200RP15+ NUMFATT + 000137 + NUMREG +AP1200RP15+ NUMFATT + NUMDDT + 000678 +AP1200RP15+ NUMFATT + NUMDDT + 000684 +AP1200RP15+ NUMFATT + NUMDDT + 000699 +AP1200RP15+ NUMFATT + NUMDDT + 000700 +AP1200RP15+ Is possible to clean the cell ? Thank yo Carlos |
Wed, May 26 2010 12:07 PM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Carlos
SELECT NUMFATTURA, '' AS NUMDDT, '' AS NUMREG, CODINT FROM FATDET WHERE CODINT='AP1200RP15' UNION SELECT '' AS NUMFATTURA, NUMDDT, '' AS NUMREG, CODINT FROM DDT WHERE CODINT='AP1200RP15' UNION SELECT '' AS NUMFATTURA, '' AS NUMDDT, NUMREG, CODINT FROM MM0MOVI WHERE CODINT='AP1200RP15' Should work ie I'm asking for an emptystring to be returned as the column Roy Lambert |
This web page was last updated on Sunday, May 19, 2024 at 08:46 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |