I can't believe I can't figure out something this simple. I have three tables: customers (email, ...), books (isbn, title, author,...), and purchases (isbn, email, ...). I included the fields that I need to retrieve in parentheses. My problem is my purchases table has a user that appears multiple times, but all the queries I've tried only return one.
Here's one of the ones I've tried:
SELECT customers.email, firstName, lastName, title, author, dateBought, priceBought FROM purchases, customers, books WHERE customers.email = purchases.email AND books.isbn = purchases.isbn AND purchases.email = '[email protected]'
Selecting from multiple tables problem
Page 1 of 16 Replies - 1205 Views - Last Post: 11 February 2008 - 08:47 PM
Replies To: Selecting from multiple tables problem
#2
Re: Selecting from multiple tables problem
Posted 10 February 2008 - 06:19 PM
Remember to setup your joins in the order you are linking. In this case you are going from customers to purchases to books... so instead of books.isbn equals purchases.isbn, try switching them around and go purchases.isbn to books.isbn.
Going from customers to purchases should give you all purchases for a given customer and going from purchases to books so give you all the books related to those purchases.
And try specifying customers.email = '[email protected]' instead of purchases. It just makes it clearer to read.
See if that works for you.
Going from customers to purchases should give you all purchases for a given customer and going from purchases to books so give you all the books related to those purchases.
And try specifying customers.email = '[email protected]' instead of purchases. It just makes it clearer to read.
See if that works for you.

#3
Re: Selecting from multiple tables problem
Posted 10 February 2008 - 07:07 PM
Looks right.
Written another way, it might look like this:
To figure out what's going on, switch to outer joins:
This will get you all perchase records and show nulls is something on the joins doesn't match up.
Hope this helps.
Written another way, it might look like this:
SELECT c.email, c.firstName, c.lastName, b.title, b.author, p.dateBought, p.priceBought FROM purchases p INNER JOIN customers c ON c.email = p.email INNER JOIN books b ON b.isbn = p.isbn WHERE p.email = '[email protected]'
To figure out what's going on, switch to outer joins:
SELECT c.email, c.firstName, c.lastName, b.title, b.author, p.dateBought, p.priceBought FROM purchases p LEFT OUTER JOIN customers c ON c.email = p.email LEFT OUTER JOIN books b ON b.isbn = p.isbn WHERE p.email = '[email protected]'
This will get you all perchase records and show nulls is something on the joins doesn't match up.
Hope this helps.
#4
Re: Selecting from multiple tables problem
Posted 11 February 2008 - 09:29 AM
I've tried the ways both of you have suggested and it still shows up as one entry.
When I do this query,
I get two rows, but the title and author come up NULL
When I do this query,
SELECT c.email, c.firstName, c.lastName, b.title, b.author, p.dateBought, p.priceBought FROM purchases p LEFT OUTER JOIN customers c ON c.email = p.email LEFT OUTER JOIN books b ON b.isbn = p.isbn WHERE p.email = '[email protected]'
I get two rows, but the title and author come up NULL
#5
Re: Selecting from multiple tables problem
Posted 11 February 2008 - 09:48 AM
then that is the data you have in there.
you can use a database frontend to verify this.
you can use a database frontend to verify this.
#6
Re: Selecting from multiple tables problem
Posted 11 February 2008 - 09:53 AM
wingz198, on 11 Feb, 2008 - 11:29 AM, said:
When I do this query,
I get two rows, but the title and author come up NULL
SELECT c.email, c.firstName, c.lastName, b.title, b.author, p.dateBought, p.priceBought FROM purchases p LEFT OUTER JOIN customers c ON c.email = p.email LEFT OUTER JOIN books b ON b.isbn = p.isbn WHERE p.email = '[email protected]'
I get two rows, but the title and author come up NULL
So, the conculsion is you don't have the isbn from books b on b.isbn = p.isbn. Your loss of record has nothing to do with customers and email. The book listed in your purchases table does not exist in your books table.
#7
Re: Selecting from multiple tables problem
Posted 11 February 2008 - 08:47 PM
baavgai, on 11 Feb, 2008 - 10:53 AM, said:
So, the conculsion is you don't have the isbn from books b on b.isbn = p.isbn. Your loss of record has nothing to do with customers and email. The book listed in your purchases table does not exist in your books table.
Looks like that was the problem. For some reason some of the ISBNs that were in the purchases table didn't match up with the ones in the books table.
Thanks, everyone, for the help
Page 1 of 1