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
Advertisement
Answer
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.