I have a database with three tables
tbl_listings - listings of houses on for sale
tbl_intersted - table which tracks if a user is interested in the
listing, it has two columns mls(the key for tbl_listings) and user(user
login)
tbl_review - table which trackes if a user has reviewed the listing.
Like tbl_interested it has two columns (the key for tbl_listings) and
user(user login)
How can I create a query on tbl_listings for reocords reviewed by one
user?
I am trying to create a query for listings that are revied by user
userid. I am using the query below. It works fine unless there is a
record in tbl_interested for a differnt user.
In reality I am calling this query from the web. On the website I have
an intersted dropdown with the choices All, interested, not interested.
The website also has a reviewed dropdown with all, reviewed and not
reviewed.
I am using the query below as a starting point. my query works fine
with one user, but if a user2 enters a record in tbl_intersted it
throws off the left join for user1. How can I fix this?
SELECT COUNT(B.reviewed) AS review_count,Count(B.mls) as mls_count,
A.mls,
FROM mls.tbl_listings A
LEFT OUTER JOIN mls.tbl_review B ON A.mls = B.mls
LEFT OUTER JOIN mls.tbl_interested D ON A.mls = D.mls
where (B.reviewed = 'userid') and ((D.interested is null) or
(D.interested = 'userid'))
----
My query works fine if there is one user, however once user2 reviews a
record from tbl_listing user1