Database

Harini N K1-Mar-07 0:45

Harini N K

1-Mar-07 0:45

Hi
I have little doubt on your question. Do you want to intersect those two tables based on the column ID ??

If Table1 has {1,2,2,3} and Table 2 {2,2,3,4} then you want {2,3,4} display values from two tables.

Rate this message. Thank you. Harini Smile | :)

Harini N K1-Mar-07 0:54

Harini N K

1-Mar-07 0:54

Hi Parasu,

Your solution is here:

<br />
SELECT TABLEA.* FROM TABLEA<br />
WHERE EXISTS<br />
           (SELECT * FROM TABLEB   WHERE TABLEA.ID = TABLEB.ID)<br />
UNION <br />
SELECT TABLEB.* FROM TABLEB<br />
WHERE EXISTS <br />
   (SELECT * FROM TABLEA WHERE TABLEA.ID = TABLEB.ID)<br />

Please note the difference here:

UNION

This removes duplicate results

UNION ALL

Incorporates all rows into the results, including duplicates. If not specified, duplicate rows are removed.

Hope this solves your problem

Rate this message. Thank you. Harini Smile | :)

Prashant C1-Mar-07 1:11

Prashant C

1-Mar-07 1:11

Thank you very much Harini

How to run script file through .net?

Priya_20071-Mar-07 20:29

Priya_2007

1-Mar-07 20:29

Select * from tablea,tableb where tablea.columnname=tableb.columnname.

Generally Intersection means having data inboth the tables should be displayed.
For this we have to put a column in both tables which contains same data

So, Select * from two tables where columnin tablea=column in tableb are equal

Priya

NanaAM28-Feb-07 21:33

Re: How to run script file through .net?

28-Feb-07 21:33

Is it possible to run a .sql file on sql server through .net code?

Nana

Colin Angus Mackay28-Feb-07 23:35

Colin Angus Mackay

28-Feb-07 23:35

NanaAM wrote:
Is it possible to run a .sql file on sql server through .net code?

Yes.

However, remember that GO is not part of SQL. It is a preprocessor commant to the Query Analyser that tells it where to split the Script into individual batches. You must make the same splits when issuing the SQL via the SqlCommand.

I blogged in more detail about this here[^] - It will show you how to handle the situation with GOs too.

Upcoming events:
* Glasgow: Geek Dinner (5th March)
* Edinburgh: Web Security Conference Day for Windows Developers (12th April)

My: Website | Blog | Photos

Re: How to run script file through .net?

NanaAM1-Mar-07 19:24

Re: How to run script file through .net?

1-Mar-07 19:24

Thanks for your help.
But I wanted to ask whether we can run the script file, the .sql file direclty?
For this I need to store the file contents in string and pass it to commandText

Nana

Colin Angus Mackay1-Mar-07 21:24

Colin Angus Mackay

1-Mar-07 21:24

NanaAM wrote:
But I wanted to ask whether we can run the script file, the .sql file direclty?
For this I need to store the file contents in string and pass it to commandText

Yes, my previous answer shows you how to do that. Please look at the source code to the application to see how that is done.

Upcoming events:
* Glasgow: Geek Dinner (5th March)
* Edinburgh: Web Security Conference Day for Windows Developers (12th April)

My: Website | Blog | Photos

how to get top theree salary getters from table

mohd imran abdul aziz28-Feb-07 19:51

mohd imran abdul aziz

28-Feb-07 19:51

Dear All,
i want to know how to get top three salary getters from the employee(eid , ename, salary) table

i tried this
select top 3 salary from employee order by salary desc

but it gives me top three salary record say there is salary 1000,1200,1300,1300,1500
then my query return me 1500,1300,1200 whereas i want to 1500,1300,1300,1200

how can i do it

please help

thanks

regards
imran khan

Re: how to get top theree salary getters from table

sam#28-Feb-07 20:09

Re: how to get top theree salary getters from table

28-Feb-07 20:09

SELECT * FROM Employee
WHERE (Salary >=SELECT TOP 1 Salary FROM (SELECT DISTINCT TOP 3 Salary FROM Employee ORDER BY Salary DESC) DERIVEDTBL ORDER BY Salary))

Sylvester george2-Mar-07 0:01

Sylvester george

2-Mar-07 0:01

Here is the pur SQL query. This will work irrespective of any RDBMS

select eid , ename, salary from employee
where salary in
(
(select max(salary) from employee),

(select max(salary) from employee
where salary<
(select max(salary) from employee)),

(select max(salary) from employee
where salary<(select max(salary) from employee
where salary<(select max(salary) from employee)))
)
order by salary desc

Regards,
Sylvester G
sylvester_g_m@yahoo.com

How to get script of a table?

NanaAM28-Feb-07 19:08

Re: How to get script of a table?

28-Feb-07 19:08

Hello,

Can someone tell me how can I get the entire script using query of table.
What I mean is, in SQL server, if we right click on a table, and say script table as-create to-New Query Editor Window, we get the entire script of the table.

I want that using a query.
Please help me its urgent.

Nana

sam#28-Feb-07 20:11

Re: How to get script of a table?

28-Feb-07 20:11

please enlighten us more . give us an example

NanaAM1-Mar-07 19:26

Basic Questions on SQL Stored Procedure

1-Mar-07 19:26

Thanks Sam.
I got the answer.
We can user SMO namespace

Nana

Rahithi28-Feb-07 16:16

Rahithi

28-Feb-07 16:16

Hi all,
if we want to use Stored procedures in VB.Net page, then what are the namespaces we have to import?? and how can we implement Transaction management in the stored procedure??

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... "

Re: Basic Questions on SQL Stored Procedure

gauthee1-Mar-07 0:10

gauthee

1-Mar-07 0:10

system.data.sqlclient

transaction can be maintained by simple way of begin transaction and thereby commit transaction or rollback the transaction.

If not you can use the concurrency levels too!!!

Gautham

relationship creation through alter table query

Saira Tanwir28-Feb-07 8:56

Saira Tanwir

28-Feb-07 8:56

hello

i'm trying to create One to One and One to Many relationships between two tables.

can anyone plz tell me the queries for

1. One to One
2. One to Many

supposing i have two table Table1 and Table2.

plz tell query for Onne to One relationship between Table1 and TAble2 and also One to many relationship between Table1 and Table2 through Alter table query(using Sql Server 2000)

Thanks for the time
Saira

Re: relationship creation through alter table query

Mark J. Miller28-Feb-07 10:34

Mark J. Miller

28-Feb-07 10:34

The difference between One-to-One and One-to-Many is mostly conceptual. A query for either will be exactly the same. However, it can be enforced using a combination of a Foreign Key constraint and a Unique constraint. What this will do is enforce the relationship for UPDATE, INSERT and DELETE, but again, not SELECT.

If your schema looks like this:

<br />
CREATE TABLE Table1 (<br />
 [ID] INT,<br />
 BLAH,<br />
 BLAH2<br />
)<br />
<br />
CREATE TABLE Table2 (<br />
 [ID] INT,<br />
 Table1ID INT,<br />
 BLAH,<br />
 BLAH2<br />
)<br />
<br />

Then you would create your constraints like this:

<br />
ALTER TABLE Table2 ADD CONSTRAINT FK_Table2_Table1 FOREIGN KEY(<br />
 Table1ID<br />
) REFERENCES Table1 (<br />
 ID<br />
)<br />
<br />
<br />
ALTER TABLE Table2 ADD CONSTRAINT UK_Table2 UNIQUE(<br />
 Table1ID<br />
)<br />

Re: relationship creation through alter table query

Mark J. Miller28-Feb-07 10:36

Mark J. Miller

28-Feb-07 10:36

Sorry, I forgot to specify. If you have a One-to-Many relationship you would like to enforce then all you need is the FOREIGN KEY constraint. If you have a One-to-One relationship then you need both the FOREIGN KEY and the UNIQUE constraints.

timeStart TimeEnd

bernie_01128-Feb-07 20:53

bernie_011

28-Feb-07 20:53

i have 3 columns in my table the room,timeStart,timeEnd.
For example i have inserted this data
room: room1
timeStart: 2007-03-02 8:00 AM
timeEnd: 2007-03-02 3:00 PM

Here's the data that will not be accepted if try to insert
room: room1
timeStart: 2007-03-02 9:00 AM
timeEnd: 2007-03-02 4:00 PM
because the timestart is between the timeStart and the timeEnd. Room1 will only be occupied after 3:00 PM.

Can you help me with this?.
Thank you very Much

Bernie

Re: timeStart TimeEnd

sam#28-Feb-07 21:19

Cube File Connection string ?

28-Feb-07 21:19

Use Trigger and put your condition there

Re: timeStart TimeEnd

bernie_01128-Feb-07 22:30

bernie_011

28-Feb-07 22:30

Can u show me the posible code for that?..

Is it posible to use the CHECK CONSTRAINT?.

CONSTRAINT chk CHECK (timeStart BETWEEN timeStart AND timeEnd)

Can i use SELECT statement IN CHECK CONSTRAINT?

Bernie

_mubashir28-Feb-07 8:46

_mubashir

28-Feb-07 8:46

Hello All,
I am working in Visual Studio 2003. I want to access local cube (.cub) file with ADODB object. How is this possible?
I am using the following code, but it doesnt seems to work

<br />
Dim cnActive As New ADODB.Connection<br />
Dim rs As ADODB.Recordset<br />
cnActive = New ADODB.Connection<br />
Try<br />
cnActive.Open("provider=msolap;Data Source=c:\temp\mycube.cub;", "", ",0)<br />
rs = cnActive.OpenSchema(ADODB.SchemaEnum.adSchemaCatalogs)<br />
Catch ex As Exception<br />
sError = ex.Message<br />
Exit Sub<br />
End Try<br />

The error is "The operation has failed because of an error in the COM component - unknown error c:\temp\mycube.cub Access Denied. ". I have given full access to this folder but no use. Any ideas?

thanx
Mubashir

Every job is a self portrait of the person who did it.

Incorrect Time in DateTime column with MS Access database

Scott Page28-Feb-07 8:24

Scott Page

28-Feb-07 8:24

I have a table in an MS Access database with a Date/Time datatype for one of the fields. I generated a DataSet using a OledbDataAdapter in the VS.NET 2003 Forms Designer from the table mentioned previously.

Everything works as expected during Fill and Updates. My problem is that the DateTime displays the Date portion correctly, but the Time is constantly 12:00:00 AM, not the time that was inserted.

I recall seeing this problem a few years ago, but I have forgotten the solution and all of my searches come up empty.

Any help would be greatly appreciated!

Thanks,
Scott

"Some people spend an entire lifetime wondering if they made a difference. The Marines don't have that problem."
( President Ronald Reagan)

Re: Incorrect Time in DateTime column with MS Access database

sam#28-Feb-07 20:18