database - SQL Retrieve Names Based on Multiple Tables -

so have 3 tables. customer(customerid, lastname, firstname), purchase(purchaseid, itemname), , transaction(customerid, purchaseid, date).

the problem having need full name of customers buy both items "paint" , "books" when run code nothing comes up. here have:

select customer.firstname, customer.lastname customer, purchase purchase.item = 'paint' , purchase.item = 'books' group customer.lastname, customer.firstname; 

please help, new , help.

this type of problem called relational division.

select  customer.firstname, customer.lastname    customer             inner join transaction             on customer.customerid = transaction.customerid         inner join purchase             on transaction.purchaseid = purchase.purchaseid   purchase.item in ('paint', 'books')  -- list items here group   customer.lastname, customer.firstname having  count(distinct purchase.item) = 2 -- total number of items searched 

• sql of relational division

if there unique constraint enforced every itemname on each transaction, can use *

select  customer.firstname, customer.lastname    customer             inner join transaction             on customer.customerid = transaction.customerid         inner join purchase             on transaction.purchaseid = purchase.purchaseid   purchase.item in ('paint', 'books') group   customer.lastname, customer.firstname having  count(*) = 2