How do you UNION 2 tables based having a primary Key with different fields in either table?-CodePudding

Given this scenario: I have 2 tables, 1 named Books and the other Customers that look like this -

Books
----------
CustomerId
BookTitle2

Customers
----------
CustomerId
FirstName
LastName
BookTitle

The customers table has a BookTitle field that has the name of books the customer acquired from another Source (or they could be books they can manual enter into that field)

The Books table can have the same Customer as in the Customers table but the book in that table is from a publisher entered book.

How would I write an SQL query to acquire all the records in the customer table for a specific Customer based on the CustomerId and add the record(s) from the Books table to the returned record set?

The end result would look similar to this:

CustomerId | FirstName | LastName | BookTitle
CustomerId | FirstName | LastName | BookTitle2

I could create empty pseudo fields in the Books table to correspond to the Customers table and perform a UNION but I would want to be able to filter on a specific customer ID in one place in the query to acquire the combination of records from both tables

CodePudding user response：

Try this:

Select CustomerId, FirstName, LastName, BookTitle
from Customers
where ...
UNION 
Select CustomerId, '' as FirstName, '' as LastName, BookTitle2 as Booktitle
from Books
where...

Note that WHERE condition in the above query is optional and you can use them appropriately. Hope this helps. Please share your thoughts if it solved your problem.