Aleniko
Hi;
I have 2 tables. polgr.dbf and po.dbf.
I want to query all the records from polgr and some matching info from PO.
The problem is that in some cases there will be 2 records in PO with the same PO#.
The following query produces duplicates if there are 2 records matching in PO.
select polgr.qopen,po.acct from polgr left join po on polgr.pono = po.pono
Is there a join condition or other way to avoid the duplicates
Thanks.
SQL Join question.
Visual FoxPro General
-
- Previous
- 1
- ERROR FOXPRO Would you please provide more info for the problem Usually this error message means, that the application is compiled in a different VFP version than the DLLs in the path. Tag: SQL Join question.
- 2
- CursorAdapters and UDF in FoxPro 9 You may use CursorAdapter Builder to test; it is easy to handle. Tag: SQL Join question.
- 3
- Determining if multiple EXCEL spreadsheet tabs exist before import Yes, you can do it through Excel automation. Code Snippet loExcel = CREATEOBJECT("Excel.Application") *-- Open XLS File loExcel.DisplayAlerts = .F. loExcel.APPLICATION.WorkBooks.OPEN(m.tcInputFile,,,,m.tcPassword) lnNumSheets = loExcel.APPLICATION.APPLICATION.WorkBooks(1).Sheets.COUNT FOR lnSheet = 1 TO m.lnNumSheets *-- Select individual sheet from opened XLS File loSheet = loExcel.APPLICATION.APPLICATION.WorkBooks(1).Sheets(m.lnSheet) WITH loSheet .SELECT lcStr = loExcel.APPLICATION.ActiveSheet.NAME Tag: SQL Join question.
- 4
- ftp and vfp FTP is File Transfer Protocol and could be used to transfer any type of file. DBFs are files so you could transfer them. Reports are files from one point of view (their definiton are in .frx files). However a report output can be a file or not (a postscript,ASCII,html etc). Developers generally prefer to output to a PDF or HTM to send it over to an FTP or email client. Tag: SQL Join question.
- 5
- Foxpro7 crashing with "fatal error exception code=c0000005 ..." You may try to update vfp 7 with service pack 1, install the latest drivers for printers, check if a part from dll remains "hanged" in ram memory. Tag: SQL Join question.
- 6
- SQL query too long doesn´t work with SQLEXEC() AFAIK the limit is 8192 chars, though I may be mistaken. Also, what exactly doesn't work What error do you get Tag: SQL Join question.
- 7
- new to FoxPro...in search of answers Depending of what versions of foxpro, sql or oracle, how big are dbf files...Anyway foxpro is a very good interface to SQL and you may keep it. In my opinion the best combination is vfp +SQL. Tag: SQL Join question.
- 8
- Some questions about VFP Hi Ashan ... I can answer the first couple 1) use the PACK command 2) SET DELETED ON will automatically exclude deleted records from commands with a scope (such as BROWSE, LOCATE, SCAN, etc) and when using the SELECT - SQL command. Alternatively you can explicitly exclude them by using the DELETED () function, e.g. SELECT * from table WHERE NOT DELETED () or SCAN ALL FOR NOT DELETED () Dom Tag: SQL Join question.
- 9
- Creating a folder using VFP6 lcFolderName = "c:\MyDir" + substr(a,1,3) md (m.lcFolderName) I would advise you to use meangful variable names instead of a, b, etc. for several reasons: 1. Maintenance 2. letters a-j are reserved for work areas If you want to use variable in the SQL, it depends on the location, e.g. lcTable = 'c:\MyDir\MyTable' lcNewTable = 'c:\MyDir\MyNewTable' lcWhere = "MyIntegerField = 5" select * from (m.lcTable) where &lcWhere into table (m.lcNewTable) Tag: SQL Join question.
- 10
- Retrieve Recno() of a newly inserted row into a table If you are using insert you may find it at recno()+1 Tag: SQL Join question.
- 11
- MSCHRT20.ocx erro You may try to registe the ocx file from START>RUN and type: regsvr32 MSCHRT20.ocx Tag: SQL Join question.
- 12
- ActiveX control with FoxPro What is " EnableSimpleFrame" What Fox Pro version are you using (It is visual) Tag: SQL Join question.
- 13
- CONFIG in top-level application Are you sure you're running with this config.fpw in effect Is it in the same start directory where the application resides Because it would run your command specified in the COMMAND otherwise. You second question is unclear. What exactly do you mean Tag: SQL Join question.
- 14
- unique It may be possible, depending on your software used to scan... You may index table by id (depending on vfp version): index on id tag YourTagName of YourIndexFileName ascending UNIQUE Tag: SQL Join question.
- 15
- Problem selecting a font in Visual Foxpro 9.0 I have the same problem. Did you ever figure it out Thanks Tag: SQL Join question.
- 16
- InstallShield vaporized project? (neophyte user) What operating system system Files were on local hdd You may try to not write to much if you had files on local hdd. It is possible to recover. Tag: SQL Join question.
- 1
- Next
- 1
- Fax Report Using VFP9 May be you need to put: SET REPORTBEHAVIOR 80 &&before report command Tag: SQL Join question.
- 2
- replace image with own name Your question is unclear. What exactly would you like to do Your last line REPLACE TABLE1.IMAGE WITH THISFORM.IMAGE1.Picture should be changed to replace Image with m.lcFile in Table1 Tag: SQL Join question.
- 3
- Linking Foxpro table to Access 2003 Sounds like the programmer used varbinary(12) or blob datatype for the column instead of using nocptrans. You can edit your SQL getting the data I think (at least with ADO). If so and you're sure that they would be char data, you can cast to char. ie: select cast(myField as varchar(20)) as MyField from myTable select cast(myField as c(20)) as MyField from myTable select cast(myField as m) as MyField from myTable Tag: SQL Join question.
- 4
- how to store encrypted passwords .. No, as far as I know. My suggestion is to encrypt your passwords or other important data via windows crypt api and save them into a dbf or anykind of file you like. that's what I'm doing there are samples and classes to simplify crypt api access installed with vfp Tag: SQL Join question.
- 5
- Copy File Error in Visual FoxPro 7? This is not a VFP bug, it is general to Microsoft tools (including Word and Excel) and is one of the oddities that Microsoft allow you put spaces in paths, but don't hanlde them directly in their products. Ah well. The solution is to enclose the path in quotes, but not as a quoted string as you are showing: COPY FILE "D:\VFP90\APPLICATION DEVELOPMENT\trycatchtest.prg" TO "C:\Program Files\ComPlus Applications\aktest.prg" should work just fine in any version of VFP. Tag: SQL Join question.
- 6
- SQL Select help. You could try Code Snippet SELECT polgr.pono,po.podate as podate,polgr. design ,polgr.qopen,"Y" AS yesNo ; FROM ; krcbp!polgr ; LEFT JOIN krcbp!po ; ON polgr.pono = po.pono ; INNER JOIN ( SELECT pono FROM poarrivals GROUP BY Pono) AS T ; ON polgr.pono = T.pono ; WHERE polgr. design = "SU-115" AND polgr.qopen <> 0; ORDER BY podate Tag: SQL Join question.
- 7
- need help converting printing from foxpro 2.1 Unix to VFP 7.0 Welcome to the Windows world, where you don't have to worry about printer codes. I think getting your code working in VFP would be way more work than it's worth. Take a look at the Report Designer: MODIFY REPORT. You can lay out the report you need and have lots more control over layout. You can have even more attractive output by automating Microsoft Word, if you prefer. Tamar Tag: SQL Join question.
- 8
- Visual Foxpro Grid is hiding something from me :( Between these two MS KB articles, you can figure out why it's happening and how to solve it so it doesn't happen again. A cursor-based grid is blank if the SELECT-SQL command resets the cursor in Visual FoxPro http://support.microsoft.com/default.asp x scid=kb; en- us;1406 53 PRB: Grid Column's CurrentControl Reverts to Text1 Default http://support.microsoft.com/default.asp x scid=kb; en- us;1324 78 Tag: SQL Join question.
- 9
- How to show properties window to a file in foxpro Hi, I really don't get what you want. I assume you are trying for the context menus in VFP. Context menus can be created by defining a popup as shortcut For Example, Put the following code in right-click event of some control Code Snippet DEFINE POPUP mtest_menu SHORTCUT RELATIVE FROM MROW (), MCOL () DEFINE BAR 1 OF mtest_menu PROMPT "\<Options" DEFINE BAR 2 OF mtest_menu PROMPT "\-" DEFINE BAR 3 OF mtest_menu PROMPT "\<Help " ON SELECTION POPUP mtest_menu moption= BAR () ACTIVATE POPUP mtest_menu DO case CASE moption=1 MESSAGEBOX ("Options") * Handle here... CASE moption=3 MESSAGEBOX ("Help") * Handle here... ENDCASE Best regards, Markish Tag: SQL Join question.
- 10
- Get column name in SQL query Why would you want to do that It sounds as bad design. A data access method (in your data acess layer) should know the name of the column. If you get a column by position you are relying on the table(s) always having your desired column in the proper position. That said, this is how you would do it. Assume here column 3 (column numbers in VFP start with 1 not 0) Code Snippet * open table select 0 use MyTable * get name of 3rd column at runtime x = Field(3) * do query on third column Select evaluate(x) as Col3 from MyTable into cursor MyResult HTH Tag: SQL Join question.
- 11
- Autoincrement and default values with Foxpro tables and ADO CursorAdapters approach ?? Can we assume this is a dead, since your other post on the same subject was answered! Tag: SQL Join question.
- 12
- Combo You're doing something really strange. Please list all the properties for your combobox. Is it bound (e.g. does it have a ControlSource) If yes, then the correct value should automatically appear. You can also always try thisform.ComboBox.ListIndex = 1 to select the first item in the list. Tag: SQL Join question.
- 13
- is it possible to bold a section of a textbox in a report? Use the ActiveX RTF control. Not sure it's easier, but it can do what you want with a single control. Tamar Tag: SQL Join question.
- 14
- datatype casting INT(VAL(string)) Tag: SQL Join question.
- 15
- Get column name in SQL query Why would you want to do that It sounds as bad design. A data access method (in your data acess layer) should know the name of the column. If you get a column by position you are relying on the table(s) always having your desired column in the proper position. That said, this is how you would do it. Assume here column 3 (column numbers in VFP start with 1 not 0) Code Snippet * open table select 0 use MyTable * get name of 3rd column at runtime x = Field(3) * do query on third column Select evaluate(x) as Col3 from MyTable into cursor MyResult HTH Tag: SQL Join question.
- 16
- SQl Select question use the UNION clause. Select * from table1 union ALL select * from table2 Alen. Tag: SQL Join question.
- 1
- Previous
Re: SQL Join question.
Markish
Hi Aleniko,
probably you can use subqueries....
Best regards,
Markish
Re: SQL Join question.
CetinBasoz
If po.acct is same for all pono that has the same Id then you can use distinct keyword:
select distinct polgr.qopen,po.acct from polgr left join po on polgr.pono = po.pono
Re: SQL Join question.
Naomi Nosonovsky
What is the criteria for selecting unique PO Say, you want the most recent dates info
If yes, you may want to check http://www.tek-tips.com/viewthread.cfm qid=1367284&page=1