CODEWITHSUNDEEP
sqlsql-serverjoinmerge
Ian G
Ian G

Reputation: 30234

How do I join (merge?) several tables?

I've got three tables

AUTHOR_TABLE
-------------
AUTHOR_ID (PK)   AUTHOR_NAME   
1                me
2                you 

ARTICLE_AUTHOR_TABLE
-------------
AUTHOR_ID  ARTICLE_ID  
1          100
2          101


EVENT_AUTHOR_TABLE
------------------------------------------
AUTHOR_ID   EVENT_ID  
1           200
1           201

All I want is either

RESULTS
-----------------------------------------
AUTHOR_ID AUTHOR_NAME SOURCE_TABLE ID
1         me          article      100
2         you         article      101
1         me          event        200
1         me          event        201 

/* where SOURCE_TABLE would be either "EVENT" or "ARTICLE" */

EDIT I don't really want this

RESULTS
-----------------------------------------
AUTHOR_ID AUTHOR_NAME EVENT_ID ARTICLE_ID
1         me          NULL         100
2         you         NULL         101
1         me          200         NULL
1         me          201         NULL

Any pointers appreciated.

THanks

Upvotes: 0

Views: 306

Answers (3)

Ólafur Waage
Ólafur Waage

Reputation: 69981

Since this is homework, and i am guessing you are using MySQL, read this documentation on the UNION syntax. What you are doing is basically doing two (or more) queries from similar databases and getting the results in one query.

Be advised that the resulting columns need to be the same.

You can also create variables to show what table you are calling from.

SELECT AUTHOR_ID, AUTHOR_NAME, `AUTHOR_TABLE` AS SOURCE_TABLE, ID FROM AUTHOR_TABLE

You do this will all the union's but change the table name.

Upvotes: 0

Seb
Seb

Reputation: 25147

SELECT
  at.author_id,
  at.author_name,
  'article' AS source_table,
  aat.id
FROM
  author_table at
  JOIN article_author_table aat
    ON at.author_id = aat.author_id

UNION ALL

SELECT
  at.author_id,
  at.author_name,
  'event' AS source_table,
  eat.id
FROM
  author_table at
  JOIN event_author_table eat
    ON at.author_id = eat.author_id

Upvotes: 3

Konstantin Tarkus
Konstantin Tarkus

Reputation: 38358

SELECT A.AUTHOR_ID, A.AUTHOR_NAME, EA.EVENT_ID, AA.ARTICLE_ID
FROM AUTHOR_TABLE AS A
    LEFT JOIN ARTICLE_AUTHOR_TABLE AS AA ON AA.AUTHOR_ID = A.AUTHOR_ID
    LEFT JOIN EVENT_AUTHOR_TALBE AS EA ON EA.AUTHOR_ID = A.AUTHOR_ID

Upvotes: 1

Related Questions