Query erors with empty result set but works fine if table has data

confused here.

been away from the pc for a while so that may be a contributing factor.

the following query returns a resultset without error if there are records in the table. If there are no matching recrods in the table, an error is thrown and I have never encountered that before.

heres the query

        , el.title_of_link
        , el.url
        , el.description
        , el.sequence_number 
     FROM external_links AS el
       JOIN (select business_id
                  , max(sequence_number) as max_seq_no
      FROM external_links 
      WHERE business_id = 123
       ) as ms
       on ms.business_id = el.business_id
      and ms.business_id = 123  
    WHERE el.business_id = 123
     order by el.sequence_number
       limit 0, 2

here some data and the create table statement.

CREATE TABLE external_links (
  business_id int(11) NOT NULL,
  url varchar(125) collate utf8_unicode_ci NOT NULL,
  title_of_link varchar(255) collate utf8_unicode_ci NOT NULL,
  description text collate utf8_unicode_ci NOT NULL,
  sequence_number int(2) unsigned zerofill NOT NULL,
  last_updated timestamp NOT NULL default CURRENT_TIMESTAMP,
  last_updated_by varchar(99) collate utf8_unicode_ci NOT NULL,
  UNIQUE KEY business_id (business_id,sequence_number),
  UNIQUE KEY url (business_id,url),
  UNIQUE KEY title_of_link (business_id,title_of_link)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- Dumping data for table 'external_links'

INSERT INTO external_links (business_id, url, title_of_link, description, sequence_number, last_updated, last_updated_by) VALUES
(477, 'www.thatdomain.com', 'over there', 'delightful', 01, '2011-02-16 10:20:25', 'Me'),
(477, 'www.this.com', 'sffd', 'gsgg', 02, '2011-02-16 10:20:25', 'Me'),
(477, 'www.another_domain.com', 'ghs', 'hsgdhs', 03, '2011-02-16 10:20:25', 'Me'),
(513, 'www.example.com', 'jkgf67r', 'desc', 01, '2011-01-15 19:29:32', 'John Doe'),
(513, 'www.new_domain.com', 'name of place', 'description', 02, '2011-01-15 19:29:32', 'Me');

The error confuses me even more:

Column ‘business_id’ cannot be null at external_links_input.pl line 463.


i dunno why it executes when there is data, but without the data, the error message clearly identifies a major problem – your subquery desperately needs a GROUP BY clause, despite the fact that its WHERE clause would limit the retrieval to just one business_id

i think i’ve seen this mysql behaviour before –

SELECT foo, SUM(bar) FROM t

the intent is to return the total bar for each foo, but when this executes, only one row is returned (with dubious results), instead of the intended one row per foo

of course, what you had was –

SELECT foo, SUM(bar) FROM t WHERE foo=123

in which case, if you follow me here, you could as easily have written –

SELECT SUM(bar) FROM t WHERE foo=123

see where i’m coming from?

Thanks rudy.

The business_id is in the sub select because, otherwise, I get a join error because the value needed for the join is missing. I take a hint from your suggestion that maybe there is a better way to structure the query?
I can’t think of it thought so any pointer would be helpful.

Anyhoo, it ‘seems’ to work now once I followed your recommendation to add a GROUP BY clause.

thanks again.

Oh… I found a way to improve it.

          sum(sequence_number) as max_seq_no
	  , el.title_of_link
        , el.url
        , el.description
        , el.sequence_number 
     FROM external_links AS el
    WHERE el.business_id = ?
     order by el.sequence_number

must be a sleepy head tonight. :frowning:

what I meant was, your suggestion finally sank in :cool:

the query in post #4 is missing a GROUP BY clause