I just tried that and it is giving the same results. I also tried creating a new database with just those two tables, data, and no keys on either table and it is giving the same result.
From: [email protected] [mailto:[email protected]] On Behalf Of Bill Downall Sent: Thursday, January 06, 2011 3:28 PM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: Select statement Charlie, Try this: Drop the Foreign Key, Primary Key and/or indexes on StfMbrDscplRecno in both tables, and create them over again. Bill On Thu, Jan 6, 2011 at 4:14 PM, Charles Parks <[email protected]<mailto:[email protected]>> wrote: I reloaded the database. These are the results that I get SELECT count(*) FROM StfMbrLicense WHERE StfMbrDscplRecno in (select StfMbrDscplRecno from StfMbrDiscipline) count (*) ---------- 1274 SELECT count(*) FROM StfMbrLicense t1, StfMbrDiscipline t2 WHERE t1.StfMbrDscplRecno = t2.StfMbrDscplRecno count (*) ---------- 0 SELECT count(*) FROM StfMbrLicense t1 INNER JOIN StfMbrDiscipline t2 on t1.StfMbrDscplRecno = t2.StfMbrDscplRecno count (*) ---------- 0 From: [email protected]<mailto:[email protected]> [mailto:[email protected]<mailto:[email protected]>] On Behalf Of James Bentley Sent: Monday, January 03, 2011 7:09 PM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: Select statement Charlie, Try SELECT t1.StfMbrDscplRecno, t1.RgstrtnStatusRecno, t2.StfMbrDscplRecno, t2.StfMbr, t2.Discipline FROM StfMbrLicense t1 + INNER JOIN StfMbrDiscipline t2 on t1.StfMbrDscplRecno = t2.StfMbrDscplRecno just make sure that column names are properly spelled and correctly associated with t1 and t2 references Jim Bentley American Celiac Society [email protected]<mailto:[email protected]> tel: 1-504-737-3293 From: Charles Parks <[email protected]<mailto:[email protected]>> To: RBASE-L Mailing List <[email protected]<mailto:[email protected]>> Sent: Mon, January 3, 2011 4:52:25 PM Subject: [RBASE-L] - Select statement I have a select statement that is not returning rows of data or an error message. Is there a different way to write the statement? This command: bro * from stfmbrlicense where StfMbrDscplRecno in (Select StfMbrDscplRecno from StfMbrDiscipline) returns several rows of data. This command: SELECT t1.StfMbrDscplRecno, t1.RgstrtnStatusRecno, t2.StfMbrDscplRecno, t2.StfMbr, t2.Discipline FROM StfMbrLicense t1, StfMbrDiscipline t2 where t1.StfMbrDscplRecno = t2.StfMbrDscplRecno Returns no rows of data.
