Problem in inner join ?

Hai ,

select emp_id,count(emp_id) as count1 from poll_data group by emp_id order by count1 desc

The output of above query is

emp_id count1

EMP10041 8
EMP10058 8
EMP10059 6
EMP10008 6
EMP10012 3
EMP10018 3
EMP10039 3
EMP10001 2

I have another table as user_table

user_id user_name

EMP10001 Raja
EMP10039 Ram
EMP10018 Ravi

etc

now i am writing a inner join as follows

select y.user_name, x.count1 from [select emp_id,count(emp_id) as count1 from poll_data group by emp_id order by count1 desc] x inner join [user_table] y on x.emp_id = y.user_id

I want to get the below format

Jambu 8
Elangovan 8
Ravi 6
Anitha 6
Ram 3

but i am getting the below error :

Invalid object name 'select emp_id,count(emp_id) as count1 from poll_data group by emp_id order by count1 desc

how to solve this

remove the [ ] & order by clasue on the sub query. Sub query should be enclosed with ( ).

Code Snippet

select

y.user_name

, x.count1

from

(select emp_id,count(emp_id) as count1 from poll_data group by emp_id) x

inner join [user_table] y on x.emp_id = y.user_id

|||

You need to enclose the derived table in parentheses -NOT square brackets.

Code Snippet

SELECT
u.User_Name,
p.Count1
FROM (SELECT
Emp_ID,
Count1 = count(Emp_ID)
FROM Poll_Data
GROUP BY Emp_ID
) p
INNER JOIN User_Table u
ON p.Emp_ID = u.User_ID

problem in copying a table into another one

I am having problems to copy a table into another one

SELECT * INTO UserCopy FROM User WHERE User.ID IN (SELECT MAX(ID) AS LastId FROM Category GROUP BY CatUser)

with SELECT MAX(ID) AS LastId FROM Category GROUP BY CatUser I get : 1,2,3,10

now if I look the result in my table Usercopy I get the values for : 1,2,3,9,10

what can be the problem ? where does the 9 comes from

thank youwas the table empty when you started|||userCopy of course is empty because it is just created

user is full and category too
I want to extract datas from user depending on an ID list in category

thank you for helping|||I created table to test data and as I first though first
your logic is correct

If you return 1,2,3,10
that is what should be used to create your table.

Try to hardcode 1,2,3,10 and see what that returns.
or
just run the select instead of the into and what is returned.

Could you be looking at usercopy created by another owner.
Could you be selecting from a user a category table from another owner.|||ok I try ! and i come back :-)

thank you|||read the sticky at the top of the forum and posts what it asks you to give...

Problem during reinstalling SQL 2000

Hi Group,
I had to unistall my SQL server 2000 SQP4 instalation due to management
console failure.
The problem is, during the reinstallation while the setup is configuring the
server after copy files, setup stops with 'can't not configure the server'
window.
I'm gong to add the lasts rows of SqlStp.log, and i wish to know the best
practices of cleaning an old installation (registry keys, etc...)
Thank a lot to all...
16:49:51 El programa de instalacin no pudo configurar el servidor. Consulte
los registros de errores y C:\Documents and
Settings\sergior\WINDOWS\sqlstp.log para obtener ms informacin.
16:49:51 Action CleanUpInstall:
16:49:51 C:\DOCUME~1\sergior\CONFIG~1\Temp\SqlSetup\Bin\scm .exe -Silent
1 -Action 4 -Service SQLSERVERAGENT
16:49:51 Process Exit Code: (1060) El servicio especificado no existe como
servicio instalado.
16:49:51 C:\DOCUME~1\sergior\CONFIG~1\Temp\SqlSetup\Bin\scm .exe -Silent
1 -Action 4 -Service MSSQLSERVER
16:49:51 Process Exit Code: (0)
16:49:51 StatsGenerate returned: 2
16:49:51 StatsGenerate (0x0,0x1,0xf00000,0x100,1034,303,0x0,0x1,0,0,0
16:49:51 StatsGenerate -1,sergior)
16:49:51 Installation Failed.
Hi Sergio
"Sergio" wrote:

> Hi Group,
> I had to unistall my SQL server 2000 SQP4 instalation due to management
> console failure.
> The problem is, during the reinstallation while the setup is configuring the
> server after copy files, setup stops with 'can't not configure the server'
> window.
> I'm goXng to add the lasts rows of SqlStp.log, and i wish to know the best
> practices of cleaning an old installation (registry keys, etc...)
> Thank a lot to all...
>
> 16:49:51 El programa de instalación no pudo configurar el servidor. Consulte
> los registros de errores y C:\Documents and
> Settings\sergior\WINDOWS\sqlstp.log para obtener más información.
> 16:49:51 Action CleanUpInstall:
> 16:49:51 C:\DOCUME~1\sergior\CONFIG~1\Temp\SqlSetup\Bin\scm .exe -Silent
> 1 -Action 4 -Service SQLSERVERAGENT
> 16:49:51 Process Exit Code: (1060) El servicio especificado no existe como
> servicio instalado.
> 16:49:51 C:\DOCUME~1\sergior\CONFIG~1\Temp\SqlSetup\Bin\scm .exe -Silent
> 1 -Action 4 -Service MSSQLSERVER
> 16:49:51 Process Exit Code: (0)
> 16:49:51 StatsGenerate returned: 2
> 16:49:51 StatsGenerate (0x0,0x1,0xf00000,0x100,1034,303,0x0,0x1,0,0,0
> 16:49:51 StatsGenerate -1,sergior)
> 16:49:51 Installation Failed.
>
This seems to be the same problem as http://tinyurl.com/2c3tdf, the solution
suggested there is to remove the SQL Server installation, download and
install MDAC or check that you current configuration is up-to-date and not
corrupted, then install SQL Server in debug mode "setupsql.exe k=dbg"
so you can skip over the MDAC installation step.
John
|||Ok John,
Thanks for your fast reply, i'm gonna probe it...
"John Bell" <jbellnewsposts@.hotmail.com> escribi en el mensaje
news:24F82C2A-2919-482A-AC7D-C2965BD7D690@.microsoft.com...
> Hi Sergio
> "Sergio" wrote:
> This seems to be the same problem as http://tinyurl.com/2c3tdf, the
> solution
> suggested there is to remove the SQL Server installation, download and
> install MDAC or check that you current configuration is up-to-date and not
> corrupted, then install SQL Server in debug mode "setupsql.exe k=dbg"
> so you can skip over the MDAC installation step.
> John

Problem during reinstalling SQL 2000

Hi Group,
I had to unistall my SQL server 2000 SQP4 instalation due to management
console failure.
The problem is, during the reinstallation while the setup is configuring the
server after copy files, setup stops with 'can't not configure the server'
window.
I'm gong to add the lasts rows of SqlStp.log, and i wish to know the best
practices of cleaning an old installation (registry keys, etc...)
Thank a lot to all...
16:49:51 El programa de instalacin no pudo configurar el servidor. Consulte
los registros de errores y C:\Documents and
Settings\sergior\WINDOWS\sqlstp.log para obtener ms informacin.
16:49:51 Action CleanUpInstall:
16:49:51 C:\DOCUME~1\sergior\CONFIG~1\Temp\SqlSet
up\Bin\scm.exe -Silent
1 -Action 4 -Service SQLSERVERAGENT
16:49:51 Process Exit Code: (1060) El servicio especificado no existe como
servicio instalado.
16:49:51 C:\DOCUME~1\sergior\CONFIG~1\Temp\SqlSet
up\Bin\scm.exe -Silent
1 -Action 4 -Service MSSQLSERVER
16:49:51 Process Exit Code: (0)
16:49:51 StatsGenerate returned: 2
16:49:51 StatsGenerate (0x0,0x1,0xf00000,0x100,1034,303,0x0,0x1
,0,0,0
16:49:51 StatsGenerate -1,sergior)
16:49:51 Installation Failed.Hi Sergio
"Sergio" wrote:

> Hi Group,
> I had to unistall my SQL server 2000 SQP4 instalation due to management
> console failure.
> The problem is, during the reinstallation while the setup is configuring t
he
> server after copy files, setup stops with 'can't not configure the server'
> window.
> I'm go_ng to add the lasts rows of SqlStp.log, and i wish to know the be
st
> practices of cleaning an old installation (registry keys, etc...)
> Thank a lot to all...
>
> 16:49:51 El programa de instalación no pudo configurar el servidor. Consu
lte
> los registros de errores y C:\Documents and
> Settings\sergior\WINDOWS\sqlstp.log para obtener más información.
> 16:49:51 Action CleanUpInstall:
> 16:49:51 C:\DOCUME~1\sergior\CONFIG~1\Temp\SqlSet
up\Bin\scm.exe -Silent
> 1 -Action 4 -Service SQLSERVERAGENT
> 16:49:51 Process Exit Code: (1060) El servicio especificado no existe como
> servicio instalado.
> 16:49:51 C:\DOCUME~1\sergior\CONFIG~1\Temp\SqlSet
up\Bin\scm.exe -Silent
> 1 -Action 4 -Service MSSQLSERVER
> 16:49:51 Process Exit Code: (0)
> 16:49:51 StatsGenerate returned: 2
> 16:49:51 StatsGenerate (0x0,0x1,0xf00000,0x100,1034,303,0x0,0x1
,0,0,0
> 16:49:51 StatsGenerate -1,sergior)
> 16:49:51 Installation Failed.
>
This seems to be the same problem as http://tinyurl.com/2c3tdf, the solution
suggested there is to remove the SQL Server installation, download and
install MDAC or check that you current configuration is up-to-date and not
corrupted, then install SQL Server in debug mode "setupsql.exe k=dbg"
so you can skip over the MDAC installation step.
John|||Ok John,
Thanks for your fast reply, i'm gonna probe it...
"John Bell" <jbellnewsposts@.hotmail.com> escribi en el mensaje
news:24F82C2A-2919-482A-AC7D-C2965BD7D690@.microsoft.com...
> Hi Sergio
> "Sergio" wrote:
>
> This seems to be the same problem as http://tinyurl.com/2c3tdf, the
> solution
> suggested there is to remove the SQL Server installation, download and
> install MDAC or check that you current configuration is up-to-date and not
> corrupted, then install SQL Server in debug mode "setupsql.exe k=dbg"
> so you can skip over the MDAC installation step.
> John

Problem during reinstalling SQL 2000

Hi Group,
I had to unistall my SQL server 2000 SQP4 instalation due to management
console failure.
The problem is, during the reinstallation while the setup is configuring the
server after copy files, setup stops with 'can't not configure the server'
window.
I'm goíng to add the lasts rows of SqlStp.log, and i wish to know the best
practices of cleaning an old installation (registry keys, etc...)
Thank a lot to all...
16:49:51 El programa de instalación no pudo configurar el servidor. Consulte
los registros de errores y C:\Documents and
Settings\sergior\WINDOWS\sqlstp.log para obtener más información.
16:49:51 Action CleanUpInstall:
16:49:51 C:\DOCUME~1\sergior\CONFIG~1\Temp\SqlSetup\Bin\scm.exe -Silent
1 -Action 4 -Service SQLSERVERAGENT
16:49:51 Process Exit Code: (1060) El servicio especificado no existe como
servicio instalado.
16:49:51 C:\DOCUME~1\sergior\CONFIG~1\Temp\SqlSetup\Bin\scm.exe -Silent
1 -Action 4 -Service MSSQLSERVER
16:49:51 Process Exit Code: (0)
16:49:51 StatsGenerate returned: 2
16:49:51 StatsGenerate (0x0,0x1,0xf00000,0x100,1034,303,0x0,0x1,0,0,0
16:49:51 StatsGenerate -1,sergior)
16:49:51 Installation Failed.Hi Sergio
"Sergio" wrote:
> Hi Group,
> I had to unistall my SQL server 2000 SQP4 instalation due to management
> console failure.
> The problem is, during the reinstallation while the setup is configuring the
> server after copy files, setup stops with 'can't not configure the server'
> window.
> I'm goíng to add the lasts rows of SqlStp.log, and i wish to know the best
> practices of cleaning an old installation (registry keys, etc...)
> Thank a lot to all...
>
> 16:49:51 El programa de instalación no pudo configurar el servidor. Consulte
> los registros de errores y C:\Documents and
> Settings\sergior\WINDOWS\sqlstp.log para obtener más información.
> 16:49:51 Action CleanUpInstall:
> 16:49:51 C:\DOCUME~1\sergior\CONFIG~1\Temp\SqlSetup\Bin\scm.exe -Silent
> 1 -Action 4 -Service SQLSERVERAGENT
> 16:49:51 Process Exit Code: (1060) El servicio especificado no existe como
> servicio instalado.
> 16:49:51 C:\DOCUME~1\sergior\CONFIG~1\Temp\SqlSetup\Bin\scm.exe -Silent
> 1 -Action 4 -Service MSSQLSERVER
> 16:49:51 Process Exit Code: (0)
> 16:49:51 StatsGenerate returned: 2
> 16:49:51 StatsGenerate (0x0,0x1,0xf00000,0x100,1034,303,0x0,0x1,0,0,0
> 16:49:51 StatsGenerate -1,sergior)
> 16:49:51 Installation Failed.
>
This seems to be the same problem as http://tinyurl.com/2c3tdf, the solution
suggested there is to remove the SQL Server installation, download and
install MDAC or check that you current configuration is up-to-date and not
corrupted, then install SQL Server in debug mode "setupsql.exe k=dbg"
so you can skip over the MDAC installation step.
John|||Ok John,
Thanks for your fast reply, i'm gonna probe it...
"John Bell" <jbellnewsposts@.hotmail.com> escribió en el mensaje
news:24F82C2A-2919-482A-AC7D-C2965BD7D690@.microsoft.com...
> Hi Sergio
> "Sergio" wrote:
>> Hi Group,
>> I had to unistall my SQL server 2000 SQP4 instalation due to management
>> console failure.
>> The problem is, during the reinstallation while the setup is configuring
>> the
>> server after copy files, setup stops with 'can't not configure the
>> server'
>> window.
>> I'm goíng to add the lasts rows of SqlStp.log, and i wish to know the
>> best
>> practices of cleaning an old installation (registry keys, etc...)
>> Thank a lot to all...
>>
>> 16:49:51 El programa de instalación no pudo configurar el servidor.
>> Consulte
>> los registros de errores y C:\Documents and
>> Settings\sergior\WINDOWS\sqlstp.log para obtener más información.
>> 16:49:51 Action CleanUpInstall:
>> 16:49:51 C:\DOCUME~1\sergior\CONFIG~1\Temp\SqlSetup\Bin\scm.exe -Silent
>> 1 -Action 4 -Service SQLSERVERAGENT
>> 16:49:51 Process Exit Code: (1060) El servicio especificado no existe
>> como
>> servicio instalado.
>> 16:49:51 C:\DOCUME~1\sergior\CONFIG~1\Temp\SqlSetup\Bin\scm.exe -Silent
>> 1 -Action 4 -Service MSSQLSERVER
>> 16:49:51 Process Exit Code: (0)
>> 16:49:51 StatsGenerate returned: 2
>> 16:49:51 StatsGenerate (0x0,0x1,0xf00000,0x100,1034,303,0x0,0x1,0,0,0
>> 16:49:51 StatsGenerate -1,sergior)
>> 16:49:51 Installation Failed.
> This seems to be the same problem as http://tinyurl.com/2c3tdf, the
> solution
> suggested there is to remove the SQL Server installation, download and
> install MDAC or check that you current configuration is up-to-date and not
> corrupted, then install SQL Server in debug mode "setupsql.exe k=dbg"
> so you can skip over the MDAC installation step.
> John