select all rows in two tables without a foreign key property

I have two tables in a database, say, cars and bikes. They both have just two columns id and name like so:

Bikes table(BikeID int, BikeName)
Cars Table(CarId int, CarName)

These two tables have absolutely no physical relationship, (no foreign keys etc) no primary keys, its really just a column of values.

I want to select all rows from both tables. But if they have common names, I want to combine them, like so:

Name        BikeId        CarId
```````````````````````````````
Car1         null         1
Car2         null         2
Bike1        1            null
Bike2        2            null
BikeCar      3            5

Is it even 开发者_JS百科possible to do this? I am using tsql (Sql server 2008)

thanks.

ps: I can think of making two tmp tables, adding values, make a third temp table, use a while loop and check for each product etc.. but I am just wondering if there is a shorter way of doing it... thanks.

edit: I realized I had typed the question without thinking too much. changed. thanks.

Contrary to the post title, this can be done using a join. The FULL OUTER JOIN will return all results from both of the tables joined together where possible but if the join criteria is not matched it will return NULL for the other table columns (similar to doing both a left and a right outer join at the same time).

SELECT
    ISNULL(Bikes.BikeName, Cars.CarName) AS Name,
    Bikes.BikeId,
    Cars.CarId
FROM Bikes
    FULL OUTER JOIN Cars ON Bikes.BikeName = Cars.CarName
ORDER BY ISNULL(Bikes.BikeName, Cars.CarName)

Yes there is, using a FULL OUTER JOIN : documentation http://msdn.microsoft.com/en-us/library/ms187518.aspx

select BikeName as Name, BikeID, null as CarID from Bikes
union all
select CarName as Name, null as BikeID, CarID from Cars

Should do it for you.

Try :-

SELECT 
  Name, MAX(BikeId) AS BikeId, MAX(CarId) AS CarId
FROM
(
   SELECT CarName AS Name, NULL AS BikeId, CarId FROM Cars
   UNION
   SELECT BikeName AS Name, BikeId, NULL AS CarId FROM Bikes
) vehicles
GROUP BY
  NAME