Hi,

I am trying to get rid of an old query which used *= to fetch results from
three tables.
Here is the problem

Table 1
--------
NewsCatid
NewsCatTitle
NewsCatDesc

Table 2
--------
NewsId
NewsCatId
NewsTitle
NewsUrl

Table 3
---------
UserId
NewsId


I am trying to get all the news. If the user has read a particular news
(entry in Table 3), I would like to return the NewsId, other wise null in
that column for that row. So that in code i can flag if the user has read the
news.

But when I try to join the three tables I don't get any results if the user
has not read any news.

Thanks for your help

Could you post the query as well?

Here's a guess (clue: outer joins):

select <column list>
         from Table3
                 left outer join Table2
                                     on Table2.NewsId = Table3.NewsId


ML

---
http://milambda.blogspot.com/

SELECT     dbo.News.NewsId AS Expr1, dbo.News.NewsCatId, dbo.News.NewsTitle,
dbo.News.NewsUrl, dbo.NewsCat.NewsCatId, dbo.NewsCat.NewsCatTitle,
                      dbo.NewsRead.ItemId, dbo.NewsRead.UserId,
dbo.NewsRead.NewsId
FROM         dbo.News INNER JOIN
                      dbo.NewsCat ON dbo.News.NewsCatId =
dbo.NewsCat.NewsCatId LEFT OUTER JOIN
                      dbo.NewsRead ON dbo.News.NewsId = dbo.NewsRead.NewsId

Show quoteHide quote

Try a subquery:

SELECT dbo.News.NewsId
            ,dbo.News.NewsCatId
            ,dbo.News.NewsTitle
            ,dbo.News.NewsUrl
            ,dbo.NewsCat.NewsCatId
            ,dbo.NewsCat.NewsCatTitle
            ,HasBeenRead
               = case
                         when exists(
                                          select *
                                          from dbo.NewsRead
                                          where (dbo.News.NewsId =
dbo.NewsRead.NewsId)
                                         )
            FROM dbo.News
                      INNER JOIN dbo.NewsCat
                                       ON dbo.News.NewsCatId =
dbo.NewsCat.NewsCatId


ML

---
http://milambda.blogspot.com/

random select a row based a weight column.
Timeout expire
Optimize this query
store results over network
dynamic SQL versus a huge IF structure