What To Join?

[Dot Net Daddy]

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.pictur eID = Views.pictureID WHERE Pictures.active ='Y' ORDER BY
NEWID()

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


2.
SELECT TOP 1 Pictures.pictur eID FROM Pictures INNER JOIN Views ON
Pictures.pictur eID = 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.pictur eID FROM Pictures LEFT OUTER JOIN Views ON
Pictures.pictur eID = 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.pictur eID FROM Pictures, Views WHERE
Pictures.pictur eID = 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.

[Erland Sommarskog]

Re: What To Join?

Dot Net Daddy (cagriandac@gma il.com) writes:

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.

In general, a good recommendation for this type of question is that
you post:

1) CREATE TABLE statements for your tables.
2) INSERT statments with sample data.
3) The desired result given the sample.

To wit, your attempt queries tells me very little of what you are trying
to do.

2.
SELECT TOP 1 Pictures.pictur eID FROM Pictures INNER JOIN Views ON
Pictures.pictur eID = Views.pictureID WHERE Pictures.active ='Y' ORDER BY
NEWID()
>
and got the error: There is no row at position 0.

This is a mysterious error. Did it come from SQL Server? Or more
precisely in which context did you run the query? If you ran it from
a query window, can you the complete message with message numbers and
all. I tried to search for such a message in sys.messages but find none,
why I suspect that it's not a message from SQL Server.

Do you get this message every time when you run this query?

SELECT TOP 1 Pictures.pictur eID FROM Pictures LEFT OUTER JOIN Views ON
Pictures.pictur eID = Views.pictureID WHERE Pictures.active ='Y' ORDER BY
NEWID
>
This query hasn't returned an error, but doesnt return the desired
result.

This query could give you a picture which is not in Views, since you
perform an outer join.

4.
>
SELECT TOP 1 Pictures.pictur eID FROM Pictures, Views WHERE
Pictures.pictur eID = Views.pictureID AND Pictures.active ='Y' ORDER BY
NEWID()
>
This query also hasn't returned an error, but doesnt return the desired
result too.

This query is equivalent to the second query and should yield the
same query plan - and error message.

Anyway, since I have no idea of what result you get and what result
you desire, I can't comment on that part.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx