Even though I'm doing all this in PostgreSQL I would think it applies to VFP as well.
I'm not used to doing RIGHT JOIN's i.e. including everything in the right-side table regardless of matches in the left-side table. Basically I want to build a list of all my sales reps and tally up their orders for the day. The report will get much more complicated than that of course but right now I'm just trying to get the list of reps to show up. Following an example, this works just fine: drop table test_t1; drop table test_t2; create table test_t1 (id int4, name varchar(10)); create table test_t2 (id int4, value int4); insert into test_t1 (id, name) values (1, 'Matt'); insert into test_t1 (id, name) values (2, 'Joe'); insert into test_t1 (id, name) values (3, 'Bill'); insert into test_t2 (id, value) values (1, 100); insert into test_t2 (id, value) values (2, 200); insert into test_t2 (id, value) values (3, 300); insert into test_t2 (id, value) values (4, 400); insert into test_t2 (id, value) values (5, 500); select * from test_t2 ; select t2.value, t1.name from test_t1 t1 right join test_t2 t2 using (id); And the results are (as I would expect): 100|"Matt" 200|"Joe" 300|"Bill" 400|"" 500|"" Everything from the right-side table, with only matching records from the left-side. Now my real-world example: drop table temp_replist; --build a list of Reps - this works fine select r.rep, r.repid as salesrepid, m.msgentityid into temporary temp_replist from reps r inner join messageentity m using (repid) inner join messageentitygroup_ct mct on (m.msgentityid = mct.msgentityid) where mct.msggroupid = 14 and m.isactive is true ; select r2.rep ,o.salesrepid ,o.orderid as Maxinv from orders o right join temp_replist r2 using (salesrepid) where o.orderdate = CURRENT_DATE and o.quote is false and o.custid <> 2268 ; Yes, I'm doing some funky alias stuff but I've done it with strict naming, and also tried it using direct tables rather than temp tables as shown here. I expect to see a list of 13 reps w/ nothing to the right, except for the couple of lines that actually do have matches. I get: "GregT"|116|100117 "Bruno"|124|100120 "Hugh"|125|100118 "Hugh"|125|100116 The linking field (salesrepid) is the same data type (INT4). I'm stumped. Why don't I get the whole list of reps? TIA, -- Matthew S. Jarvis IT Manager Bike Friday - "Performance that Packs." www.bikefriday.com 541/687-0487 x140 [EMAIL PROTECTED] _______________________________________________ Post Messages to: [email protected] Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED] ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
