P
Phil
I have a query that should return 160 records. It returns 28. An INNER
join to user_3 produces the same result as a LEFT join to user_3,which
is to select the record, ONLY if there is a matching record in BOTH
tables. If I remove user_3, I get my 160 records. I was under the
impression I would get a record, (with a LEFT join) whether or not there
was a matching record in both tables. I SHOULD be getting ALL records
from Invoice_h, with data from user_3 should a mathc exist. I am only
getting records from invoice_h IF user_3 has matching data.
What gives?
Phil
Left Join:28 records
invoice_h.prebook_ship_date, invoice_h.invoice_id,
invoice_h.invoice_total, invoice_h.sales_rep1pct,
invoice_h.sales_rep2pct, users_3.username, invoice_h.sales_rep3pct
FROM (invoice_h LEFT JOIN prebook ON invoice_h.prebook_id =
prebook.prebook_id) LEFT JOIN users AS users_3 ON invoice_h.sales_rep3id
= users_3.user_id
WHERE (((invoice_h.company_id)=1) AND ((users_3.company_id)=1) AND
((prebook.name)="PSN"));
INNER join: 28 records
SELECT invoice_h.requested_ship_date, invoice_h.ship_date,
invoice_h.prebook_ship_date, invoice_h.invoice_id,
invoice_h.invoice_total, invoice_h.sales_rep1pct,
invoice_h.sales_rep2pct, users_3.username, invoice_h.sales_rep3pct
FROM (invoice_h LEFT JOIN prebook ON invoice_h.prebook_id =
prebook.prebook_id) INNER JOIN users AS users_3 ON
invoice_h.sales_rep3id = users_3.user_id
WHERE (((invoice_h.company_id)=1) AND ((users_3.company_id)=1) AND
((prebook.name)="PSN"));
USER_3 removed: 160 records.
SELECT invoice_h.requested_ship_date, invoice_h.ship_date,
invoice_h.prebook_ship_date, invoice_h.invoice_id,
invoice_h.invoice_total, invoice_h.sales_rep1pct,
invoice_h.sales_rep2pct, invoice_h.sales_rep3pct
FROM invoice_h LEFT JOIN prebook ON invoice_h.prebook_id =
prebook.prebook_id
WHERE (((invoice_h.company_id)=1) AND ((prebook.name)="PSN"));
join to user_3 produces the same result as a LEFT join to user_3,which
is to select the record, ONLY if there is a matching record in BOTH
tables. If I remove user_3, I get my 160 records. I was under the
impression I would get a record, (with a LEFT join) whether or not there
was a matching record in both tables. I SHOULD be getting ALL records
from Invoice_h, with data from user_3 should a mathc exist. I am only
getting records from invoice_h IF user_3 has matching data.
What gives?
Phil
Left Join:28 records
invoice_h.prebook_ship_date, invoice_h.invoice_id,
invoice_h.invoice_total, invoice_h.sales_rep1pct,
invoice_h.sales_rep2pct, users_3.username, invoice_h.sales_rep3pct
FROM (invoice_h LEFT JOIN prebook ON invoice_h.prebook_id =
prebook.prebook_id) LEFT JOIN users AS users_3 ON invoice_h.sales_rep3id
= users_3.user_id
WHERE (((invoice_h.company_id)=1) AND ((users_3.company_id)=1) AND
((prebook.name)="PSN"));
INNER join: 28 records
SELECT invoice_h.requested_ship_date, invoice_h.ship_date,
invoice_h.prebook_ship_date, invoice_h.invoice_id,
invoice_h.invoice_total, invoice_h.sales_rep1pct,
invoice_h.sales_rep2pct, users_3.username, invoice_h.sales_rep3pct
FROM (invoice_h LEFT JOIN prebook ON invoice_h.prebook_id =
prebook.prebook_id) INNER JOIN users AS users_3 ON
invoice_h.sales_rep3id = users_3.user_id
WHERE (((invoice_h.company_id)=1) AND ((users_3.company_id)=1) AND
((prebook.name)="PSN"));
USER_3 removed: 160 records.
SELECT invoice_h.requested_ship_date, invoice_h.ship_date,
invoice_h.prebook_ship_date, invoice_h.invoice_id,
invoice_h.invoice_total, invoice_h.sales_rep1pct,
invoice_h.sales_rep2pct, invoice_h.sales_rep3pct
FROM invoice_h LEFT JOIN prebook ON invoice_h.prebook_id =
prebook.prebook_id
WHERE (((invoice_h.company_id)=1) AND ((prebook.name)="PSN"));