New topic: Data in a listbox from 2 tables
<http://forums.realsoftware.com/viewtopic.php?t=46393> Page 1 of 1 [ 5 posts ] Previous topic | Next topic Author Message rhartz Post subject: Data in a listbox from 2 tablesPosted: Sun Dec 30, 2012 4:09 pm Joined: Mon Feb 20, 2012 3:16 pm Posts: 75 I have a table "customers" and a table "orderheaders". I am creating the customers screen and want to include a lbxReferrals listing any referrals a customer has. The data in lbxReferrals includes ID (which is the order number), OrderDate, and LastName (of the customer). The ID and OrderDate come form the orderheaders table. LastName comes from the customers table. dim sql3 as String sql3 = "SELECT * FROM orderheaders WHERE CustomerIDReferral IN (SELECT ID FROM customers WHERE ID = " + lbxreg.Cell(lbxReg.ListIndex, 0) + ") ORDER BY OrderDate" dim rs3 as RecordSet = db.SQLSelect(sql3) while not rs3.eof lbxReferrals.AddRow(rs3.Field("ID").StringValue) 'lbxReferrals.Cell(lbxReferrals.LastIndex, 1) = rs3.Field("LastName").StringValue /// this is the field I am struggling with lbxReferrals.Cell(lbxReferrals.LastIndex, 2) = rs3.Field("OrderDate").StringValue lbxReferrals.Cell(lbxReferrals.LastIndex, 3) = rs3.Field("ID").StringValue rs3.MoveNext wend rs3.Close The other fields are showing up in the listbox, but the LastName is not. I commented it out b/c as is, it was throwing an error because there is no LastName field in the orderheaders table. But I included it here to show its placement in the lbxReferrals. This is possible, right? I'm hoping I just need some guidance on the SQL statement. Or will I need a seperate SQL statement? Top DaveS Post subject: Re: Data in a listbox from 2 tablesPosted: Sun Dec 30, 2012 4:29 pm Joined: Sun Aug 05, 2007 10:46 am Posts: 4374 Location: San Diego, CA "SELECT a.ID, a.OrderDate, b.lastname FROM orderheaders a LEFT JOIN customers b ON a.CustomerIDReferral=b.ID AND b.ID="+ lbxreg.Cell(lbxReg.ListIndex, 0) ORDER BY a.OrderDate _________________ Dave Sisemore MacPro, OSX Lion 10.7.4 RB2012r1 Note : I am not interested in any solutions that involve custom Plug-ins of any kind Top rhartz Post subject: Re: Data in a listbox from 2 tablesPosted: Sun Dec 30, 2012 5:18 pm Joined: Mon Feb 20, 2012 3:16 pm Posts: 75 Thanks Dave. With the code as is, lbxReferrals loaded with all orderheader data (not right). From your code, I changed the ON to WHERE, and this listed the appropriate referral order data in the listbox, but the LastName pulled the last name of the customer selected in lbxReg (the customer listbox), which is not right. For example, customers Jones (ID = 50) and Thompson (ID = 51) were both referred by customer Smith (ID = 3). In the orderheaders table, the Jones and Thompson orders both have CustomerIDReferral as 3 (Smith). When I select Smith's name in lbxReg, the lbxReferral should show Jones and Thompson and their order details. I made a screenshot, but cannot see how to upload it here. sql3 = "SELECT a.ID, a.OrderDate, b.LastName FROM orderheaders a LEFT JOIN customers b WHERE b.ID = a.CustomerIDReferral AND b.ID = "+ lbxreg.Cell(lbxReg.ListIndex, 0) + " ORDER BY a.OrderDate" Top DaveS Post subject: Re: Data in a listbox from 2 tablesPosted: Sun Dec 30, 2012 5:27 pm Joined: Sun Aug 05, 2007 10:46 am Posts: 4374 Location: San Diego, CA ON is correct syntax for a LEFT JOIN WHERE causes incorrect result as it is not applied to the LEFT JOIN clause. However you are partially correct "SELECT a.ID, a.OrderDate, b.lastname FROM orderheaders a WHERE a.ID="+ lbxreg.Cell(lbxReg.ListIndex, 0) LEFT JOIN customers b ON a.CustomerIDReferral=b.ID AND b.ID=a.id ORDER BY a.OrderDate _________________ Dave Sisemore MacPro, OSX Lion 10.7.4 RB2012r1 Note : I am not interested in any solutions that involve custom Plug-ins of any kind Top rhartz Post subject: Re: Data in a listbox from 2 tablesPosted: Sun Dec 30, 2012 5:56 pm Joined: Mon Feb 20, 2012 3:16 pm Posts: 75 I'm getting NilObject. I think I need to switch around some of the a's and b's I wonder if the WHERE a.ID="+ lbxreg.Cell(lbxReg.ListIndex, 0) should be b.ID a = the orderheaders table and b = customers table. The ID in orderheaders is the orderheader ID, and not equal to the ID in the customers listbox (lbxReg). The CustomerIDReferral is in the orderheaders table and would equal an ID in the lbxReg Top Display posts from previous: All posts1 day7 days2 weeks1 month3 months6 months1 year Sort by AuthorPost timeSubject AscendingDescending Page 1 of 1 [ 5 posts ] -- Over 1500 classes with 29000 functions in one REALbasic plug-in collection. The Monkeybread Software Realbasic Plugin v9.3. http://www.monkeybreadsoftware.de/realbasic/plugins.shtml [email protected]
