[RESOLVED] left join... w/ a limit

perpetualshaun

I searched the board here and did a Google search before my post, but still can't quite get this and I'm thinking it should be relatively simple.

I have two tables that I'm working with - products and monthly_invoices. I've programmed a file that will send out invoices, and the invoices should be sent either monthly, quarterly, bi-annually or once a year. So I need to pretty much grab everything from the products table, and the LAST invoice_date from the invoices table. So I have a query like this:

SELECT products.*,
monthly_invoices.invoice_date
FROM products
LEFT JOIN monthly_invoices ON monthly_invoices.invoice_company_id=products.products_company_id WHERE products_paid_up_through <= 1146855189 AND products_invoice_amount > '0.00'
ORDER BY products_company_id ASC, products_name ASC

Only problem is that the monthly_invoices will pretty much always have multiple rows. So how can I modify this query so that it will always only return ONE row from the monthly_invoices table - the last invoice sent?

esukf

Is the last invoice sent always the one with the newest date? Can you do a ORDER BY date LIMIT 1 to select that last row?

perpetualshaun

Yes, the last invoice sent w/ always have the largest timestamp, but it's per company and product as well. Here's a revised query that I thought would work:

SELECT products.*,
monthly_invoices.invoice_date
FROM products
LEFT JOIN
(
	SELECT invoice_date
	FROM products
	LEFT JOIN monthly_invoices ON monthly_invoices.invoice_company_id=products.products_company_id
	ORDER BY invoice_date DESC
	LIMIT 1
) AS monthly_invoices
WHERE products_paid_up_through <= 1146855189 AND products_invoice_amount > '0.00'
ORDER BY products_company_id ASC, products_name ASC

But it doesn't. When I try that through PHP MyAdmin, it spits out this error:

 MySQL said:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE products_paid_up_through <=1146855189 AND products_invoice_amount >  '0.00' at line 9

We're running MySQL MySQL 4.1.7-standard on this server. Any ideas?

bogu

I dont think mysql 4.1.7 supprot subselects ...

Did u try this?

ORDER BY invoice_date DESC
LIMIT 1

u dont need

ORDER BY products_company_id ASC, products_name ASC

because u need only one record ...

perpetualshaun

Do you know of another way that I could write the query?

bogu

Write your table structure and what u need to select ...

perpetualshaun

Thanks for taking a look @ this for me. I think this is what you asked for in the way of table structure. Here's the fields in my products table:

CREATE TABLE `products` (
  `products_id` int(11) NOT NULL auto_increment,
  `products_company_id` int(11) NOT NULL default '0',
  `products_name` varchar(90) NOT NULL default '',
  `products_status` tinyint(4) NOT NULL default '0',
  `products_type_of_billing` tinyint(1) NOT NULL default '0',
  `products_invoice_amount` decimal(10,2) NOT NULL default '0.00',
  PRIMARY KEY  (`products_id`)
)

And then my monthly_invoices table looks like this:

CREATE TABLE `monthly_invoices` (
  `invoice_id` int(11) NOT NULL auto_increment,
  `invoice_number` varchar(20) NOT NULL default '',
  `invoice_company_id` int(11) NOT NULL default '0',
  `invoice_date` varchar(14) NOT NULL default '',
  `invoice_amount` decimal(10,2) NOT NULL default '0.00',
  `invoice_freq_of_billing` tinyint(1) NOT NULL default '0',
  `invoice_paid` tinyint(1) NOT NULL default '0',
  PRIMARY KEY  (`invoice_id`)
)

So when this cron job runs every month, I need to look for items in the Products table that have a invoice_amount ("WHERE products_paid_up_through <= 1146855189 AND products_invoice_amount > '0.00'"), and then pull in the LAST invoice that was sent for that company (LEFT JOIN monthly_invoices ON monthly_invoices.invoice_company_id=products.products_company_id). The monthly invoices table is a log, so it will typically have multiple entries for each company and product. I want to pull just the most recent invoice_date into this query.

bogu

Well u want to select the last invoice from monthly_invoices

SELECT mi.invoice_date, p.* FROM monthly_invoices mi
LEFT JOIN products p ON mi.invoice_company_id = pi.products_company_id
WHERE p.products_paid_up_through <= 1146855189 AND p.products_invoice_amount > 0
ORDER BY mi.invoice_date DESC
LIMIT 1

perpetualshaun

Not quite... my first query will return 3 rows w/ the data I have at the moment. Three products w/ two different company_ids. I suppose I can simply just do one more query as I get ready to prepare each invoice to find the date of the last one sent. But I thought it would be nice to pul everything in one shot, in one result set. But based on the responses I've received here and the number of other views this post has had, I guess there just isn't a better way.

Lars_Berg

SELECT products.*,
max(monthly_invoices.invoice_date)
FROM products JOIN
monthly_invoices ON monthly_invoices.invoice_company_id=products.products_company_id
WHERE products_paid_up_through <= 1146855189 
  AND products_invoice_amount > 0.00
group by <all columns in the product table>
ORDER BY products_company_id ASC, products_name ASC

Mysql 4.1.7 do support derived tables and subqueries. The error in the query is that there is no join condition for the outer join.