[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Strange join query

Posted on 2006-11-09
6
Medium Priority
?
298 Views
Last Modified: 2006-11-18
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
Comment
Question by:poutses
  • 3
  • 2
6 Comments
 

Author Comment

by:poutses
ID: 17912245
the name1,name2, bname1,bname2 etc are not fields in the table, they are data!
0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 400 total points
ID: 17912247
You can use CROSS JOIN

SELECT a.Field1 , b.Field1
FROM TableA a CROSS JOIN TableB B
0
 
LVL 35

Expert Comment

by:Raynard7
ID: 17912249
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 

Author Comment

by:poutses
ID: 17912294
each table has more than 1 field, but I only need one field out of them....
0
 
LVL 35

Expert Comment

by:Raynard7
ID: 17912306
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
 

Author Comment

by:poutses
ID: 17912323
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

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
If you try to migrate from Elastix to Issabel, you will face a lot of issues. These problems are inevitable but fortunately, you can fix them. In the guide below, I will explain how I performed the migration while keeping all data and successfully t…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

640 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question