Database

Re: Understanding what an inner join query produces

18-Oct-09 1:00

Thanks Eddy, that is exactly the article I was after, have to bookmark it for future reference.

Never underestimate the power of human stupidity
RAH

Eddy Vluggen18-Oct-09 2:23

Eddy Vluggen

18-Oct-09 2:23

You're welcome Smile | :)

Re: Understanding what an inner join query produces

Not Active18-Oct-09 3:34

Not Active

18-Oct-09 3:34

A very good reference book for this subject is Inside Microsoft® SQL Server® 2008: T-SQL Querying[^]

only two letters away from being an asset

Database Synchronization

Member 426726116-Oct-09 19:32

Member 4267261

16-Oct-09 19:32

There are two databases in different locations.one in my local database server and the other in a remote server. I want to synchronize theses two databases so that the data available in both servers are exactly the same .when ever any dml operation takes place on one server then the other should also be replicated .What are the bottlenecks and the step by step procedure of implementation.

Re: Database Synchronization

εїзεїзεїз16-Oct-09 20:05

εїзεїзεїз

16-Oct-09 20:05

try Googling and what problem are you facing bcz its not that much small topic that can be answered in a post. Smile | :)

Best Of Regards,
SOFTDEV
If you have knowledge, let others light their candles at it

Re: Database Synchronization

Mycroft Holmes16-Oct-09 23:02

Find lowest common ancestor in a in a tree using the Adjacency list model

16-Oct-09 23:02

That is a very large topic, you are not going to get an sensible answer in a forum post, as softie said try Google [^]as the first step in your research.

Never underestimate the power of human stupidity
RAH

Jörgen Andersson16-Oct-09 4:28

Jörgen Andersson

16-Oct-09 4:28

I'm having a tree structure using the Adjacency list model, having an ID and ParentID for every node in the tree.

I need to find the lowest common ancestor for two or more IDs.
Anyone that knows how to do this?

(I know how to do it with the path enumeration or nested set model, but that doesn't help me here)

Re: Find lowest common ancestor in a in a tree using the Adjacency list model

Mycroft Holmes16-Oct-09 23:07

Re: Find lowest common ancestor in a in a tree using the Adjacency list model

16-Oct-09 23:07

Jörgen Andersson wrote:
using the Adjacency list model

I'm curious, what is this?
What database are you using?
Does this model dictate the format of your keys?

I use the hierarchyid in SQL Server to store my structures. How you would achieve this is dependant on your data structure.

Never underestimate the power of human stupidity
RAH

Jörgen Andersson17-Oct-09 11:44

Jörgen Andersson

17-Oct-09 11:44

Adjacency_list[^] is what Joe Celko calls what probably is the most common representation of a tree in a database.
Where every node has an ID and also stores the id of its parent.

I'm using Oracle

Re: Find lowest common ancestor in a in a tree using the Adjacency list model

Mycroft Holmes17-Oct-09 13:25

17-Oct-09 13:25

Thank you, I'm sorry not to be able to help, I have not used Oracle in a decade or so.

Never underestimate the power of human stupidity
RAH

Update top row

dptalt16-Oct-09 3:49

dptalt

16-Oct-09 3:49

I want to update the top row in a table. I'm trying to get this to work:

update top (1) mytable set myfield="test"

This does not seem to compile. We have Microsoft SQL Server 2000. How can I accomplish this?

Eddy Vluggen16-Oct-09 4:50

Eddy Vluggen

16-Oct-09 4:50

AFAIK, the update statement does not support the TOP condition. Try something like this;

UPDATE dbo.myTable
   SET myField = 'test'
 WHERE myPrimaryKeyField = (SELECT TOP 1 myPrimaryKeyField FROM dbo.myTable)

Hope this helps Smile | :)

I are Troll Smile | :)

i.j.russell16-Oct-09 10:41

i.j.russell

16-Oct-09 10:41

You should have an ORDER BY clause on the subquery to determine the correct TOP 1.

Not Active16-Oct-09 13:08

Not Active

16-Oct-09 13:08

A more efficient statement would be this.

update Table
set field = new_value
where PK = (select min(PK) from Table)

only two letters away from being an asset

Break Fullname Into First And Last Name

Geoff Williams16-Oct-09 4:56

Geoff Williams

16-Oct-09 4:56

The predicate "TOP 1" applies only to the SELECT statement, not the UPDATE statement.

In order to update just the first row in your table, your UPDATE statement will need to be

UPDATE mytable
SET myfield = "test"
WHERE ((SELECT TOP 1 myfield FROM mytable) = myfield);

(I've split the statement over several lines to improve readability).

MSSQL Question

Wouter Coinless16-Oct-09 3:13

Wouter Coinless

16-Oct-09 3:13

Is there a way to do this:

@FirstId uniqueidentifier = NULL;
@SecondId uniqueidentifier = NULL;

SELECT ..
FROM ..
WHERE
   IF(@FirstId IS NOT NULL)
      BEGIN
          FirstId = @FirstId
      END
   IF(@SecondId IS NOT NULL)
      BEGIN
          AND FirstId = @FirstId
      END

Or a smarter way? Big Grin | :-D

Thanks in advance!

Re: MSSQL Question

Luc Pattyn16-Oct-09 3:58

Luc Pattyn

16-Oct-09 3:58

Hi,

this is what I would try:

... WHERE (@FirstId=null OR FirstId=@FirstId) AND (@SecondId =null OR SecondId =@SecondId )

Luc Pattyn

I only read code that is properly indented, and rendered in a non-proportional font; hint: use PRE tags in forum messages

Local announcement (Antwerp region): Lange Wapper? Neen!

Re: MSSQL Question

Wouter Coinless16-Oct-09 4:37

Wouter Coinless

16-Oct-09 4:37

I dont want to include the Ids if they are null.

Re: MSSQL Question

J4amieC16-Oct-09 4:54

J4amieC

16-Oct-09 4:54

The suggestion given does exactly what you want it to do.

AndyInUK15-Oct-09 23:29

AndyInUK

15-Oct-09 23:29

Hi ,

How can i break Fullname Into First And Last Name.

For instance there is a column with name "Mr James Smith" and i want to break this into 3 columns with Title, FirstName and LastName.

Thank You

Andy_L_J15-Oct-09 23:34

Andy_L_J

15-Oct-09 23:34

http://www.devx.com/tips/Tip/20009[^]

I don't speak Idiot - please talk slowly and clearly

'This space for rent'

Driven to the arms of Heineken by the wife

Eddy Vluggen16-Oct-09 0:01

Eddy Vluggen

16-Oct-09 0:01

Be carefull with those; some people have no title, but a compound First-name, or a surname that's made up out of multiple names. I'd check the first argument from the string against a known list of titles, just to see whether that part was included or whether it's a first name.

I are Troll Smile | :)

dan!sh 16-Oct-09 0:52

dan!sh

16-Oct-09 0:52

Given the various kinds of name people have in various parts of world, you can not be sure that your code will work fine. It will irritate one person with different kind of name.

Still, you can have something predefined like:
1. First two words will go in as first name.
2. Last word will be the Last name.
3. Rest can be treated as middle name.

Beware, Sean Claude Van Dam will be angry by this approach.

It's not necessary to be so stupid, either, but people manage it. - Christian Graus, 2009 AD

J4amieC16-Oct-09 4:54

J4amieC

16-Oct-09 4:54

d@nish wrote:
Sean Claude Van Dam

Jean