June 8, 2004 at 6:17 pm
The following displays just "Jane Doe",
however I want it to display all customers
and then their passwords if they have one
SELECT customers.number, customers.name, xref.details
FROM customers LEFT OUTER JOIN
xref ON customers.number = xref.number
WHERE (xref.type = 'pass')
------------------------------------------
The following displays all customers,
however it makes the password column identical for
both which is wrong since John Doe password = blank
SELECT customers.number, customers.name, xref.details
FROM customers LEFT OUTER JOIN
xref ON customers.number >= xref.number
WHERE (xref.type = 'pass')
------------------------------------------
Please let me know if you want me to email
the 100k MDB file to you for testing purposes.
CUSTOMER dataset
number name
1 Jane Doe
2 John Doe
XREF dataset
number type details
1 email 126@a.com
1 pass goodfood
2 email 42@e.com
------------------------------------------
End Result that I need:
number name details
1 Jane Doe goodfood
2 John Doe
June 8, 2004 at 6:24 pm
The problem is the WHERE clause processes the final resultset and removes all none matches. What you need to do is handle in the join condition like so:
SELECT customers.number, customers.name, xref.details
FROM customers LEFT OUTER JOIN
xref ON customers.number >= xref.number
AND (xref.type = 'pass')
This way if there is a tpye = 'pass' you get that otherwise you get NULL.
Also please do not cross post as most folks will see your post via Active Topics and many cross posts create a lot to dig thru.
June 8, 2004 at 6:27 pm
Thanks for the info.
Sorry about the cross-post.
I will stop doing double posting.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply