I have two select statements to get a book authors full name, however the databa
ID: 3663159 • Letter: I
Question
I have two select statements to get a book authors full name, however the database is split in retail orders and ecommerce orders. I cannot figure out how to join these two different select statements together so i can count the authors and group them in order by populatiry while showing the count. I should see something like this as a final product.
author count
smith 22
doe 21
frank 10
lucy 2
select full_name
from dbo.ecommerce_order_detail
inner join dbo.products
on dbo.ecommerce_order_Detail.product_num=dbo.products.id
inner join dbo.author
on dbo.products.authorid=dbo.author.id
select full_name
from dbo.pos_transdetail
inner join dbo.products
on dbo.pos_TransDetail.productid=dbo.products.id
inner join dbo.author
on dbo.products.authorid=dbo.author.id
Explanation / Answer
SELECT ecommerce_orderr_detail.full_name, pos_transdetail.full_name,COUNT(*)
FROM dbo.ecommerce_order_details As e
INNER JOIN dbo.products As p
ON dbo.products.authorId= e.authorId
INNER JOIN pos_transdetail As r
ON p.authorId = r.authorId
ORDER BY p.popularity;
We have used COUNT(*) to display all the rows of products table.
Then we used Aliases to make table names shorter.
ORDER BY clause is used to order the data according to popoularity.