|
|
Posted by Erland Sommarskog on 08/03/06 21:13
Dot Net Daddy (cagriandac@gmail.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.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.
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.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.
This query could give you a picture which is not in Views, since you
perform an outer join.
> 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.
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@sommarskog.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
Navigation:
[Reply to this message]
|