Improve company productivity with a Business Account.Sign Up

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
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

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…
I recently worked on a Wordpress site that utilized the popular ContactForm7 (https://contactform7.com/) plug-in that only sends an email and does not save data. The client wanted the data saved to a custom CRM database. This is my solution.
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.
Watch the video of Kernel Migrator for SharePoint, which demonstrate the process easily of migration from SharePoint to SharePoint, OneDrive for Business & Google Drive servers, Public Folder to SharePoint, File Server to SharePoint. The tool has va…

579 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