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.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 About Join
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

krjaga
Starting Member

United Kingdom
3 Posts

Posted - 03/15/2005 :  12:28:58  Show Profile
Hi,

I am having two Tables. In Which i have to join these tables.
In fact if we join the tables, the result set from two table which are matching will be concatenated in a single row.

But, here i need two get results in a separate rows..is it possible ???

Ex :
Table 1:

Id Name
1 Jack
2 Philip

Table 2 :

Id Address
1 London
2 Glascow

Result will be :(joining by id)

1 Jack London
2 Philip Glascow

But i need the Answer as

1 Jack
2 Philip
1 London
2 Glascow...

U can say that we can easily do this by Union..but i need to join with a condition.

Please Help.

Thanks & Regards,
Jagadhees

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 03/15/2005 :  12:32:26  Show Profile  Visit jsmith8858's Homepage
what condition do you need? There is no join at all in your sample, just a regular UNION. you need to give us more sample data or some more information about why a regular UNION will not work for you.

- Jeff

Edited by - jsmith8858 on 03/15/2005 12:32:43
Go to Top of Page

krjaga
Starting Member

United Kingdom
3 Posts

Posted - 03/15/2005 :  12:47:39  Show Profile
Hi,

the Condition for the join is

select * from table1 join table2 On table1.id = table2.id...

this kind of condition..but i should give union result..pls help..

The Requirement is, i have two tables, For each row in the First table, i have to fetch a row from second table.

The Result set should like,

Row From Table1 : 1 Jack
Row From Table2 : 1 London
Row From Table1 : 2 Philip
Row From Table2 : 2 Glascow

like the above....

Regards,
Jagadhees.
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 03/15/2005 :  12:51:30  Show Profile  Visit jsmith8858's Homepage
I understand what a JOIN expression is, but you have not indicated why you feel one is necessary. To acheive the results you have indicated all you need is:

Select Id,Name
From Table1
union
select id, Address
From Table2

Unless there is more sample data or more information you can give us, it is impossible for us to give you what you are looking for. Remember, we don't work at your company and we don't know what problems you are facing or what data you have or what requirements you are trying to meet. Does this make sense?

- Jeff
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 03/15/2005 :  13:18:27  Show Profile
Join or union, it's up to you.

Got a copy of books online?


USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(Col1 int, Col2 varchar(15))
CREATE TABLE myTable00(Col1 int, Col2 varchar(15))
GO

INSERT INTO myTable99(Col1, Col2)
SELECT 1, 'Jack' UNION ALL
SELECT 2, 'Philip'

INSERT INTO myTable00(Col1, Col2)
SELECT 1, 'London' UNION ALL
SELECT 2, 'Glascow'
GO

SELECT Col1, Col2 
  FROM (
	   SELECT Col1, Col2, 1 AS TableOrder FROM myTable99
	UNION ALL
	   SELECT Col1, Col2, 2 AS TableOrder FROM myTable00
	) AS XXX
ORDER BY Col1, TableOrder

-- OR

    SELECT l.Col1, r.Col2+', '+l.Col2 AS FullName 
      FROM myTable99 l 
INNER JOIN myTable00 r
	ON l.Col1 = r.Col1

GO

SET NOCOUNT OFF
DROP TABLE myTable99
DROP TABLE myTable00
GO




Brett

8-)
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
38200 Posts

Posted - 03/15/2005 :  14:36:40  Show Profile  Visit tkizer's Homepage
quote:
Originally posted by X002548


SELECT l.Col1, r.Col2+', '+l.Col2 AS FullName
FROM myTable99 l
INNER JOIN myTable00 r
ON l.Col1 = r.Col1




Yeah but that doesn't give the expected result set. He wants four rows. You can't do that with a join.

Tara
Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 03/15/2005 :  18:24:13  Show Profile
Tara is right so far, but with a little cheating...

Jagadhees - WHY???

CREATE TABLE table1(Id int, Name varchar(15))
CREATE TABLE table2(Id int, Address varchar(15))

INSERT INTO table1(Id, Name)
SELECT 1, 'Jack' UNION ALL
SELECT 2, 'Philip' UNION ALL
SELECT 3, 'Peter'

INSERT INTO table2(Id, Address)
SELECT 1, 'London' UNION ALL
SELECT 2, 'Glascow' UNION ALL
SELECT 3, 'Pan'

SELECT table1.Id, CASE d WHEN 1 THEN Name ELSE Address END
FROM table1 CROSS JOIN (SELECT 1 AS d UNION SELECT 2) d
JOIN table2 ON table1.ID = table2.ID

DROP TABLE table1
DROP TABLE table2


rockmoose
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 03/16/2005 :  09:14:31  Show Profile
quote:
Originally posted by tduggan

quote:
Originally posted by X002548


SELECT l.Col1, r.Col2+', '+l.Col2 AS FullName
FROM myTable99 l
INNER JOIN myTable00 r
ON l.Col1 = r.Col1




Yeah but that doesn't give the expected result set. He wants four rows. You can't do that with a join.

Tara



My first Union doesn't give hime what he asked for?



Brett

8-)
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
38200 Posts

Posted - 03/16/2005 :  12:31:05  Show Profile  Visit tkizer's Homepage
The UNION does, but the JOIN does not. I wonder how his class is going.

Tara
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 04/12/2005 :  14:22:24  Show Profile
Yes...what grade did you get?



Brett

8-)
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2019 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000