[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 295
  • Last Modified:

Strange join query

I have two tables TableA and Table B (one field each) with data

[TableA]
name1,
name2,
name3

and
[Table B]
bname1,
bname2,
bname3

I want to build the query to return the following:

name1, bname1
name1, bname2
name1, bname3
name2, bname1
name2, bname2
name2, bname3
name3, bname1
name3, bname2
name3, bname3

How can I do it in SQL Server 2000?
Thanks
0
poutses
Asked:
poutses
  • 3
  • 2
1 Solution
 
poutsesAuthor Commented:
the name1,name2, bname1,bname2 etc are not fields in the table, they are data!
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
You can use CROSS JOIN

SELECT a.Field1 , b.Field1
FROM TableA a CROSS JOIN TableB B
0
 
Raynard7Commented:
Hi, you have only one field in each table (here I'm calling them field)
If you do not put any join conditions in it will do what is called a cartesian join and will give you all combinations of rows from the two tables

select
   a.field, b.field
from
   tableA as a, tableB as B
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
poutsesAuthor Commented:
each table has more than 1 field, but I only need one field out of them....
0
 
Raynard7Commented:
the same principle should apply, this is row based combinations - so only show the desired fields in the query - CROSS JOIN is probably better
0
 
poutsesAuthor Commented:
sorry, it works both ways. I am stupid, because in one table I had no data, that's why I was getting no results... The crossjoin query is given from Create New View....

Thanks for the time
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now