Help On SQL Query

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.I don't understand your request. (Your queries below will only compare
PictureID.)
Are you wanting to know if rows exists in one table and ALSO in another
table?
Or do they exists in one table but NOT in the other table.
If so, must all fields match?
It would help us better assist you if you could include table DDL, query
strategy used so far, sample data in the form of INSERT statements, and an
illustration of the desired results. (For help with that refer to:
http://www.aspfaq.com/5006 )
The less 'set up' work we have to do, the more likely you are going to have
folks tackle your problem and help you. Without this effort from you, we are
just playing guessing games.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Dot Net Daddy" <cagriandac@.gmail.com> wrote in message
news:1154637672.972725.282200@.p79g2000cwp.googlegroups.com...
> 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.
>sql