Result from 2 tables with no link
-
I need a result from 2 tables (TableA and TableB) with no common link column between the two.
It should return the values from TableA even though TableB returns no result
Select TableA.Col1, TableB.ColA
FROM TableA
LEFT JOIN Table B ON ?????
WHERE TableA.ID = 1
AND TableB.ID = 2
-
Use the old syntax
Select TableA.Col1, TableB.ColA
FROM TableA, Table B
WHERE TableA.ID = 1
AND TableB.ID = 2
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com -
But this will not return any result if TableB has no row with ID=2 and TableA has row with ID =1
I would still like to return all rows from TableA even though TableB has no rows
-
If you have no common link column, then you cannot specify JOIN (INNER, OUTER, whatever).
And your request then makes no sense at all - you have nothing to join on!
Is there an intermediate table (TableC) that does link the tables - join through that instead?
If not, the basic design seems wrong and you cannot achieve what you want because it is not a logical request...
Sorry to be negative. Maybe you can post more information?
-
Sounds like you want a cartesian product. This is from books online.
Using Cross Joins
A cross join that does not have a WHERE clause produces the Cartesian product of the tables involved in the join. The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table. This is an example of a Transact-SQL cross join:
USE pubsSELECT au_fname, au_lname, pub_nameFROM authors CROSS JOIN publishers ORDER BY au_lname DESCThe result set contains 184 rows (authors has 23 rows and publishers has 8; 23 multiplied by 8 equals 184).
However, if a WHERE clause is added, the cross join behaves as an inner join. For example, these Transact-SQL queries produce the same result set:
USE pubsSELECT au_fname, au_lname, pub_nameFROM authors CROSS JOIN publishers WHERE authors.city = publishers.cityORDER BY au_lname DESC-- OrUSE pubsSELECT au_fname, au_lname, pub_nameFROM authors INNER JOIN publishers ON authors.city = publishers.cityORDER BY au_lname DESCGood luckTom -
This query does not return any rows.
USE Pubs
Go
SELECT au_lname, au_fname, employee.fname, employee.lname
FROM authors, employee
WHERE authors.au_id = '472-27-2349'
AND employee.emp_id = 'ZZZZZZZZ'
what I would like to return is
Gringlesby Burt Null Null
Is this possible ?
-
In that case, you need a LEFT JOIN.
Looking at your original post, you did use a LEFT JOIN, but with 1 problem - you placed a WHERE clause on the LEFT JOIN'ed table.
Doing so implicitly converts the join to an INNER. What you really need is (using your PUBS db example):
SELECT au_lname, au_fname, employee.fname, employee.lname
FROM authors
LEFT JOIN employee ON ( employee.emp_id = 'ZZZZZZZZ' )
WHERE authors.au_id = '472-27-2349'
... although why you want to do something like this is a mystery.
-
Instead of running 2 queries from .Net, make one trip to the server to get the data.
Thanks PW, it worked.
-
I'm still curious... why did you want to join two tables that have nothing in common to join on?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Select TableA.Col1 FROM TableA WHERE TableA.ID = 1;
Select TableB.ColA FROM Table B WHERE TableB.ID = 2
Put that as the commandtext for your .net SQLCommand. It will return 2 recordsets.
That, or write a stored proc that runs the 2 statements with one call.
Joining 2 unrelated tables just to reduce the client-server round trips isn't the best ides. There are much better ways.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Definitely agree. If you have 2 rows in table A and 3 rows in table B then you'll get repeated data, etc - yukky to deal with in client side code. You should definitely return two separate resultsets from your single TSQL query as GilaMonster has said. This might sound complex in your client-side code, but it is very easy to deal with and is MUCH easier than deciphering repeated results..
-
How about Union....
-
Obvious simple choice
Should've suggested that!! 
Will only work though if the tables have the same format - do they? 
-
Select TableA.Col1, 1 FromTable FROM TableA WHERE TableA.ID = 1;
UNION ALL
Select TableB.ColA, 2 AS FromTable FROM Table B WHERE TableB.ID = 2
U will get like this
Col1 FromTable
------------------
Record1 1
Record2 1
Record1 2
Record2 2
It might help u out
-
Only if the two tables have the same number of fields with compatable datatypes.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply