Posted by Dot Net Daddy on 08/03/06 20:39

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.

• Next in forum: Migrating data from DB2 to SQL Server
• Prev in forum: What To Join?
• Thread view: What To Join?

[Reply to this message]