Nested Select query - Database Discussion Boards

Re: Without using order by [modified]

GuyThiebaut28-Dec-07 0:02

GuyThiebaut

28-Dec-07 0:02

Try something like this:

<br />
CREATE TABLE #MyTempTable (salary decimal(28,12))<br />
INSERT INTO #MyTempTable  <br />
SELECT salary FROM employeedetails<br />
GROUP BY salary <br />
<br />
CREATE INDEX salary <br />
ON #MyTempTable (salary DESC)<br />
<br />
SELECT * FROM #MyTempTable<br />

Regards

Guy

You always pass failure on the way to success.

modified on Friday, December 28, 2007 6:15:09 AM

Re: Without using order by

vimal_yet28-Dec-07 0:54

vimal_yet

28-Dec-07 0:54

Thanks Guy....

It sounds good.and this was the one i guessed....

If U Get Errors U Will Learn
If U Don't Get Errors U Have Learnt

Re: Without using order by

Pete O'Hanlon28-Dec-07 0:34

Pete O'Hanlon

28-Dec-07 0:34

This sounds very suspiciously like homework to me. We don't actually do your homework for you, so you'd better let us know what you've tried beforehand.

Deja View - the feeling that you've seen this post before.

My blog | My articles

Insert text to sql server 2000, single quotation appears occationally

followait27-Dec-07 1:56

followait

27-Dec-07 1:56

for (...) {
  s.Format("%s",toInsert);
  pConn.Execute(...);
}

Sometimes toInsert contains single quotation mark(s), how to do deal with it efficiently?

Thanks very much.

Re: Insert text to sql server 2000, single quotation appears occationally

N a v a n e e t h27-Dec-07 1:58

N a v a n e e t h

27-Dec-07 1:58

followait wrote:
Sometimes toInsert contains single quotation mark(s), how to do deal with it efficiently?

Use parameterized query

All C# applications should call Application.Quit(); in the beginning to avoid any .NET problems.- Unclyclopedia

My Website | Ask smart questions

Re: Insert text to sql server 2000, single quotation appears occationally [modified]

followait27-Dec-07 2:10

followait

27-Dec-07 2:10

Could give me some more guide?

pCmd->CommandText=_bstr_t(L"INSERT INTO tmp(id,a,b,c) "
			L"VALUES(?,?,?,?)");

Like this, how to go on?
Thanks.

modified on Thursday, December 27, 2007 8:22:18 AM

Re: Insert text to sql server 2000, single quotation appears occationally

N a v a n e e t h27-Dec-07 20:32

N a v a n e e t h

27-Dec-07 20:32

followait wrote:
pCmd->CommandText=_bstr_t(L"INSERT INTO tmp(id,a,b,c) "
L"VALUES(?,?,?,?)");

In VC I don't know. C# it could be

INSERT INTO tmp(id,a,b,c) VALUES(@id,@a,@b,@c)
CommandObject.Parameters.Add(@id,value);
CommandObject.Parameters.Add(@a,value);
CommandObject.Parameters.Add(@b,value);
CommandObject.Parameters.Add(@c,value);

All C# applications should call Application.Quit(); in the beginning to avoid any .NET problems.- Unclyclopedia

My Website | Ask smart questions

Nested Select query

Poonam Gandash26-Dec-07 20:50

Poonam Gandash

26-Dec-07 20:50

"select * from (select username,designation, count(*) as points from (select username,designation from messages union all select username,designation from replies) group by username,designation) order by points desc"

this query works fine with MS Access but not with SQL Server.

Through this query I am listing most active members of forum by counting no. of message and replies. Members are 2 types- Students & Professionals

PLZ.... Help..

Re: Nested Select query

Krish - KP26-Dec-07 23:30

Krish - KP

26-Dec-07 23:30

hope you are getting error message at group by clause
if so, try this

select * <br />
from (select username,designation, count(*) as points <br />
      from (select username,designation <br />
	    from messages <br />
	   union all <br />
	    select username,designation <br />
	    from replies<br />
	   )t1 <br />
      group by username,designation<br />
     )t2<br />
order by points desc

Regards
KP

Re: Nested Select query

Poonam Gandash27-Dec-07 0:44

Poonam Gandash

27-Dec-07 0:44

Thanks a lot it worked for me..

I rated u 5

This is 4 u Rose | [Rose]

Re: Nested Select query

andyharman26-Dec-07 23:33

andyharman

26-Dec-07 23:33

Try:

select * from (
  select username, designation, count(*) as points
  from (
    select username, designation from messages
    union all
    select username, designation from replies
  ) as A
  group by username, designation
) as B
order by points desc

You need to provide table aliases. I'm not sure where menber-types fit into this.

Regards
Andy

Re: Nested Select query

vimal_yet27-Dec-07 0:13

vimal_yet

27-Dec-07 0:13

What is the exact purpose of this Query ???

If U Get Errors U Will Learn
If U Don't Get Errors U Have Learnt

Database Design Issue

Mostafa Siraj26-Dec-07 19:57

Mostafa Siraj

26-Dec-07 19:57

I'm building a web application which has lots of users, each with lots of security rules. what is better???
1- to make a table for users and another table for permissions. and insure by coding(in ASP.NET for example) the security
2- to create for each user in the system new user in the DBMS itself and assign the security from the database.

Thanks in Advance

Re: Database Design Issue

vimal_yet27-Dec-07 0:18

vimal_yet

27-Dec-07 0:18

First one is better,because the users are specific to your application not your DB.

if u create a user in db even when ur Application is Down . hte users can access the DB directly and update the DB.

use the Design which is specific to ur application.

If U Get Errors U Will Learn
If U Don't Get Errors U Have Learnt

Re: Database Design Issue

Colin Angus Mackay27-Dec-07 0:30

Colin Angus Mackay

27-Dec-07 0:30

Mostafa Siraj wrote:
I'm building a web application which has lots of users, each with lots of security rules. what is better???
1- to make a table for users and another table for permissions. and insure by coding(in ASP.NET for example) the security
2- to create for each user in the system new user in the DBMS itself and assign the security from the database.

Number 2 is a bad idea because every time you create a user from your ASP.NET application you will have to supply the database with sufficient permission to perform that action. If your application is attacked, do you really want the attacker to have the ability to create users in the database?

Number 2 is also a bad idea from a performance perspective. It means you won't be able to take advantage of the pooling of connections in your ASP.NET application as each user will be using a different connection. If they all use the ASP.NET user connection then you get the advantages of connection pooling.

So, by a process of elimination the answer seems to be #1. However, you can implement some of this in SQL Server by using Stored procedures and passing in a user ID and constraining the data returned based also on the user.

Upcoming FREE developer events:
* Developer Day Scotland

Recent blog posts:
* Follow up on hiring a software developer
* Aarrgghh!! Accidentally pressing the Caps Lock key

My website | blog

Re: Database Design Issue

Adeel Chaudhry27-Dec-07 20:50

Adeel Chaudhry

27-Dec-07 20:50

Hello,

Why dont you try ASP.NET security?

Nice website for some ideas:

http://weblogs.asp.net/scottgu/archive/2006/02/24/ASP.NET-2.0-Membership_2C00_-Roles_2C00_-Forms-Authentication_2C00_-and-Security-Resources-.aspx[^]

Regards,

Adeel

Do rate the reply, if it helps or even if it doesnot, because it helps the members to know, what solved the issue. Thanks.

Re: Database Design Issue

Paul Conrad28-Dec-07 10:25

Paul Conrad

28-Dec-07 10:25

The first is better. Easier to maintain and look at the reason Colin said for #2 in his reply.

"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon

Select statement

tellytub26-Dec-07 16:13

tellytub

26-Dec-07 16:13

Hi all,

I'm currently doing a contract and supplier window form application.

I have this table (Contract) containing the following fields:

Contract_ID INT NOT NULL,
Vendor_ID INT NOT NULL,
DateStart datetime,
DateDue datetime,
PRIMARY KEY(Contract_ID),
FOREIGN KEY (Vendor_ID) REFERENCES Vendor(Vendor_ID)

I would like to do something like select contracts up for renewal in 3 months (my DateDue values range from 2004-04-16 to 2012-10-29).

I only know how to begin with the following:

SQL Select Statement:-
SELECT Contract_ID FROM Contracts WHERE DateDue... blahblahblah (can anyone guide me to continue?)

I am not sure that the above beginning statement is correct..

Any help is greatly appreciated. Thanks! Smile | :)

telly

Re: Select statement

Krish - KP26-Dec-07 17:21

Krish - KP

26-Dec-07 17:21

SELECT Contract_ID <br />
FROM Contracts <br />
WHERE DateDue BETWEEN GetDate() AND DateAdd(mm, 3, GetDate())

Regards
KP

Re: Select statement

tellytub26-Dec-07 21:08

tellytub

26-Dec-07 21:08

Hey, KP!

I tried the code and it works! Thanks for the help. Smile | :)

doing function of search

shufun26-Dec-07 14:29

shufun

26-Dec-07 14:29

anybody can help on the MS SQL Server 2000? i try to run the store procedure below with the parameter -@description which is chinese words. it is using for doing the search function and the results come out with a unrelated result. is it that not supported with chinese words?

CREATE PROCEDURE TG_testChiness
(
@description nvarchar(200)
)
AS

select * from acstk
where description like @description
GO

I'm sure it is just a minor syntax error...

Andy_L_J26-Dec-07 12:23

Andy_L_J

26-Dec-07 12:23

Can someone tell me what is wrong here? I know the answer is simple, but i'm tired,...and its Christmas... Sigh | :sigh:

DECLARE @CatList VARCHAR(100)<br />
SET @CatList = ''''+ 'Hardware' + '''' + ',' + '''' + 'Custom' + ''''<br />
PRINT @CatList

Returns ‘Hardware’,’Custom’

SELECT p.ProductCode<br />
  FROM Product p<br />
    JOIN ProductCategory pc<br />
      ON p.ProductCatID = pc.ProductCatID<br />
  WHERE ProductCategory IN ('''' + @CatList + '''')

(Returns Nothing)

SELECT p.ProductCode<br />
  FROM Product p<br />
    JOIN ProductCategory pc<br />
      ON p.ProductCatID = pc.ProductCatID<br />
  WHERE ProductCategory IN ('Hardware','Custom')

(Returns
ProductCode
HM10WH2700
HM10WH3000
HM10WH3600
...)

Cheers for any advise,

The Cool Code goes inside the box

Re: I'm sure it is just a minor syntax error...

J$26-Dec-07 13:08

26-Dec-07 13:08

Your variable @CatList is being intrepreted as a literal string, so your query is looking for any ProductCategory values that match the string 'Hardware', 'Custom' as opposed to ProductCategory values that happen to be 'Hardware', or 'Custom'

Looks like you need to rethink how you are going to be querying your tables if you are trying to filter for those values.

Re: I'm sure it is just a minor syntax error...

Krish - KP26-Dec-07 17:11

Krish - KP

26-Dec-07 17:11

try this ...

DECLARE @CatList VARCHAR(100)<br />
DECLARE @strSQL VARCHAR(500)<br />
<br />
SET @CatList = ''''+ 'Hardware' + '''' + ',' + '''' + 'Custom' + ''''<br />
PRINT @CatList<br />
<br />
SET @strSQL = 'SELECT p.ProductCode<br />
			FROM Product p<br />
				JOIN ProductCategory pc ON p.ProductCatID = pc.ProductCatID <br />
			WHERE ProductCategory IN (' + @CatList + ')'<br />
<br />
PRINT @strsql<br />
EXECUTE (@strSQL)

Regards
KP

Re: I'm sure it is just a minor syntax error...

Andy_L_J26-Dec-07 18:15

Andy_L_J

26-Dec-07 18:15

Krish - KP wrote:
DECLARE @CatList VARCHAR(100)
DECLARE @strSQL VARCHAR(500)

SET @CatList = ''''+ 'Hardware' + '''' + ',' + '''' + 'Custom' + ''''
PRINT @CatList

SET @strSQL = 'SELECT p.ProductCode
FROM Product p
JOIN ProductCategory pc ON p.ProductCatID = pc.ProductCatID
WHERE ProductCategory IN (' + @CatList + ')'

PRINT @strsql
EXECUTE (@strSQL)

Thanks Krish - As I thought, simple. Big Grin | :-D

The Cool Code is Inside the Box

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.