Solved

Strange join query

Posted on 2006-11-09
6
280 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 100 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

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: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

707 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