Simple 3 table query failing.

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

Your description is a little unclear (to me, at least), so perhaps you
could post a short test case, to show exactly what your tables look
like and what results you require?

http://www.aspfaq.com/etiquette.asp?id=5006

Simon

On 24 Aug 2005 21:52:06 -0700, tdmailbox@yahoo.com wrote:

>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

Hi tdmailbox,

I agree with Simon: complete table definitions, sample data and expected
output make helping you a lot easier. Based on this message, the best I
can do is a guess:

SELECT          COUNT(B.reviewed)  AS review_count,
COUNT(B.mls)       AS mls_count,
A.mls,
FROM            mls.tbl_listings   AS A
LEFT OUTER JOIN mls.tbl_review     AS B
ON   A.mls = B.mls
AND  B.reviewed = 'userid'
LEFT OUTER JOIN mls.tbl_interested AS D
ON   A.mls = D.mls
AND (D.interested = 'userid' OR D.interested IS NULL)

By the way, I recommend you rename your tables: get rid of the tbl_
prefix (a table is the only data structure allowed in a database, so
it's useless) and for the main table: name it after what it contains,
not after how it's presented.

* tbl_listings   ==> HousesAvailable
* tbl_interested ==> Interests
* tbl_review     ==> Reviews

The names may not be optimal (English is not my native language), but
you get the idea.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)