What To Join?

Giganews Newsgroups
Subject: What To Join?
Posted by:  Dot Net Daddy (cagriand…@gmail.com)
Date: 3 Aug 2006

Hi,

I am new to MS-SQL and I have a problem. Actually I have made similar
queries on Oracle, but now I failed on MS Sql. First of all I am using
SQL Server 2005, but I think it is not that much important because my
question is related something fundamental.

I want to compare the result (which should be random) in one table,
with another one in another table and if they are same, I want to check
the other information. In both tables the column name is the same.

So far I have tried:

1.

SELECT TOP 1 pictureID FROM Pictures INNER JOIN Views ON
Pictures.pictureID = Views.pictureID WHERE Pictures.active='Y' ORDER BY
NEWID()

and got the error:    Ambiguous column name 'pictureID'.

2.
SELECT TOP 1 Pictures.pictureID FROM Pictures INNER JOIN Views ON
Pictures.pictureID = Views.pictureID WHERE Pictures.active='Y' ORDER BY
NEWID()

and got the error:    There is no row at position 0.

3.

SELECT TOP 1 Pictures.pictureID FROM Pictures LEFT OUTER JOIN Views ON
Pictures.pictureID = Views.pictureID WHERE Pictures.active='Y' ORDER BY
NEWID

This query hasn't returned an error, but doesnt return the desired
result.

4.

SELECT TOP 1 Pictures.pictureID FROM Pictures, Views WHERE
Pictures.pictureID = Views.pictureID AND Pictures.active='Y' ORDER BY
NEWID()

This query also hasn't returned an error, but doesnt return the desired
result too.

Thanks in advance for your help.

Replies