|
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 descYou need to provide table aliases. I'm not sure where menber-types fit into this.
Regards
Andy
|
|
|
|
|
What is the exact purpose of this Query ???
If U Get Errors U Will Learn
If U Don't Get Errors U Have Learnt
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
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.
|
|
|
|
|
|
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
|
|
|
|
|
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! 
telly
|
|
|
|
|
SELECT Contract_ID <br />
FROM Contracts <br />
WHERE DateDue BETWEEN GetDate() AND DateAdd(mm, 3, GetDate())
Regards
KP
|
|
|
|
|
Hey, KP!
I tried the code and it works! Thanks for the help.
|
|
|
|
|
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
|
|
|
|
|
Can someone tell me what is wrong here? I know the answer is simple, but i'm tired,...and its Christmas... 
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
|
|
|
|
|
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.
|
|
|
|
|
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
|
|
|
|
|
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. 
The Cool Code is Inside the Box
|
|
|
|
|
Hi friends,
I have a two columns as unitprice and quantity in salesreport table. I am constructing new column by multplying this two column. I gave the name of sales for this new column. I want to ge average of sales column.
I tried below code but program says the column name of sales is invalid.
select unitprice,quantity,unitprice*quantitiy as sales from salesreport
where sales>avg(sales)
|
|
|
|
|
Please don't cross post.
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
select unitprice, quantity, (unitprice*quantitiy) as sales<br />
from salesreport<br />
where (unitprice*quantitiy)> (select avg(unitprice*quantitiy) from salesreport)
|
|
|
|
|
Hi,
How can I can encrypt and decypt the data and stored in a databse. If anyone knows how to do it. Please tell me.
Rock Star
|
|
|
|
|
Hi,
If you are using VS 2005 then you can use classes of System.Security.Cryptography namespace. For eg. you can use TripleDESCryptoServiceProvider to encrypt and decrypt data programatically and store it in database.
Regards,
Ujjaval Modi
Manpower moves wrenches, horsepower moves cars, and the power of the mind moves the world.
|
|
|
|
|
Thanks
Do you hav any link for it so i can get some more information about it.
Rock Star
|
|
|
|
|
Use the Cryptography classes like Ujjaval said. Google should turn up quite a bit in terms of examples.
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
in the stored procedure , generally it is said that these are faster then the queries which we write directly into the code behind(asp.net code file).
i know stored procedure helps in preventing the SQL Query Injections.
Now my Question is how stored procedure are faster then the query which we write directly, I mean what's the background scene behind this.
Yesterday is a canceled check. Tomorrow is a promissory note. Today is the ready cash. USE IT.
|
|
|
|
|
Stored Procedure are complied during the creation of it. But your Queries that you write in the ASP page gets compiled at the time the page executes.there may chance of errors in the quries that u write dynamically.
But SP all the quries are compiled.
If U Get Errors U Will Learn
If U Don't Get Errors U Have Learnt
|
|
|
|
|
Sonia Gupta wrote: generally it is said that these are faster then the queries which we write directly into the code behind(asp.net code file).
It's not
Sonia Gupta wrote: i know stored procedure helps in preventing the SQL Query Injections.
NO. Parameterized queries prevents SQL Injection. Stored Procedures are not for this.
Sonia Gupta wrote: Now my Question is how stored procedure are faster then the query which we write directly, I mean what's the background scene behind this.
SP's are not faster that normal queries if it does only Insert/Delete/Update. Advantages I found with SP's are
1 - Clean separation of queries from code
2 - Easy maintainability
3 - Easy to write long transact SQL statements
4 - Security can be controlled
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
