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

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

---

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

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

---