Basic Join Question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Basic Join Question

Author

Topic

Shaper
Starting Member

8 Posts

Posted - 2009-03-04 : 02:47:03

At least I think that's what it is!

I have a table of which a small sample is:

A B
1 2
1 3
2 4
2 5

What I want is to add to this table based on what is in it. For example, within my program I INSERT (A,B) = (2,4) and before moving on I want to search the table for occurrences of B=2 and INSERT the row:

1 4

This seems like a simple problem but when I SELECT the correct row (i.e. 1,2) I'm having issues working out how to add (1,4).

(This is a basic Transitive Closure program)

Cheers for you help!

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-03-04 : 04:46:21

Could you explain some more? Do you want to UPDATE row (1,2) to (1,4), or INSERT (1,4) if (1,2) exists?

Shaper
Starting Member

8 Posts

Posted - 2009-03-04 : 05:34:22

Sorry, re-reading that it isn't as clear as it should be.

Within the program, I find a pair 2,4 and insert it into the table. Upon inserting (A=2,B=4) I then want to find all instances where B=2 and insert the corresponding A value with B=4, in this example the only instance is (1,2).

So to find the A value I have a SELECT:

SELECT A FROM Table WHERE B=2 with the result = 1 (note that I already know the value 4 in the program).

So what I want to do is something like:

INSERT INTO Table(A,B)

(SELECT A FROM Table WHERE B=2),(4)

How do I do an INSERT which uses a SELECT to find one value and then insert it along with the known value?

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2009-03-04 : 05:51:03

are you asking similar to the below one.?

INSERT INTO table(A,B)
(SELECT A,4 FROM @a WHERE B=2)

Karthik

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-04 : 05:57:48

Not very clear. I'll take a shot anyhow.

select s1.a,s.b from 
(
select 1 A, 2 B
union all select 1, 3
union all select 2, 4
union all select 2, 5
)s join
(
select 1 A, 2 B
union all select 1, 3
union all select 2, 4
union all select 2, 5
)s1 on s.a=s1.b

Ifor
Aged Yak Warrior

700 Posts

Posted - 2009-03-04 : 06:19:12

Maybe:

DECLARE @a int, @b int
SELECT @a = 2, @b = 4

INSERT INTO YourTable
SELECT @a, @b
WHERE NOT EXISTS (SELECT * FROM YourTable WHERE A = @a AND B = @b)

INSERT INTO YourTable
SELECT A, @b
FROM YourTable T
WHERE B = @a
    AND NOT EXISTS (SELECT * FROM YourTable T1 WHERE T1.A = T.A AND B = @b)

Shaper
Starting Member

8 Posts

Posted - 2009-03-04 : 07:14:40

are you asking similar to the below one.?

INSERT INTO table(A,B)
(SELECT A,4 FROM @a WHERE B=2)

Karthik

This is looking possibly like what I need. Selecting from @a...does that mean selecting from column a in "Table"?

Further explanation might help:

Just focusing on the 3 pairs (1,2) (2,4) (1,4).

Say I have a node path: 1 -> 2 -> 4

Moving through the list, I add the pair (1,2) then find the next pair (2,4) and add it too. After adding 2,4 I need to find all parents of 2, in this case only 1, and add 1,4.

So as I say above if I could do this:

INSERT INTO Table(A,B)

(SELECT A FROM Table WHERE B=2),(4)

then that would be perfect. However it doesn't like that and tells me there is a syntax error at "4". Why can't I set that value and select the other one?

Shaper
Starting Member

8 Posts

Posted - 2009-03-04 : 07:16:56

I hope that is clearer, it is hard to explain.

Thanks so much for all your help, I will try your suggestions and see what happens when I get to work!

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2009-03-04 : 07:19:56

@a is a table. see the below one which will help you.

declare @a table (a int,b int)
insert into @a values (1 ,2)
insert into @a values (1 ,3)
insert into @a values (2 ,4)
insert into @a values (2 ,5)
select a from @a where b=2

INSERT INTO @a(A,B)

(SELECT A,4 FROM @a WHERE B=2)

select * from @a

Karthik

Shaper
Starting Member

8 Posts

Posted - 2009-03-04 : 07:33:40

The problem with that is that I will have many iterations and will create a lot of those temporary tables. Also, in order to insert (1,2) I would first have to do a select on Table to find that pair before I can insert it into the temp table @a.

Is there a way to Insert 2 values, one of which is found from a SELECT and the other is a set constant?

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-03-04 : 10:01:59

Try this using OUTPUT:

DECLARE @MyTableVar table( a varchar(50),
b varchar(50));
declare @A varchar (50)

INSERT TABLEA(a,b)
OUTPUT INSERTED.a, INSERTED.b
INTO @MyTableVar
VALUES ('2','4');

set @A = (Select a from TABLEA where b =
( Select a FROM @MyTableVar ))
select @A

INSERT INTO a(A,B)
VALUES
(@A,'4')

Shaper
Starting Member

8 Posts

Posted - 2009-03-04 : 23:49:37

I haven't been able to get these solutions working. As far as performance goes, would I lose much if I split the select and insert into different queries like so:

DataSet ds = new DataSet();
sql = "select closure_superconcept from iaso_closure where closure_subconcept = " + Super[ii].ToString() + " AND closure_snomed_release_id = " + LoadNo;
NpgsqlDataAdapter conceptdata = new NpgsqlDataAdapter(sql, conn);
ds.Reset();
conceptdata.Fill(ds);
foreach (DataRow row in ds.Tables[0].Rows)
{
sql = "INSERT INTO iaso_closure(closure_snomed_release_id,closure_subconcept,closure_superconcept) VALUES (" + LoadNo + "," + Sub[ii].ToString() + "," + row["closure_superconcept"].ToString() + ")";
command = new NpgsqlCommand(sql, conn);
command.ExecuteNonQuery();
}

Ifor
Aged Yak Warrior

700 Posts

Posted - 2009-03-05 : 05:12:12

Did you try my queries?

The first one inserts 2,4 if it does not already exist.
The second one inserts 1,4 if it does not already exist.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources