T-SQL Join Query

Question

I have been trying to write a query for a view but I can't seem to get it... I have two tables that I need to join... but what I need is that for every ID in table 1 I get all records on table 2. For Example:

 Table 1       Table 2
  Jhon          Car
  Bill          House
                Jet

I would like to get:

Table 3 Jhon Car Jhon House Jhon Jet Bill Car Bill House Bill Jet

P.S. Data on both tables can vary. P.S.S. Table 1 is actually a Left Outer Join between two other tables where the first table contains the indexes and the second contains the field used to create relation to Table 2.

Martin Smith · Accepted Answer · 2011-01-13 12:56:17Z

6

You need a CROSS JOIN for this (AKA Cartesian Product).

SELECT t1.col, t2.col
FROM Table1 t1 cross join Table2 t2

answered Jan 13, 2011 at 12:56

Martin Smith

457k97 gold badges777 silver badges887 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

marc_s · Accepted Answer · 2011-01-13 13:17:46Z

3

Try this

select * from table1, table2

or use a CROSS JOIN if database supports it

edited Jan 13, 2011 at 13:17

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Jan 13, 2011 at 12:57

Exoas

1816 bronze badges

3 Comments

Martin Smith Over a year ago

SQL Server does support it so I think it's best practice to use the explicit syntax so it is clear that it is intentional!

marc_s Over a year ago

If you post code, XML or data samples, please highlight those lines in the text editor and click on the "code samples" button ( { } ) on the editor toolbar to nicely format and syntax highlight it!

marc_s Over a year ago

This is the "old-style" JOIN syntax - which is both deprecated (you should use explicit joins, e.g. INNER JOIN; OUTER JOIN, CROSS JOIN, as needed) and also less clear and less obvious in my opinion. I would avoid this syntax if ever possible

Quassnoi · Accepted Answer · 2011-01-13 12:56:38Z

2

SELECT  *
FROM    table1
CROSS JOIN
        table2

answered Jan 13, 2011 at 12:56

Quassnoi

428k94 gold badges628 silver badges623 bronze badges

Comments

odiseh · Accepted Answer · 2011-01-13 12:59:53Z

2

select columns you want to get
from Table1 Cross Join Table2

answered Jan 13, 2011 at 12:59

odiseh

26.7k33 gold badges114 silver badges155 bronze badges

Collectives™ on Stack Overflow

T-SQL Join Query

4 Answers 4

Comments

3 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related