Try this : SELECT r2.rep,o.salesrepid,o.orderid ; FROM orders as o RIGHT JOIN temp_replist as r2 ON o.salesrepid = r2.salesrepid ; WHERE (ALLTRIM(o.orderdate) = "09/25/2007" OR ISNULL(o.orderdate)) and (ALLTRIM(o.quote) = "F" OR ISNULL(o.quote)) and (ALLTRIM(o.custid) <> "2268" OR ISNULL(o.custid))
The way I understand it, the "join" part of the SQL statement is done before the "where" clause is taken in consideration. By including the NULL fields, you will include the records returned from the join. On Tue, 2007-09-25 at 09:57 -0700, Matthew Jarvis wrote: > 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, > > _______________________________________________ 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.
