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.
>

Help needed!

Hello guys,
I'm new to the forum and to MS SQL 2K.
I'm trying to a merge similar rows in a table into a single row and put them in a new table.

Example:-
This is my input table
TableA
ID A B C
--------
1 jk kl bj
2 sd we op
3 io po kl
1 ui gh ew
2 kl re op
1 qw kj nn

My output table should look like this
TableB
ID A1 B1 C1 A2 B2 C2 A3 B3 C3
----------------
1 jk kl bj ui gh ew qw kj nn
2 sd we op kl re op
3 io po kl

Please help me on how to create my output.
Thanks in advance,

Sid.You want help violating the rules of normalization? Should I buy you a carton of cigarettes while I'm at it? Neither activity is healthy.

Seriously, if you must do this it is important to know whether the number of records for an ID is fixed or not. If it can be any number then you are not going to be able to define the columns on your output table ahead of time and you are left with a messy dynamic query task. If there is a limit on the number of records per ID then your problem is merely a moderately difficult cross-tab query.|||normalization or not
this is a good exercise to displace data
kinda like playing scales before you actually play a song on an instrument

i will be working on this tonight|||I'll check this query today ...|||giving up

I don't understand to purpose of this query|||i now have a headache|||There's not much point in pursuing this without further clarification from coolhandsid, so save the Tylenol.|||I've got

Table1
ID | A | B | C
1 | 2 | 3 | 4
2 | 9 | 4 | 5
3 | 22| 53 94

I want this result

Table1
ID X Y Z
1 | 81 | April | NULL
Y | 12 | Dog | Sheep
12.3 | Cherry | Spain | 3|||I've got broccolli and I want lobster. I can't make one out of the other either.|||Remeber the MASH episode (when they used to be good) when they made the spam lamb for the turkish troops?|||Originally posted by blindman
You want help violating the rules of normalization? Should I buy you a carton of cigarettes while I'm at it? Neither activity is healthy.

I would not want you doing the first ... but you can certainly buy that carton of cigs for me ...|||Thanks for all your help :),

I figured it out , it can be down by a DTS package or a cross-tab query.

Sid.

Originally posted by Karolyn
I've got

Table1
ID | A | B | C
1 | 2 | 3 | 4
2 | 9 | 4 | 5
3 | 22| 53 94

I want this result

Table1
ID X Y Z
1 | 81 | April | NULL
Y | 12 | Dog | Sheep
12.3 | Cherry | Spain | 3

Help me Insert/Update!

Can someone please Help,

I'm trying to write an insert/update query but it doesn't work.
My syntax is similar to the following

INSERT/UPDATE INTO GL_SCORE VALUES (1, 'andrew');
INSERT INTO GL_SCORE VALUES (2, 'gordon');
SELECT * FROM GL_SCORE

But it doesn't work, why?

Please help me insert/update becuase it would be good.RTFM

Look up the syntax for UPDATE & INSERT, I'm sure you can answer your question by yourself.|||Hi,

I found syntax on insert and update but nothing on insert/update, like doing them both at once. Why?|||Why do you think, that SQL Server has a Update/Insert command, like Oracle 9i has it's MERGE? Which SQL Server version you are talking about?|||Dear Tronboy

I have never encountered Insert/Update in SQL Server syntax
I think you will have to choose, either its INSERT or UPDATE

If its a type of functionality or logic which is often used you should consider putting it in a user defined function or sproc.|||Tronboy,

Insert and Update are two different command in SQL Server (although updates actually involve deleting and reinserting rows, this all happens behind the scenes).

You may be getting confused because triggers can be set to execute and update.

blindman|||Originally posted by blindman
You may be getting confused because triggers can be set to execute and update.
blindman

Good point, blindman; this is the way to confuse TronBoy completely. I'm proud of you!