MySQL Forums
Forum List  »  General

Re: Help with this join query please
Posted by: Matt Edelman
Date: January 22, 2005 01:02PM

Well, no sooner do I post this than I come up with half the answer. I am now able to get the customer's first name into the result:

mysql> SELECT i.id, li.description, c.first_name from customers c, invoices i left join line_items li on i.id = li.invoice_id where c.id = i.customer_id group by i.id;
+----+---------------+------------+
| id | description | first_name |
+----+---------------+------------+
| 1 | NULL | Jake |
| 2 | intake valve | Jason |
| 3 | NULL | Matt |
| 4 | NULL | Matt |
| 5 | NULL | Matt |
| 6 | spoiler bolts | |
| 7 | NULL | Matt |
| 8 | NULL | Jake |
| 9 | NULL | Jake |
| 10 | NULL | Jake |
| 11 | NULL | Jake |
| 12 | NULL | Jake |
| 13 | NULL | Jake |
| 14 | NULL | Jake |
+----+---------------+------------+

I'm not exactly sure how the query works because I'm still a little mystified by outer joins. Also, the "group by" functionality. Without "group by" though, I get a row for each line_item, giving multiple rows with the same invoice ID.

Also, still not explicitly selecting the line_item.description with the lowest id, though it seems to be selected implicitly.

If anyone has enough wind to try to demystify this query for me, and answer the second part of the question, I am grateful.

Options: ReplyQuote


Subject
Written By
Posted
Re: Help with this join query please
January 22, 2005 01:02PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.