Query within a query

Hi, I am having some logic trouble... I think I want to create a Left Join but I am not sure here is my query...

SELECT Nature.Nature, t.Apr
FROM Nature LEFT JOIN (SELECT NatureCountPerMonth.ComplaintNumber AS Apr, NatureCountPerMonth.Nature FROM NatureCountPerMonth WHERE Month = 4 AS t) ON Nature.Nature = t.Nature

But it doesn't work. How do I nest queries within queries? Is it a syntax problem or is this something that can't be done?

Thanks in advance for any help,Can you explain what you want to achieve?

Did you try this? Does this sql work?

SELECT Nature.Nature, t.Apr
FROM Nature,
(SELECT NatureCountPerMonth.ComplaintNumber AS Apr,
NatureCountPerMonth.Nature
FROM NatureCountPerMonth WHERE Month = 4) t
where Nature.Nature = t.Nature;|||Cheers,
That would be it... I figured you would change the name of a table the same way you change a field. This is what I needed.

Thanks,

query with a table name that has a space...

I have a table name in SQL Server 2000 that has a space in it
ex: aim international

I had trouble just in the query analyzer with this..I had to place the
name in brackets [] for it to work. But now I'm in Visual Studio .Net
2003 and it gives me another problem. I get the table name from a drop
down list selection and send it to a query string. But is gives me this
error:
***************
Line 1: Incorrect syntax near 'AIM international'.
Exception Details: System.Data.SqlClient.SqlException: Line 1:
Incorrect syntax near 'AIM international'.
******************
Here is the string:
****************
Dim sqlStr As String = "SELECT DISTINCT Last_Name FROM '" & PubName &
"' WHERE PostalCode ='" & postalcode & "' And Title='" & title & "'
ORDER BY Last_Name "
**********************

And the variable PubName is the string AIM international .
I tried placing it in brackets like in the query analyzer :
****************
Dim sqlStr As String = "SELECT DISTINCT Last_Name FROM ['" & PubName &
"'] WHERE PostalCode ='" & postalcode & "' And Title='" & title & "'
ORDER BY Last_Name "
*******************

and I get this:
*******************
Invalid object name ''AIIM international''.
Exception Details: System.Data.SqlClient.SqlException: Invalid object
name ''AIIM international''.
*******************

Any idea what I have to do for it to work ? Can I use table names
with spaces or it's just not a good idea?
Thanks for the help guys!!
JMTHi,

> I have a table name in SQL Server 2000 that has a space in it
> ex: aim international
> I had trouble just in the query analyzer with this..I had to place the
> name in brackets [] for it to work. But now I'm in Visual Studio .Net
> 2003 and it gives me another problem. I get the table name from a drop
> down list selection and send it to a query string. But is gives me this
> error:
> ***************
> Line 1: Incorrect syntax near 'AIM international'.
> Exception Details: System.Data.SqlClient.SqlException: Line 1:
> Incorrect syntax near 'AIM international'.
> ******************
> Here is the string:
> ****************
> Dim sqlStr As String = "SELECT DISTINCT Last_Name FROM '" & PubName &
> "' WHERE PostalCode ='" & postalcode & "' And Title='" & title & "'
> ORDER BY Last_Name "
> **********************
> And the variable PubName is the string AIM international .
> I tried placing it in brackets like in the query analyzer :
> ****************
> Dim sqlStr As String = "SELECT DISTINCT Last_Name FROM ['" & PubName &

There's an extra single quote there: [' ... & pubname ...

> "'] WHERE PostalCode ='" & postalcode & "' And Title='" & title & "'
> ORDER BY Last_Name "
> *******************
> and I get this:
> *******************
> Invalid object name ''AIIM international''.
> Exception Details: System.Data.SqlClient.SqlException: Invalid object
> name ''AIIM international''.
> *******************
> Any idea what I have to do for it to work ? Can I use table names
> with spaces or it's just not a good idea?

Well, it's not an good idea either :-)

--
With regards,

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com|||Well thanks alot Martijn,

Thoses extra quotes really made a difference. Now it all works!!
here's the new string:
Dim sqlStr As String = "SELECT DISTINCT Last_Name FROM [" & PubName &
"] WHERE PostalCode ='" & postalcode & "' And Title='" & title & "'
ORDER BY Last_Name "

Beautiful!! I love it when things end up working!!
Thanks again!
JMT|||vbnetrookie (bigjmt@.hotmail.com) writes:
> Well thanks alot Martijn,
> Thoses extra quotes really made a difference. Now it all works!!
> here's the new string:
> Dim sqlStr As String = "SELECT DISTINCT Last_Name FROM [" & PubName &
> "] WHERE PostalCode ='" & postalcode & "' And Title='" & title & "'
> ORDER BY Last_Name "

And now for title enter the following string:

' DROP TABLE [AIM International] --

As a safety precaution, make sure that you have a backup of your database
available.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I'm not sure what you mean :
enter the following string for title ?
WHat will this do and why add that line if it all works now?
I'm alaways willing to learn new stuff so i'm all ears!!
JMT|||vbnetrookie (bigjmt@.hotmail.com) writes:
> I'm not sure what you mean :
> enter the following string for title ?
> WHat will this do and why add that line if it all works now?
> I'm alaways willing to learn new stuff so i'm all ears!!

Presumably title comes from an input field. And it is into this input
field you should enter this string and learn what happens. You may
think you are searching for some data, but in fact you will blow away
your table.

This is something which is called "SQL injection". By entering SQL commands
into an input field, an intruder might be able to do things your database
that you did not intend. This is particular a danger if that input field
in on a web site. The trick is simple: Use an ' to close the string and
also a -- at the end to kill the syntax that comes after the query.

The remedy for this problem is simple: rather than building the complete
SQL statement, you use a parameterised statement:

Dim sqlStr As String = "SELECT DISTINCT Last_Name FROM [" & PubName &
"] WHERE PostalCode = @.postalcode And Title= @.title "

You then add the parameters with .AddParameter which I believe is on
the command object. (I'm not a very frequent ADO .Net programmer, so
I don't remember the details.) I encourage you to look it up. And I
cannot stress enough that this is essential stuff.

As you may note, I did not use a parameter for the table name; This is
because table names cannot be parameterized. Usually if you find the need
to determine the table name dynamically, this is a strong indiciation of
a poor database design.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> now for title enter the following string:
>' DROP TABLE [AIM International] --

Well, technically you'd need:
[AIM International]; DROP TABLE [AIM International] --

You have to finish that first SQL statement before you can in inject
new code.

I agree that if the table name is a parameter it indicates that the
database is not normalized, but if you *had* to do it that way then I'd
suggest putting the table names into an array and just passing as input
an index to the array (and of course doing bounds checking on it.)

QUERY TROUBLE

I am using the last updated date field to give me the dates I want, but I
want to narrow that search to give me only the greatest date per project id.
Here is my query:
SELECT POReqHdr.ProjectID, POReqHdr.User5, POReqHdr.PONbr,
POReqHdr.CuryTotalExtCost, POReqHdr.Descr, POReqHdr.CuryPrevPOTotal,
POReqHdr.CuryReqTotal, POReqHdr.User2,
POReqHdr.LUpd_DateTime, PJPROJ.project_desc
FROM POReqHdr LEFT OUTER JOIN
PJPROJ ON POReqHdr.ProjectID = PJPROJ.project
WHERE (POReqHdr.User5 IN (@.reason)) AND (POReqHdr.ProjectID = 'HE017')
AND (MONTH(POReqHdr.LUpd_DateTime) = @.MONTH)
I know I need to use "max(lupd_datetime)" somewhere but I am not sure how.
The is in a sql report, by the way.Hi,
Inside the sub query you can use the MAX function. See the below sample:-
select projid, lupd_datetime from projects x
where lupd_datetime >= (select max(y.lupd_datetime) from projects y
where y.projid = x.projid)
Thanks
Hari
SQL Server MVP
"Ben Watts" wrote:

> I am using the last updated date field to give me the dates I want, but I
> want to narrow that search to give me only the greatest date per project i
d.
> Here is my query:
> SELECT POReqHdr.ProjectID, POReqHdr.User5, POReqHdr.PONbr,
> POReqHdr.CuryTotalExtCost, POReqHdr.Descr, POReqHdr.CuryPrevPOTotal,
> POReqHdr.CuryReqTotal, POReqHdr.User2,
> POReqHdr.LUpd_DateTime, PJPROJ.project_desc
> FROM POReqHdr LEFT OUTER JOIN
> PJPROJ ON POReqHdr.ProjectID = PJPROJ.project
> WHERE (POReqHdr.User5 IN (@.reason)) AND (POReqHdr.ProjectID = 'HE017')
> AND (MONTH(POReqHdr.LUpd_DateTime) = @.MONTH)
> I know I need to use "max(lupd_datetime)" somewhere but I am not sure how.
> The is in a sql report, by the way.
>
>|||Ben Watts wrote:
> I am using the last updated date field to give me the dates I want, but I
> want to narrow that search to give me only the greatest date per project i
d.
> Here is my query:
> SELECT POReqHdr.ProjectID, POReqHdr.User5, POReqHdr.PONbr,
> POReqHdr.CuryTotalExtCost, POReqHdr.Descr, POReqHdr.CuryPrevPOTotal,
> POReqHdr.CuryReqTotal, POReqHdr.User2,
> POReqHdr.LUpd_DateTime, PJPROJ.project_desc
> FROM POReqHdr LEFT OUTER JOIN
> PJPROJ ON POReqHdr.ProjectID = PJPROJ.project
> WHERE (POReqHdr.User5 IN (@.reason)) AND (POReqHdr.ProjectID = 'HE017')
> AND (MONTH(POReqHdr.LUpd_DateTime) = @.MONTH)
> I know I need to use "max(lupd_datetime)" somewhere but I am not sure how.
> The is in a sql report, by the way.
>
I have a short post on my web site explaining how to do this, but the
site is currently being reconstructed. You can find the original post
in Google's cache by searching for
"www.realsqlguy.com/twiki/bin/view/RealSQLGuy/FindingTheLatestValue"
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I put that query in and it told me there was an error on the report server.
Here is my last query.
SELECT POReqHdr.ProjectID, POReqHdr.User5, POReqHdr.PONbr,
POReqHdr.CuryTotalExtCost, POReqHdr.Descr, POReqHdr.CuryPrevPOTotal,
POReqHdr.CuryReqTotal, POReqHdr.User2,
POReqHdr.LUpd_DateTime, PJPROJ.project_desc
FROM POReqHdr LEFT OUTER JOIN
PJPROJ ON POReqHdr.ProjectID = PJPROJ.project
WHERE (POReqHdr.User5 IN (@.reason)) AND (POReqHdr.ProjectID = 'HE017')
AND (MONTH(POReqHdr.LUpd_DateTime) = @.MONTH) AND
(POReqHdr.LUpd_DateTime >=
(SELECT MAX(LUpd_DateTime) AS Expr1
FROM POReqHdr AS POReqHdr_1
WHERE (ProjectID = PJPROJ.project)))
"Hari Prasad" <HariPrasad@.discussions.microsoft.com> wrote in message
news:9330F636-358C-462C-8030-1CFA88035CC9@.microsoft.com...[vbcol=seagreen]
> Hi,
> Inside the sub query you can use the MAX function. See the below sample:-
> select projid, lupd_datetime from projects x
> where lupd_datetime >= (select max(y.lupd_datetime) from projects
> y
> where y.projid = x.projid)
> Thanks
> Hari
> SQL Server MVP
> "Ben Watts" wrote:
>|||never mind, it worked. Thanks very much
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:44E31BD4.60003@.realsqlguy.com...
> Ben Watts wrote:
> I have a short post on my web site explaining how to do this, but the site
> is currently being reconstructed. You can find the original post in
> Google's cache by searching for
> "www.realsqlguy.com/twiki/bin/view/RealSQLGuy/FindingTheLatestValue"
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

QUERY TROUBLE

I am using the last updated date field to give me the dates I want, but I
want to narrow that search to give me only the greatest date per project id.
Here is my query:
SELECT POReqHdr.ProjectID, POReqHdr.User5, POReqHdr.PONbr,
POReqHdr.CuryTotalExtCost, POReqHdr.Descr, POReqHdr.CuryPrevPOTotal,
POReqHdr.CuryReqTotal, POReqHdr.User2,
POReqHdr.LUpd_DateTime, PJPROJ.project_desc
FROM POReqHdr LEFT OUTER JOIN
PJPROJ ON POReqHdr.ProjectID = PJPROJ.project
WHERE (POReqHdr.User5 IN (@.reason)) AND (POReqHdr.ProjectID = 'HE017')
AND (MONTH(POReqHdr.LUpd_DateTime) = @.MONTH)
I know I need to use "max(lupd_datetime)" somewhere but I am not sure how.
The is in a sql report, by the way.Hi,
Inside the sub query you can use the MAX function. See the below sample:-
select projid, lupd_datetime from projects x
where lupd_datetime >= (select max(y.lupd_datetime) from projects y
where y.projid = x.projid)
Thanks
Hari
SQL Server MVP
"Ben Watts" wrote:
> I am using the last updated date field to give me the dates I want, but I
> want to narrow that search to give me only the greatest date per project id.
> Here is my query:
> SELECT POReqHdr.ProjectID, POReqHdr.User5, POReqHdr.PONbr,
> POReqHdr.CuryTotalExtCost, POReqHdr.Descr, POReqHdr.CuryPrevPOTotal,
> POReqHdr.CuryReqTotal, POReqHdr.User2,
> POReqHdr.LUpd_DateTime, PJPROJ.project_desc
> FROM POReqHdr LEFT OUTER JOIN
> PJPROJ ON POReqHdr.ProjectID = PJPROJ.project
> WHERE (POReqHdr.User5 IN (@.reason)) AND (POReqHdr.ProjectID = 'HE017')
> AND (MONTH(POReqHdr.LUpd_DateTime) = @.MONTH)
> I know I need to use "max(lupd_datetime)" somewhere but I am not sure how.
> The is in a sql report, by the way.
>
>|||Ben Watts wrote:
> I am using the last updated date field to give me the dates I want, but I
> want to narrow that search to give me only the greatest date per project id.
> Here is my query:
> SELECT POReqHdr.ProjectID, POReqHdr.User5, POReqHdr.PONbr,
> POReqHdr.CuryTotalExtCost, POReqHdr.Descr, POReqHdr.CuryPrevPOTotal,
> POReqHdr.CuryReqTotal, POReqHdr.User2,
> POReqHdr.LUpd_DateTime, PJPROJ.project_desc
> FROM POReqHdr LEFT OUTER JOIN
> PJPROJ ON POReqHdr.ProjectID = PJPROJ.project
> WHERE (POReqHdr.User5 IN (@.reason)) AND (POReqHdr.ProjectID = 'HE017')
> AND (MONTH(POReqHdr.LUpd_DateTime) = @.MONTH)
> I know I need to use "max(lupd_datetime)" somewhere but I am not sure how.
> The is in a sql report, by the way.
>
I have a short post on my web site explaining how to do this, but the
site is currently being reconstructed. You can find the original post
in Google's cache by searching for
"www.realsqlguy.com/twiki/bin/view/RealSQLGuy/FindingTheLatestValue"
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I put that query in and it told me there was an error on the report server.
Here is my last query.
SELECT POReqHdr.ProjectID, POReqHdr.User5, POReqHdr.PONbr,
POReqHdr.CuryTotalExtCost, POReqHdr.Descr, POReqHdr.CuryPrevPOTotal,
POReqHdr.CuryReqTotal, POReqHdr.User2,
POReqHdr.LUpd_DateTime, PJPROJ.project_desc
FROM POReqHdr LEFT OUTER JOIN
PJPROJ ON POReqHdr.ProjectID = PJPROJ.project
WHERE (POReqHdr.User5 IN (@.reason)) AND (POReqHdr.ProjectID = 'HE017')
AND (MONTH(POReqHdr.LUpd_DateTime) = @.MONTH) AND
(POReqHdr.LUpd_DateTime >= (SELECT MAX(LUpd_DateTime) AS Expr1
FROM POReqHdr AS POReqHdr_1
WHERE (ProjectID = PJPROJ.project)))
"Hari Prasad" <HariPrasad@.discussions.microsoft.com> wrote in message
news:9330F636-358C-462C-8030-1CFA88035CC9@.microsoft.com...
> Hi,
> Inside the sub query you can use the MAX function. See the below sample:-
> select projid, lupd_datetime from projects x
> where lupd_datetime >= (select max(y.lupd_datetime) from projects
> y
> where y.projid = x.projid)
> Thanks
> Hari
> SQL Server MVP
> "Ben Watts" wrote:
>> I am using the last updated date field to give me the dates I want, but I
>> want to narrow that search to give me only the greatest date per project
>> id.
>> Here is my query:
>> SELECT POReqHdr.ProjectID, POReqHdr.User5, POReqHdr.PONbr,
>> POReqHdr.CuryTotalExtCost, POReqHdr.Descr, POReqHdr.CuryPrevPOTotal,
>> POReqHdr.CuryReqTotal, POReqHdr.User2,
>> POReqHdr.LUpd_DateTime, PJPROJ.project_desc
>> FROM POReqHdr LEFT OUTER JOIN
>> PJPROJ ON POReqHdr.ProjectID = PJPROJ.project
>> WHERE (POReqHdr.User5 IN (@.reason)) AND (POReqHdr.ProjectID =>> 'HE017')
>> AND (MONTH(POReqHdr.LUpd_DateTime) = @.MONTH)
>> I know I need to use "max(lupd_datetime)" somewhere but I am not sure
>> how.
>> The is in a sql report, by the way.
>>|||never mind, it worked. Thanks very much
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:44E31BD4.60003@.realsqlguy.com...
> Ben Watts wrote:
>> I am using the last updated date field to give me the dates I want, but I
>> want to narrow that search to give me only the greatest date per project
>> id. Here is my query:
>> SELECT POReqHdr.ProjectID, POReqHdr.User5, POReqHdr.PONbr,
>> POReqHdr.CuryTotalExtCost, POReqHdr.Descr, POReqHdr.CuryPrevPOTotal,
>> POReqHdr.CuryReqTotal, POReqHdr.User2,
>> POReqHdr.LUpd_DateTime, PJPROJ.project_desc
>> FROM POReqHdr LEFT OUTER JOIN
>> PJPROJ ON POReqHdr.ProjectID = PJPROJ.project
>> WHERE (POReqHdr.User5 IN (@.reason)) AND (POReqHdr.ProjectID =>> 'HE017') AND (MONTH(POReqHdr.LUpd_DateTime) = @.MONTH)
>> I know I need to use "max(lupd_datetime)" somewhere but I am not sure
>> how. The is in a sql report, by the way.
> I have a short post on my web site explaining how to do this, but the site
> is currently being reconstructed. You can find the original post in
> Google's cache by searching for
> "www.realsqlguy.com/twiki/bin/view/RealSQLGuy/FindingTheLatestValue"
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

Query to report database user role membership

Hi All,

I using sql server 2005 sp2. I'm trying to construct a query that'll show me all roles and the users that are in the roles. I'm having trouble figuring how sql server stores this information in the system tables. I think it's based around the database_principals system tables but haven't been able to display the info i need.

If anyone has a query like this can they please help me?

thanks,
Dave
I figured it out. I found the sys.database_role_members which had the info i was after:

select distinct c.name, b.name from sys.database_role_members a
inner join sys.database_principals b on b.principal_id = a.member_principal_id
inner join sys.database_principals c on c.principal_id = a.role_principal_id
and b.type <> 'R

Query to report database user role membership

Hi All,

I using sql server 2005 sp2. I'm trying to construct a query that'll show me all roles and the users that are in the roles. I'm having trouble figuring how sql server stores this information in the system tables. I think it's based around the database_principals system tables but haven't been able to display the info i need.

If anyone has a query like this can they please help me?

thanks,
Dave
I figured it out. I found the sys.database_role_members which had the info i was after:

select distinct c.name, b.name from sys.database_role_members a
inner join sys.database_principals b on b.principal_id = a.member_principal_id
inner join sys.database_principals c on c.principal_id = a.role_principal_id
and b.type <> 'R