|
feleacks wrote: I would like to know how to compare data in different columns but in the same table.
There are two ways (in my knowledge) to do this: 1) Self joining the table. 2) using sub query with EXISTS keyword.
feleacks wrote: I would like to know if i can check if a value in column B exists in column A.
1)
select distinct x.a , y.b from xx x inner join xx y on x.a=y.b --self joining
2)
select b from xx where <code>exists</code> (select * from xx x1 where xx.b=x1.a) --sub query with exists keyword
Here the sub query does not produce any data set, rather it returns True or False. The outer where checks the existence of the rows returned by the sub query. There is a bit difference between the sub query with the exists keyword and other sub queries. Here is an extraction form SQL Server Books Online
>The keyword EXISTS is not preceded by a column name, constant, or other expression.
>The select list of a subquery introduced by EXISTS almost always consists of an asterisk (*). There is no reason to list column names because you are simply testing for the existence of rows that meet the conditions specified in the subquery.
Alternatively, you can also do it as follows:
select b from xx where b = ANY (select a from xx)
-- modified at 3:02 Tuesday 9th January, 2007
The above lines of code are tested with SQL Server 2000
Regards
________________________________
Success is not something to wait for, its something to work for.
|
|
|
|
|
Hi All,
plase can anyone tell me the new features(few) of SQL server compared to their versions?
Thanks,
Rahi
If you look at what you do not have in life, you don't have anything,
If you look at what you have in life, you have everything... "
|
|
|
|
|
I never heard of SQL server 2003.
The new features of Sql server 2005 are described here[^]
Wout Louwers
|
|
|
|
|
Rahithi wrote: SQL Server 2003
There is no such product. Were you thinking of Windows Server 2003?
|
|
|
|
|
thanks for the Response. actually right now i am doing my job search. in one of the requirements...
they asked for SQLServer 2003 - stored procedures, dts, sql queries. i really don't know much about this 2003 & 2005 versions. i worked on SQL server 7.0, and also on SQL Server2000.
thanks,
Rahi
If you look at what you do not have in life, you don't have anything,
If you look at what you have in life, you have everything... "
|
|
|
|
|
Dear All,
We created a windows application using Sqlserver2000,We have to deploy it at client side. My question is, the client should not be abel to open the database in any way, even if he well known with SqlServer. How do we secure it?
|
|
|
|
|
alav wrote: We created a windows application using Sqlserver2000,We have to deploy it at client side. My question is, the client should not be abel to open the database in any way, even if he well known with SqlServer. How do we secure it?
If you prevent the client from connecting to the database you also prevent the application you deployed on the client from connecting to the database.
There is no such thing as 100% security. If your application can get through then there is a risk that something else can get through also. The solution is to minimise that risk and design the database as if the client has been compromised. Since you are talking about the creation of the windows applicaiton in the past tense I'm guessing it is too late to do much about that. So, what follows are some options you may consider if you were starting again:
* Have the application connect to a specific account on the SQL Server and do not share that information with anyone. Pros: You don't have to give permission to a windows account as the user name and ID are only issued for the one application. Cons: You then have to manage the security of the user name and password which must get installed on the client machine somehow.
* Access the database via a web service. The Web service can then validate all requests to ensure they are acceptable and won't damage the database. The database only needs to give permission to the webservice for access. Pros: A third party arbitrates all requests. Cons: Adds an extra layer that will slow down communication. The web service still has to authenticate the client somehow.
* Stucture your database so no one gets direct access to any of the tables. They must uses stored procedures that validate the input. Pros: The database can go some way to protecting itself. Only a well defined specific set of changes can be made to the database. Cons: Some of the business logic ends up in the database.
|
|
|
|
|
Hi all,
1.can any one please explain me what are this DTS? and why we use this DTS?
2.what is the difference b/w Stored procedure & Function?
3.what are Cursors?
4. what is a join and it's types?
Thanks,
Rahi
If you look at what you do not have in life, you don't have anything,
If you look at what you have in life, you have everything... "
|
|
|
|
|
Rahithi wrote: 1.can any one please explain me what are this DTS? and why we use this DTS?
Data Transformation Services. Supersceded by SSIS (SQL Server Integration Services). It allows you to extract, load and transform data between different databases.
Rahithi wrote: 2.what is the difference b/w Stored procedure & Function?
A function must be deterministic. A stored procedure doesn't have to be.
Stored procedures are more flexible in what you can do within them.
Functions can be used inside a SELECT statement, but stored procedures can't.
e.g.
SELECT * FROM dbo.FunctionName(1, 2, 3)
Rahithi wrote: 3.what are Cursors?
Cursors provide a way to iterate over a result set in a procedural way. They are slow and should be avoided unless you cannot do what is required in a set based operation.
Rahithi wrote: 4. what is a join and it's types?
A join is a way of combining the data in two or more tables into one result set.
For more details see Types of Join[^]
|
|
|
|
|
a google is highly recommended
|
|
|
|
|
Unless you're just asking out of curiosity you'll really need to dig into a good book which addresses each of the topics in detail. Off the top of my head I'd say SQL Server Unleashed would be a good place to start. It should address each and every one of those questions and show you how to use them as well.
|
|
|
|
|
Is there any way to define Optional paramter in SQL server SP.??
CREATE PROC [ EDURE ] procedure_name [ ; number ]
[ { @parameter data_type }
[ VARYING ] [ = default ] [ OUTPUT ]
] [ ,...n ]
[ WITH
{ RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]
[ FOR REPLICATION ]
AS sql_statement [ ...n ]
Thanks
Amit
|
|
|
|
|
Amit Kumar G wrote: Is there any way to define Optional paramter in SQL server SP.??
Yes, and you've already answered your question
Amit Kumar G wrote: [ = default ]
Here is an extract from the documentation, which you obviously have since you've quoted from it:
default
Is a default value for the parameter. If a default is defined, the procedure can be executed without specifying a value for that parameter. The default must be a constant or it can be NULL. It can include wildcard characters (%, _, [], and [^]) if the procedure uses the parameter with the LIKE keyword.
|
|
|
|
|
|
Hi,
To illustrate the problem, let's say we have those two tables:
table1
id cost
===============
1 100.15
2 577
3 214.975
4 894.12
table2
id cost
===============
1 217.13
2 577
3 214.975
4 214.975
I want to select all rows from both tables that don't have an equivalent value in the cost field .. for example, in table1 we have the value 100.15 which doesn't exist in table2 therefore we want it to show in the results, the tricky part here is that if you look at table2 you'll find that the value 214.975 exist twice while it only exists once in table1, I want it to show only once in the results.
To make it clearer, this is an accounting application, we have two accounts that should be identical, so we want to select all amounts from both accounts that have no equivalent in the other account.
Your help is really appreciated
Wal
|
|
|
|
|
check these queries:
for table1:
Select distinct cost<br />
from table1 t1<br />
where (Select count(*) from table2 t2 where t2.cost=t1.cost) <> (Select count(*) from table1 t11 where t11.cost=t1.cost)
results:
100.15
214.975
894.12
for table2:
Select distinct cost<br />
from table2 t2<br />
where (Select count(*) from table1 t1 where t1.cost=t2.cost) <> (Select count(*) from table2 t22 where t22.cost=t2.cost)
results:
214.975
217.13
note that these queries are not great from the performance point of view.
cursors can do the work too..
|
|
|
|
|
hspc wrote: check these queries:
for table1:
Select distinct cost
from table1 t1
where (Select count(*) from table2 t2 where t2.cost=t1.cost) <> (Select count(*) from table1 t11 where t11.cost=t1.cost)
results:
100.15
214.975
894.12
Thanks for your help, but well, actually this is not quite what I'm trying to do. In the results above, 214.975 shouldn't be showing as it already exists in table2, it should only show if it occurs in table1 more times than it occurs in table2 ... e.g. if it occurs 7 times in table1 and 4 times in table2 then it should show 3 times in the results, can that be achieved?
Wal
|
|
|
|
|
Hai,
In my product table
pr_id Pr_ty pro_name
1001 video film au
1002 audio film vd
1003 drinks cocacola
1004 video songs_vd
1005 audio songs_au
i have above records if i select pr_ty i want to display only 3 records like video,audio and drinks only ...
I am new in sql server please help me.....
A.Rajapandian
|
|
|
|
|
SELECT DISTINCT pr_ty FROM Product
|
|
|
|
|
Thanks for your help...
A.Rajapandian
|
|
|
|
|
I have a few fields I would like to have encrypted in the database that will be displayed on a web page but will also be accessed by windows application. What is the best encryption stategy. Use the encryption tools from SQL Server 2005, or the encryption toolset from ASP.net 2.0
Right now I have been playing with using SQL server but trying to understand how to open the keys when I need to decrypt something to display it on the page or on a report. One thought is to put it all in the stored procedure but I dont know what the performance hit would be for that.
Any help or good references would be appreciated.
Thanks.
DW
|
|
|
|
|
How about the System.Security.Cryptography namespace? That way the data is being transferred to the server in encrypted form.
Just a suggestion to consider, I'm by no means an authority on SQL (or ASP for that matter  ).
I have no idea what I just said. But my intentions were sincere.
|
|
|
|
|
Thanks for the reply.
I looked at that but my problem is that I need to be able to run reports using Crystal Reports along with display the information in a web page. So I dont know how to do it without building a separate application to run the CR.
It seems the simplest way to do that would be to use the SQL server encryption.
What do you think?
|
|
|
|
|
I have a table that looks something like this
ID Site_Code Item_Code Some_Other_Stuff....
=======================================================
1 AA 123 ...
2 DD 234 ...
3 GG 567 ...
4 QQ 111 ...
4 FF 123 ...
My problem is this, I need to retrieve all the rows within a group of Site_Codes (basically all the Site_Codes that relate to say, North America), but for each site the Item_Code may reoccur. So, Item "123" appears both in "AA" and "FF" but I only want to retrieve one copy of "123" for the group. To make things a little more complicated though, "123" in "AA" and "FF" may (although they are the same item) have different values in the "Some_Other_Stuff" fields, so I have to decide whether I want the "AA" copy or the "FF" copy of "123".
So, ultimately what I want to do is set a priority order the the site codes. So I'd retrieve all rows with Site_Code = "AA" and then all rows with Site_Code in ("DD","GG",...) that have Item_Codes not in "AA". I'd image I could do this with some creative table joining, but what would be the most efficient way to do this? The database is on Oracle and unfortunately I don't have the option to change the design.
Thanks
|
|
|
|
|
wouldn't something like this work?
Select A.Site_Code, A.Item_Code
FROM MyTable A
WHERE A.Item_Code NOT IN (Select Item_Code FROM MyTable WHERE Site_Code='AA')
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
