Microsoft SQL Server forum - Simple 3 table query failing.

Author

Simple 3 table query failing.

tdmailbox@yahoo.com

2005-08-25, 3:36 am

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

Simon Hayes

2005-08-25, 7:23 am

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

Hugo Kornelis

2005-08-25, 1:23 pm

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)

Sponsored Links