Solved

Strange join query

Posted on 2006-11-09
6
244 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 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MSSQL: Query to convert field in a row into several rows 29 95
MS Access question 11 59
VBA MS Word "Table of Contents" extractor` 4 24
use lov values 2 32
I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
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…

920 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now