Join two SQL Server tables

Question

I have two tables now I need a select or join command in SQL to have the third table just like image below

My two tables are like this:

I only know a simple things about join command in SQL, should I use join or something else?

I do not want have the third table in my database, I want that for a short time (something like virtual table). Please help !

Answer 1

You are actually looking for UNION or UNION ALL.

First of all, there is no condition on which to JOIN tables (review your documentation on JOIN) and JOIN is used for retrieving information about one logical element, let's say Event in your case, which has details stored in more tables.

Secondly, JOIN will make one result set with all of the columns of your two tables, when actually you are not trying to get all columns, but all rows.

For this you will have to use UNION or UNION ALL like this:

SELECT
    EventID,
    ID,
    EventName,
    Date,
    Pic,
    Privacy
FROM Table1

UNION ALL

SELECT
    PLID AS EventID,
    ID AS ID,
    PlaceName AS EventName,
    Date AS Date,
    NULL AS Pic,
    NULL AS Privacy
FROM Table2

In order to sort the result you get from the result set returned by the queries above you will need to wrap your above SELECT statements with another SELECT and use a WHERE clause at that level, like below:

SELECT *
FROM (SELECT
        EventID,
        ID,
        EventName,
        Date,
        Pic,
        Privacy
    FROM Table1

    UNION ALL

    SELECT
        PLID AS EventID,
        ID AS ID,
        PlaceName AS EventName,
        Date AS Date,
        NULL AS Pic,
        NULL AS Privacy
    FROM Table2) AS Result
WHERE Date > '2014-05-26'

Answer 2

What you're looking to do is a UNION or UNION ALL, not a join. See: http://www.w3schools.com/sql/sql_union.asp

UNION combines two tables without connecting their content. Your example shows all 4 records from the original tables unmodified.

A JOIN solution links the two tables. It's very common and you will probably use it if you're building a relational database, but it won't give you the example result.

Since the two tables don't have identical # of columns, you have to help it out here:

SELECT EventID, EventName, Date, Pic, privacy FROM [table 1]
UNION ALL
SELECT PLID, PlaceName, Date, null, null FROM [table 2]

Answer 3

You want to have one table from two different tables. So you need unified result set from each by renaming column in SELECT statement:

SELECT `EventID` AS `ObjectID`, `EventName` AS `ObjectName`, .... FROM table_1 ...

similary with table_2

Then combine to one result set:

SELECT `ID` AS `ObjectID`, `EventName` AS `ObjectName`, .... FROM table_1 ...
UNION
SELECT `PlaceID` AS `ObjectID`, `PlaceName` AS `ObjectName`, .... FROM table_2 ...

Answer 4

My mistake, I didn't take the time to examine the pictures fully. you would have to use Union since you want to return what is in both tables.