6 Replies - 982 Views - Last Post: 11 February 2008 - 08:47 PM Rate Topic: -----

#1 wingz198  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 55
  • Joined: 14-October 05

Selecting from multiple tables problem

Posted 10 February 2008 - 05:26 PM

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 = 'me@me.com'


Is This A Good Question/Topic? 0
  • +

Replies To: Selecting from multiple tables problem

#2 Martyr2  Icon User is offline

  • Programming Theoretician
  • member icon

Reputation: 4437
  • View blog
  • Posts: 12,308
  • Joined: 18-April 07

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 = 'me@me.com' instead of purchases. It just makes it clearer to read.

See if that works for you. :)
Was This Post Helpful? 0
  • +
  • -

#3 baavgai  Icon User is online

  • Dreaming Coder
  • member icon

Reputation: 5938
  • View blog
  • Posts: 12,863
  • Joined: 16-October 07

Re: Selecting from multiple tables problem

Posted 10 February 2008 - 07:07 PM

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 = 'me@me.com'



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 = 'me@me.com'



This will get you all perchase records and show nulls is something on the joins doesn't match up.

Hope this helps.
Was This Post Helpful? 0
  • +
  • -

#4 wingz198  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 55
  • Joined: 14-October 05

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,
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 = 'me@me.com'

I get two rows, but the title and author come up NULL
Was This Post Helpful? 0
  • +
  • -

#5 Trogdor  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 15
  • View blog
  • Posts: 627
  • Joined: 06-October 06

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.
Was This Post Helpful? 0
  • +
  • -

#6 baavgai  Icon User is online

  • Dreaming Coder
  • member icon

Reputation: 5938
  • View blog
  • Posts: 12,863
  • Joined: 16-October 07

Re: Selecting from multiple tables problem

Posted 11 February 2008 - 09:53 AM

View Postwingz198, 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 = 'me@me.com'

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.
Was This Post Helpful? 0
  • +
  • -

#7 wingz198  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 55
  • Joined: 14-October 05

Re: Selecting from multiple tables problem

Posted 11 February 2008 - 08:47 PM

View Postbaavgai, 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
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1