How to select another table instead of INNER JOIN multi-part identifier - Database Discussion Boards

Re: How to copy existing database in same folder?

help as an alias18-Jul-07 23:12

help as an alias

18-Jul-07 23:12

i m using Access.

yur thankfull

Re: How to copy existing database in same folder?

help as an alias18-Jul-07 23:23

help as an alias

18-Jul-07 23:23

If You can give me coding in C# or Vb.net or you can give me logic Laugh | :laugh:

Re: How to copy existing database in same folder?

originSH18-Jul-07 23:58

originSH

18-Jul-07 23:58

As Colin Angus Mackay said, check out the FileInfo class, the File class would do it too. Both live in System.IO

Re: How to copy existing database in same folder?

help as an alias19-Jul-07 0:01

help as an alias

19-Jul-07 0:01

thank you

Re: How to copy existing database in same folder?

Vasudevan Deepak Kumar19-Jul-07 21:52

Vasudevan Deepak Kumar

19-Jul-07 21:52

It is actually a test for other users on how powerful is thier vocabulary. Remember in the school days English classes, there used to be an exercise where a passage with just hints would be given and We need to rewrite it as a flowing content.

Vasudevan Deepak Kumar
Personal Homepage
Tech Gossips

Change value of existing rows of an identity column

Harini N K18-Jul-07 18:47

Harini N K

18-Jul-07 18:47

Hi experts,

I want to change value of an identity column without dropping table / references / constraints / deleting rows from that table.

I am using SQL server 2005 and I have tried to modify the column as identity seed to NO using MS SQL Server Management Studio but it is hanging.

Is there any SQL statement to modify that column as Identity Seed to NO.?

Harini

Re: Change value of existing rows of an identity column

Mike Dimmick19-Jul-07 0:17

Mike Dimmick

19-Jul-07 0:17

Harini N K wrote:
I am using SQL server 2005 and I have tried to modify the column as identity seed to NO using MS SQL Server Management Studio but it is hanging.

This normally indicates that there are existing connections open that have taken Schema Stability locks on the table. These locks should be shortlived - check for transactions that haven't been committed or rolled back.

To disable the identity feature completely, use ALTER TABLE table ALTER COLUMN column_name data_type. It sounds, though, as if you want to set the identity value of some existing rows, or set the identity value of new rows you're inserting. To do this, use SET IDENTITY_INSERT table ON. It's normally a good idea to turn this off when you're done. Also, use DBCC CHECKIDENT to ensure that the next row inserted which uses the identity value does not get a clashing value.

Stability. What an interesting concept. -- Chris Maunder

How to select another table instead of INNER JOIN multi-part identifier

masyarial18-Jul-07 17:49

masyarial

18-Jul-07 17:49

I use INNER JOIN for a multi-part identifer tbl1 a and tb1 b
But I can't find solution for select another table (exam: tbl2)
This is the code:
select a.field1,..
from tbl1 a
inner join
(select field1,.. from tbl1 group by field1,..) b
on a.field1 = b.field1 and ..
group by a.field1,..

I want to select another field(exam:field3) from tbl2, my code
select a.field1,..,tbl2.field3
from tbl2, tbl1 a
inner join
(select field1,.., from tbl1 group by field1,..) b
on a.field1 = b.field1 and ..
group by a.field1,..,tbl2.field3

The Question is: where I put the code:
a.field1 = tbl2.field1

Thanks a lot !!!

Re: How to select another table instead of INNER JOIN multi-part identifier

Krish - KP18-Jul-07 18:06

Krish - KP

18-Jul-07 18:06

masyarial wrote:
select a.field1,..,tbl2.field3
from tbl2, tbl1 a
inner join
(select field1,.., from tbl1 group by field1,..) b
on a.field1 = b.field1 and ..
group by a.field1,..,tbl2.field3

The Question is: where I put the code:
a.field1 = tbl2.field1

<br />
SELECT a.field1,..,tbl2.field3<br />
FROM tbl2 <br />
    INNER JOIN tbl1 a ON a.field1 = tbl2.field1<br />
    INNER JOIN (select field1,.., from tbl1 group by field1,..) b ON a.field1 = b.field1 and ..<br />
GROUP BY a.field1,..,tbl2.field3<br />

Regards
KP

SQL tutorial

firestoper18-Jul-07 15:54

firestoper

18-Jul-07 15:54

Any advice from you guys where I can find easy to read and digest SQL tutorials?

Thanks a lot
Dom

Re: SQL tutorial

leckey18-Jul-07 16:39

leckey

18-Jul-07 16:39

Your post is VERY general. For basic SQL tutorials just type in a few keywords in this fancy-shmancy new thing called GOOGLE.

_____________________________________________
Flea Market! It's just like...it's just like...A MINI-MALL!

Re: SQL tutorial

firestoper18-Jul-07 17:17

firestoper

18-Jul-07 17:17

Wow thats just great! I never thought we have the same brain waves Smile | :)

I know I have it in me..

You've just enlighten me Smile | :)

Thanks Lecky

Now I can be a gold member, UTFG you guys Smile | :)

SNORE

Dom

Re: SQL tutorial

Sathesh Sakthivel18-Jul-07 17:26

Sathesh Sakthivel

18-Jul-07 17:26

firestoper wrote:
You've just enlighten me

Before Posting the questions in the forum you should get ti cleared by some of our Friends like google and other tutorials.

After that only we could post the question in the forum.

Regards,

Satips. Rose | [Rose]

Don't walk in front of me, I may not follow;
Don't walk behind me, I may not lead;
Walk beside me, and just be my friend. - Albert Camus

Re: SQL tutorial

Rob Graham19-Jul-07 3:42

Rob Graham

19-Jul-07 3:42

Not exactly a way to ensure you can get help in the future. Your sarcastic, rude response will be remembered, and will impact the likelihood that someone will help you in the future.
Clearly you had not read the "how to get help" at the top of the page before you posted, and Lecky was only reminding you quite properly of that.

Good luck in the future. Your crappy attitude should carry you far.

Re: SQL tutorial

Sathesh Sakthivel18-Jul-07 17:10

Sathesh Sakthivel

18-Jul-07 17:10

(1) SQL TUTORIAL[^]

(2) w3schools[^]

Hope this links will give an idea about the sql for you.

And these links cover the basic details.

Regards,

Satips. Rose | [Rose]

Don't walk in front of me, I may not follow;
Don't walk behind me, I may not lead;
Walk beside me, and just be my friend. - Albert Camus

Re: SQL tutorial

firestoper18-Jul-07 17:24

firestoper

18-Jul-07 17:24

Sweet, just what I need Wink | ;)

Thanks Satips Rose | [Rose]

Dom

Re: SQL tutorial

Sathesh Sakthivel18-Jul-07 17:27

Sathesh Sakthivel

18-Jul-07 17:27

Glad to help always Smile | :)

Regards,

Satips. Rose | [Rose]

Don't walk in front of me, I may not follow;
Don't walk behind me, I may not lead;
Walk beside me, and just be my friend. - Albert Camus

Re: SQL tutorial

Krish - KP18-Jul-07 17:25

Krish - KP

18-Jul-07 17:25

http://sqlzoo.net/[^]

hope this would help you

Regards
KP

Can somebody write a stored proc for this problem please?

rajugrover18-Jul-07 8:29

rajugrover

18-Jul-07 8:29

suppose I have a table like this
create table MyTable(
varchar(10) s1,
varchar(10) s2
)
let an example for data in MyTable be
s1 s2
------------------------------
manager1 null
manager2 null
clerk0_1 manager1
clerk1_1 manager1
clerk2_2 manager2
clerk3_1 clerk0_1
clerk4_1 clerk1_1
clerk5_2 clerk2_2
clerk6_1 clerk4_1

the table says that s1 is the boss of s2. if s1 does not have a boss then s2 is null.
(I wrote a skeleton sql statement so that you can easily understand, please can you write a proc or sql statement)

I want a SQL statement like this

select s1 from MyTabe [please fill in here] where s1 = 'manager1'

so that the query will return
s1
---------------
manager1
clerk0_1
clerk1_1
clerk3_1
clerk4_1
clerk6_1
I mean starting from manager1 select recursively down to the last leaf. It is like given a tree select the root and all its leaves and the leafs of that subleaf and so on until all is out. Please note that there is no fixed level. The query ends when all the sub leaves are displayed. Can you please do it please?

Re: Can somebody write a stored proc for this problem please?

Colin Angus Mackay18-Jul-07 8:54

Colin Angus Mackay

18-Jul-07 8:54

rajugrover wrote:
the table says that s1 is the boss of s2. if s1 does not have a boss then s2 is null

Then why not give the columns meaningful names. s1 = employee; s2 = manager

rajugrover wrote:
I mean starting from manager1 select recursively down to the last leaf.

I've done this before - it is a difficult and time consuming query. SQL isn't so good at recursion. What I did was create a trigger so that when data was inserted, updated or deleted a denormalised version of the table was updated. If I needed to know everyone from PersonX downwards I could just query the denormalised table.

The denormalised table contains just two columns the superior and the subordinate. I'm presuming the table you show has more columns. The denormalised version can then be joined with the normalised version to get any additional data.

Unfortunately, off the top of my head, I don't remember how it was implemented so I can't give you code right now. But hopefully the above should guide you in a reasonable direction.

Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...

"I wouldn't say boo to a goose. I'm not a coward, I just realise that it would be largely pointless."

My website

Re: Can somebody write a stored proc for this problem please?

originSH18-Jul-07 22:17

originSH

18-Jul-07 22:17

Colin Angus Mackay wrote:
SQL isn't so good at recursion

THats why I love being able to use CLR stored procs in 2005. I try to stick with TSQL as much as possible but there are some problems, like recursion, that a bit of C# can do wonders with Big Grin | :-D

Re: Can somebody write a stored proc for this problem please?

Pete O'Hanlon18-Jul-07 9:17

Pete O'Hanlon

18-Jul-07 9:17

You aren't going to get anybody to actually do your work for you. It's up to you to figure out how to do it - if you don't, how are you going to be able to maintain it. I will give you a big hint here though, if you are using SQL Server 2005 - take a look at recursive queries with Common Table Expressions (CTE).

Please visit http://www.readytogiveup.com/ and do something special today.
Deja View - the feeling that you've seen this post before.

Re: Can somebody write a stored proc for this problem please?

Paul Conrad18-Jul-07 9:38

Paul Conrad

18-Jul-07 9:38

Pete O'Hanlon wrote:
It's up to you to figure out how to do it - if you don't, how are you going to be able to maintain it.

Very valuable word of wisdom there, Pete.

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

Re: Can somebody write a stored proc for this problem please?

Pete O'Hanlon18-Jul-07 9:46

Pete O'Hanlon

18-Jul-07 9:46

Thanks.

I sometimes wonder at these people. Have they no pride in their work? Do they never have to support the crap they foist on their clients?

Please visit http://www.readytogiveup.com/ and do something special today.
Deja View - the feeling that you've seen this post before.

Re: Can somebody write a stored proc for this problem please?

Paul Conrad18-Jul-07 12:11

Paul Conrad

18-Jul-07 12:11

Pete O'Hanlon wrote:
Have they no pride in their work?

I am not sure. I don't mind helping when someone is stumped, but I do mind when they try and push their work off on people thinking they will get it for free.

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

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