need help on this sql

hi

sql server 2005

select top 9 p.Id, p.Thumbnail, p.RegDate, p.RegBy, a.Title, a.Category
from Picture as p
inner join Advert as a on p.advert = a.Id
inner join Category as t on a.Category = t.Id
where a.Approved = 1 and p.Thumbnail = 1 and t.MainType = 2
order by newid();

this sql returns no rows, I wonder what I do wrong here..

The category table contain hierarchy data, so I added a MainType to that
table so I quikly could filter out rows accosiated with a specific
category..

The Advert table has 1 row, it's Category value is 5, in the table Category
the Id of row where maintype is 5 is 2 - hence I thought the second join
should work

any suggestions?

General debugging approach:

strip it down until it produces some output, then add things back in.

What happens if you drop the "where" and "order" clauses?

If still no output, start dropping the inner joins (may need to change
request to "*" during the debugging.)

If you get output, put the where conditions back one at a time.


In my experience, you'll usually find a particular clause that turns
off all output. Examine it closely and you'll usually find a bug.

Good luck!




On Tue, 7 Apr 2009 17:26:36 +0200, "Jeff"
wrote:

>hi
>
>sql server 2005
>
>select top 9 p.Id, p.Thumbnail, p.RegDate, p.RegBy, a.Title, a.Category
>from Picture as p
> inner join Advert as a on p.advert = a.Id
> inner join Category as t on a.Category = t.Id
>where a.Approved = 1 and p.Thumbnail = 1 and t.MainType = 2
>order by newid();
>
>this sql returns no rows, I wonder what I do wrong here..
>
>The category table contain hierarchy data, so I added a MainType to that
>table so I quikly could filter out rows accosiated with a specific
>category..
>
>The Advert table has 1 row, it's Category value is 5, in the table Category
>the Id of row where maintype is 5 is 2 - hence I thought the second join
>should work
>
>any suggestions?
>