[wingz198]

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]'

[Martyr2]

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.

[baavgai]

Looks right.

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.

[wingz198]

I've tried the ways both of you have suggested and it still shows up as one entry.
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

[Trogdor]

then that is the data you have in there.
you can use a database frontend to verify this.

[baavgai]

wingz198, on 11 Feb, 2008 - 11:29 AM, said:

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

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.

[wingz198]

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