Query users in a Security Group with LDAP

I have a linked server set up and working correctly. I can create a query to get all the users from active directory with something like this:

SELECT [name], [samaccountname] from OpenQuery( ADSI,
'SELECT name, samaccountname FROM ''LDAP://DC=domain,DC=com'' WHERE objectClass = ''user'' and objectCategory=''Person''')

Now I am trying to select all the users in a specifed security group, but I am not having much luck. What is the best way to get this?

Thanks much.If that can't be done, is there anyway to check if a user is a member of a group or not through a linked server?sql

query two tables to get data

Does anyone know how to query two tables to get data? I need something
like this
select * from registrations where regstatus='R' and select * from
classtop where ClassID equals the value of the first select statement.
So for exmaple:
The first select Statement will give me from registration:
id, ClassID, RegStatus, Date, UserID
I need the second select statement to give me from classtop:
(id=ClassID from first query)
id, ModuleID, Date, location
Thanks in advance for your help
Lisa
peashoe@.yahoo.com wrote:
> Does anyone know how to query two tables to get data? I need something
> like this
> select * from registrations where regstatus='R' and select * from
> classtop where ClassID equals the value of the first select statement.
> So for exmaple:
> The first select Statement will give me from registration:
> id, ClassID, RegStatus, Date, UserID
> I need the second select statement to give me from classtop:
> (id=ClassID from first query)
> id, ModuleID, Date, location
> Thanks in advance for your help
> Lisa
>
SELECT
Reg.ID,
Reg.ClassID,
Reg.RegStatus,
Reg.Date,
Reg.UserID,
Class.ID,
Class.ModuleID,
Class.Date,
Class.Location
FROM dbo.Registration AS Reg
INNER JOIN dbo.ClassTop AS Class
ON Reg.ClassID = Class.ID
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||use inner join
select * form registrations reg inner join classtop cla on cla.ClassID
=reg.ClassID
will get u data fro both table that matches ClassID
vinu
"peashoe@.yahoo.com" wrote:

> Does anyone know how to query two tables to get data? I need something
> like this
> select * from registrations where regstatus='R' and select * from
> classtop where ClassID equals the value of the first select statement.
> So for exmaple:
> The first select Statement will give me from registration:
> id, ClassID, RegStatus, Date, UserID
> I need the second select statement to give me from classtop:
> (id=ClassID from first query)
> id, ModuleID, Date, location
> Thanks in advance for your help
> Lisa
>

query two tables to get data

Does anyone know how to query two tables to get data? I need something
like this
select * from registrations where regstatus='R' and select * from
classtop where ClassID equals the value of the first select statement.
So for exmaple:
The first select Statement will give me from registration:
id, ClassID, RegStatus, Date, UserID
I need the second select statement to give me from classtop:
(id=ClassID from first query)
id, ModuleID, Date, location
Thanks in advance for your help
Lisapeashoe@.yahoo.com wrote:
> Does anyone know how to query two tables to get data? I need something
> like this
> select * from registrations where regstatus='R' and select * from
> classtop where ClassID equals the value of the first select statement.
> So for exmaple:
> The first select Statement will give me from registration:
> id, ClassID, RegStatus, Date, UserID
> I need the second select statement to give me from classtop:
> (id=ClassID from first query)
> id, ModuleID, Date, location
> Thanks in advance for your help
> Lisa
>
SELECT
Reg.ID,
Reg.ClassID,
Reg.RegStatus,
Reg.Date,
Reg.UserID,
Class.ID,
Class.ModuleID,
Class.Date,
Class.Location
FROM dbo.Registration AS Reg
INNER JOIN dbo.ClassTop AS Class
ON Reg.ClassID = Class.ID
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||use inner join
select * form registrations reg inner join classtop cla on cla.ClassID
=reg.ClassID
will get u data fro both table that matches ClassID
vinu
"peashoe@.yahoo.com" wrote:

> Does anyone know how to query two tables to get data? I need something
> like this
> select * from registrations where regstatus='R' and select * from
> classtop where ClassID equals the value of the first select statement.
> So for exmaple:
> The first select Statement will give me from registration:
> id, ClassID, RegStatus, Date, UserID
> I need the second select statement to give me from classtop:
> (id=ClassID from first query)
> id, ModuleID, Date, location
> Thanks in advance for your help
> Lisa
>sql

Query top x with sum

Hello everyone,
I'm making a query that is supposed to sum the 3 best results.
The query is this:
SELECT top 3 classificacao_principal.id_equipa AS equipa, SUM(classificacao_principal.posicao) AS soma_pontos
FROM classificacao_principal
INNER JOIN atleta ON classificacao_principal.dorsal = atleta.dorsal_atl
WHERE (atleta.sexo_atl = 'F' AND classificacao_principal.id_equipa='46')
GROUP BY classificacao_principal.id_equipa
ORDER BY soma_pontos ASC
This query resturns me the top 3 results, but the SUM is made with all the results available. What i want is to sum only the 3 best results.
Any ideas?
Try,
select
id_equipa,
sum(posicao) as soma_pontos
from
(
SELECT top 3
classificacao_principal.id_equipa AS equipa,
classificacao_principal.posicao
FROM
classificacao_principal
INNER JOIN
atleta
ON classificacao_principal.dorsal = atleta.dorsal_atl
WHERE
atleta.sexo_atl = 'F'
AND classificacao_principal.id_equipa='46'
order by
classificacao_principal.posicao asc
) as t1
group by
id_equipa
go
This solution does not take care about ties.
AMB
"Jorge Ferreira" wrote:

> Hello everyone,
> I'm making a query that is supposed to sum the 3 best results.
> The query is this:
> SELECT top 3 classificacao_principal.id_equipa AS equipa, SUM(classificacao_principal.posicao) AS soma_pontos
> FROM classificacao_principal
> INNER JOIN atleta ON classificacao_principal.dorsal = atleta.dorsal_atl
> WHERE (atleta.sexo_atl = 'F' AND classificacao_principal.id_equipa='46')
> GROUP BY classificacao_principal.id_equipa
> ORDER BY soma_pontos ASC
> This query resturns me the top 3 results, but the SUM is made with all the results available. What i want is to sum only the 3 best results.
> Any ideas?
|||Your ORDER BY soma_pontos applying the TOP cluase to the
SUM(classificacao_principal.posicao) not to
classificacao_principal.id_equipa
Try removing the Order by and see if it works.
Thanks
Ravi
"Jorge Ferreira" wrote:

> Hello everyone,
> I'm making a query that is supposed to sum the 3 best results.
> The query is this:
> SELECT top 3 classificacao_principal.id_equipa AS equipa, SUM(classificacao_principal.posicao) AS soma_pontos
> FROM classificacao_principal
> INNER JOIN atleta ON classificacao_principal.dorsal = atleta.dorsal_atl
> WHERE (atleta.sexo_atl = 'F' AND classificacao_principal.id_equipa='46')
> GROUP BY classificacao_principal.id_equipa
> ORDER BY soma_pontos ASC
> This query resturns me the top 3 results, but the SUM is made with all the results available. What i want is to sum only the 3 best results.
> Any ideas?
|||Thats it.
Thank you for your answer.
Jorge
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> escreveu na
mensagem news:3D7AF53E-0A69-47AE-B66A-62E07C4B3EE5@.microsoft.com...
> Try,
> select
> id_equipa,
> sum(posicao) as soma_pontos
> from
> (
> SELECT top 3
> classificacao_principal.id_equipa AS equipa,
> classificacao_principal.posicao
> FROM
> classificacao_principal
> INNER JOIN
> atleta
> ON classificacao_principal.dorsal = atleta.dorsal_atl
> WHERE
> atleta.sexo_atl = 'F'
> AND classificacao_principal.id_equipa='46'
> order by
> classificacao_principal.posicao asc
> ) as t1
> group by
> id_equipa
> go
> This solution does not take care about ties.
>
> AMB
> "Jorge Ferreira" wrote:
>

Query top x with sum

Hello everyone,
I'm making a query that is supposed to sum the 3 best results.
The query is this:
SELECT top 3 classificacao_principal.id_equipa AS equipa, SUM(classificacao_
principal.posicao) AS soma_pontos
FROM classificacao_principal
INNER JOIN atleta ON classificacao_principal.dorsal = atleta.dorsal_atl
WHERE (atleta.sexo_atl = 'F' AND classificacao_principal.id_equipa='46')
GROUP BY classificacao_principal.id_equipa
ORDER BY soma_pontos ASC
This query resturns me the top 3 results, but the SUM is made with all the r
esults available. What i want is to sum only the 3 best results.
Any ideas?Try,
select
id_equipa,
sum(posicao) as soma_pontos
from
(
SELECT top 3
classificacao_principal.id_equipa AS equipa,
classificacao_principal.posicao
FROM
classificacao_principal
INNER JOIN
atleta
ON classificacao_principal.dorsal = atleta.dorsal_atl
WHERE
atleta.sexo_atl = 'F'
AND classificacao_principal.id_equipa='46'
order by
classificacao_principal.posicao asc
) as t1
group by
id_equipa
go
This solution does not take care about ties.
AMB
"Jorge Ferreira" wrote:

> Hello everyone,
> I'm making a query that is supposed to sum the 3 best results.
> The query is this:
> SELECT top 3 classificacao_principal.id_equipa AS equipa, SUM(classificaca
o_principal.posicao) AS soma_pontos
> FROM classificacao_principal
> INNER JOIN atleta ON classificacao_principal.dorsal = atleta.dorsal_atl
> WHERE (atleta.sexo_atl = 'F' AND classificacao_principal.id_equipa='46')
> GROUP BY classificacao_principal.id_equipa
> ORDER BY soma_pontos ASC
> This query resturns me the top 3 results, but the SUM is made with all the
results available. What i want is to sum only the 3 best results.
> Any ideas?|||Your ORDER BY soma_pontos applying the TOP cluase to the
SUM(classificacao_principal.posicao) not to
classificacao_principal.id_equipa
Try removing the Order by and see if it works.
--
Thanks
Ravi
"Jorge Ferreira" wrote:

> Hello everyone,
> I'm making a query that is supposed to sum the 3 best results.
> The query is this:
> SELECT top 3 classificacao_principal.id_equipa AS equipa, SUM(classificaca
o_principal.posicao) AS soma_pontos
> FROM classificacao_principal
> INNER JOIN atleta ON classificacao_principal.dorsal = atleta.dorsal_atl
> WHERE (atleta.sexo_atl = 'F' AND classificacao_principal.id_equipa='46')
> GROUP BY classificacao_principal.id_equipa
> ORDER BY soma_pontos ASC
> This query resturns me the top 3 results, but the SUM is made with all the
results available. What i want is to sum only the 3 best results.
> Any ideas?|||Thats it.
Thank you for your answer.
Jorge
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> escreveu na
mensagem news:3D7AF53E-0A69-47AE-B66A-62E07C4B3EE5@.microsoft.com...
> Try,
> select
> id_equipa,
> sum(posicao) as soma_pontos
> from
> (
> SELECT top 3
> classificacao_principal.id_equipa AS equipa,
> classificacao_principal.posicao
> FROM
> classificacao_principal
> INNER JOIN
> atleta
> ON classificacao_principal.dorsal = atleta.dorsal_atl
> WHERE
> atleta.sexo_atl = 'F'
> AND classificacao_principal.id_equipa='46'
> order by
> classificacao_principal.posicao asc
> ) as t1
> group by
> id_equipa
> go
> This solution does not take care about ties.
>
> AMB
> "Jorge Ferreira" wrote:
>
>