|
|
Posted by Hugo Kornelis on 08/25/05 21:05
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)
Navigation:
[Reply to this message]
|