SQL Server Performance Forum – Threads Archive

T-Sql-Joining two tables

Hi I have two tables Table 1
Col1 Clo2
a xy
a as Table 2
Col1 Clo2
a op
a qr After joining these two tables I want result set as following a xy op
a as qr Would be greate if some one can help me to write query for the same. Regards
Rak****.

---

Select t1.col1,t1.col2,t2.col2 from table1 t1 inner join table2 t2 on t1.col1=t2.col1 Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp
Madhivanan Failing to plan is Planning to fail

---

What about properly designing your tables in the first place?
Add at least PRIMARY KEY constraints. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)

---

Btw Madhivanan, your query returns a wrong resultset. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)

---

Yes it is. rak1791, col1 should be unique to get proper result Madhivanan Failing to plan is Planning to fail

---

What rak1791 wants does not makes sense to me at all. Anyway this gives the result that he wanted
declare @table1 table
(
col1varchar(10),
col2varchar(10)
)
declare @table2 table
(
col1varchar(10),
col2varchar(10)
)
insert into @table1
select’a’, ‘xy’union all
select’a’, ‘as’ insert into @table2
select’a’, ‘op’union all
select’a’, ‘qr’ selectt1.col1, t1.col2, t2.col2
from
(
selectcol1, col2, id = (select count(*) from @table1 x where x.col1 = t.col1 and x.col2 >= t.col2)
[email protected] t
) t1
inner join
(
selectcol1, col2, id = (select count(*) from @table2 x where x.col1 = t.col1 and x.col2 <= t.col2)
[email protected] t
) t2
ont1.id= t2.id /* RESULT :
col1 col2 col2
———- ———- ———-
a xy op
a as qr (2 row(s) affected)
*/ KH

---

But I prefer having identity column in the table than generating and using that in query [<img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail

---

Dear Madhivanan Thanking you for replying me.
Your query will give cross join result. it will give total 8 record as follows a xy op
a as qr
a xy op
a as qr
a xy op
a as qr
a xy op
a as qr I cannot use set rowcount 2. Because I don#%92t know how many records would be in both the tables. Moreover I am looking for a query, which can run without setting rowcount. My ultimate goal is to create a view. Dear FrankKalis Thanking you for replying me. Actually I have 11 tables with all relationship established. I want to create a view involving all tease tables. I have created some sub views from those sub view I would be creating main view. Among those views, two views are like the example I have given. If situation is like the example I have given, how to solve? That#%92s my question.
Regards
Rak

---

Based on your sample data, the resultset should have 4 rows. What does your statement look like? To solve your problem, you should post the table structures, sample data and desired result. Looks like your doing stuff that might be better done at the client or report generator. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)

---

Yea sorry. I will give (2 * 2)4 records.. there was a typing mistake..

---

Post the table structures of table1 and table2. Do your tables have primary or unique keys? Madhivanan Failing to plan is Planning to fail

---

Looks like you need an "unique key" so you can know which is the "first" row to select (as Khtan showed using count). I wonder why the "xy qr" or "as op" missing in the cross join result set. Anyway, here is what I found if I use Madhivanan query. declare @table1 table(col1varchar(10),col2varchar(10))
declare @table2 table(col1varchar(10),col2varchar(10))
insert into @table1 select’a’, ‘xy’union all select’a’, ‘as’
insert into @table2 select’a’, ‘op’union all select’a’, ‘qr’ Select t1.col1,t1.col2,t2.col2
from @table1 t1 inner join @table2 t2 on t1.col1=t2.col1
col1 col2 col2
———- ———- ———-
a xy op
a xy qr
a as op
a as qr
May the Almighty God bless us all!
www.empoweredinformation.com

---

If there is unique query, you will get proper result declare @table1 table(i int identity(1,1), col1 varchar(10), col2 varchar(10))
declare @table2 table(i int identity(1,1), col1 varchar(10), col2 varchar(10))
insert into @table1(col1,col2) select ‘a’, ‘xy’ union all select ‘a’, ‘as’
insert into @table2(col1,col2) select ‘a’, ‘op’ union all select ‘a’, ‘qr’ Select t1.col1,t1.col2,t2.col2
from @table1 t1 inner join @table2 t2 on t1.i=t2.i
Madhivanan Failing to plan is Planning to fail

---

But that’s more or less coincidental. This will easily break when another seed or increment value is chosen, rows are deleted…. Both tables should have a PRIMARY KEY constraint and additionally one table a FOREIGN KEY constraint referencing the other table. So, more or less something like
CREATE TABLE table1
(
i INT identity(1,1) PRIMARY KEY
, col1 VARCHAR(10)
, col2 VARCHAR(10)
)
CREATE TABLE table2
(
id INT IDENTITY(1,1) PRIMARY KEY
, i INT FOREIGN KEY REFERENCES table1(i) ON DELETE CASCADE
, col1 VARCHAR(10)
, col2 VARCHAR(10)
) INSERT INTO table1(col1,col2) SELECT ‘a’, ‘xy’ UNION ALL SELECT ‘a’, ‘as’
INSERT INTO table2(i, col1,col2) SELECT 1, ‘a’, ‘op’ UNION ALL SELECT 2, ‘a’, ‘qr’ SELECT t1.col1,t1.col2,t2.col2
FROM table1 t1
INNER JOIN table2 t2 ON t1.i=t2.i DROP TABLE table2, table1 —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)

---

Yes. Thats good point Frank [<img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail

---

you only need the first instance of the record to join, is that right? I guess the assumption that the "first" instance of the record is assumed with a lower value of the identity. However, if this is not correct, you should have a PRIORITY column where the PRIORITY dictates the order of precedence and not the IDENTITY column. May the Almighty God bless us all!
www.empoweredinformation.com

---

]]>