List:General Discussion« Previous MessageNext Message »
From:Johan De Meersman Date:May 20 2011 10:22pm
Subject:Re: Complex Query
View as plain text  
Heh. The parser is pointing out a simple syntax oversight, yes. The correct syntax for
that is select ... from (subselect) aliasname;

----- Original Message -----
> From: "Mimi Cafe" <mimicafe@stripped>
> To: "Johan De Meersman" <vegivamp@stripped>, "Guido Schlenke"
> <galerius@stripped>
> Cc: mysql@stripped
> Sent: Friday, 20 May, 2011 11:52:14 PM
> Subject: RE: Complex Query
> 
> Select count(*) from (select........) did not work. It says: "ERROR
> 1248 (42000): Every derived table must have its own alias" and I am
> not sure it really mean aliases.
> 
> Although "select found_rows();" works, I am trying to avoid it as the
> documentation says it may produce expected result - especially after
> running a query with MySQL limit clause.
> 
> Mimi
> 
> => -----Original Message-----
> => From: Johan De Meersman [mailto:vegivamp@stripped]
> => Sent: 20 May 2011 21:16
> => To: Guido Schlenke
> => Cc: mysql@stripped
> => Subject: Re: Complex Query
> =>
> => Hmm. Simply replacing the field list with count(*) should work,
> too.
> => If you only need the count after having executed the select, I'm
> => pretty sure there's something in the API that gives you that
> without a
> => second query, although I'll be buggered if I can remember right
> now.
> =>
> => ----- Original Message -----
> => > From: "Guido Schlenke" <galerius@stripped>
> => > To: mysql@stripped
> => > Sent: Friday, 20 May, 2011 9:04:32 PM
> => > Subject: Re: Complex Query
> => >
> => > Hi Mimi,
> => >
> => > try this
> => >
> => > select count(*) from
> => > (    SELECT module_nr, IFNULL(image,'no_image.jpg'),title,
> subtitle,
> => > group_concat(concat(fname,' ',initial,' ',lname)), pkisbn,
> => >
> => >     publisher,publication_year, edition, cover_type, pages FROM
> book
> => >     INNER
> => > JOIN publisher on pkpublisher_id = fkpublisher_id INNER JOIN
> => > module_book on
> => >
> => >     pkisbn = fkbook_isbn INNER JOIN module on fkmodule_nr =
> => module_nr
> => >     INNER
> => > JOIN book_author on pkisbn = fkisbn INNER JOIN author on
> fkauthor_id
> => > =
> => > pkauthor_id
> => >
> => >     WHERE module_nr = ? group by
> IFNULL(image,'no_image.jpg'),title,
> => > subtitle, pkisbn, publisher    );
> => >
> => > Guido
> => >
> => > "Mimi Cafe"  schrieb im Newsbeitrag
> => > news:004701cc16f1$a2d550c0$e87ff240$@com...
> => >
> => > Hi
> => >
> => >
> => >
> => > I now need to determine the number of rows returned by this
> query
> => > below.
> => > Wrapping it within SELECT COUNT did not work as expected.
> => >
> => >
> => >
> => >
> => >
> => >     SELECT module_nr, IFNULL(image,'no_image.jpg'),title,
> subtitle,
> => > group_concat(concat(fname,' ',initial,' ',lname)), pkisbn,
> => >
> => >     publisher,publication_year, edition, cover_type, pages FROM
> book
> => >     INNER
> => > JOIN publisher on pkpublisher_id = fkpublisher_id INNER JOIN
> => > module_book on
> => >
> => >     pkisbn = fkbook_isbn INNER JOIN module on fkmodule_nr =
> => module_nr
> => >     INNER
> => > JOIN book_author on pkisbn = fkisbn INNER JOIN author on
> fkauthor_id
> => > =
> => > pkauthor_id
> => >
> => >     WHERE module_nr = ? group by
> IFNULL(image,'no_image.jpg'),title,
> => > subtitle, pkisbn, publisher;
> => >
> => >
> => >
> => > Mimi
> => >
> => >
> => >
> => > From: Mimi Cafe [mailto:mimicafe@stripped]
> => > Sent: 20 May 2011 11:33
> => > To: 'Anupam Karmarkar'
> => > Cc: 'Guido Schlenke'; mysql@stripped
> => > Subject: RE: Complex Query
> => >
> => >
> => >
> => > Hi guys
> => >
> => >
> => >
> => > That's cool! It looks like my query was good except that I miss
> the
> => > "group
> => > by".  Now I only had to remove the "as image" from the grouping
> => below
> => > and it
> => > works fine.
> => >
> => >
> => >
> => > Thanks guys
> => >
> => >
> => >
> => > Mimi
> => >
> => >
> => >
> => > From: Anupam Karmarkar [mailto:sb_akarmarkar@stripped]
> => > Sent: 20 May 2011 07:48
> => > To: 'Guido Schlenke'; mysql@stripped; Mimi Cafe
> => > Subject: RE: Complex Query
> => >
> => >
> => >
> => >
> => > Hi Guido,
> => >
> => > You need to add group by in your query get desire result
> => >
> => > SELECT IFNULL(image,'no_image.jpg') as image,title, subtitle,
> => > group_concat(concat(fname,' ',initial,' ',lname)) as author,
> pkisbn,
> => > publisher FROM book INNER JOIN publisher on pkpublisher_id =
> => > fkpublisher_id
> => > INNER JOIN module_book on     pkisbn = fkbook_isbn INNER JOIN
> module
> => > on
> => > fkmodule_nr = module_nr INNER JOIN book_author on pkisbn =
> fkisbn
> => > INNER JOIN
> => > author on fkauthor_id = pkauthor_id
> => > WHERE module_nr = 'MSING0010'
> => > group by IFNULL(image,'no_image.jpg') as image,title, subtitle,
> => > pkisbn,
> => > publisher
> => >
> => > It should give desire result. As group concat will return group
> by 1
> => > if you
> => > dont specify group by, which will return only one row, to get
> result
> => > you
> => > need to specify group by as given in above query.
> => >
> =>
> => --
> => Bier met grenadyn
> => Is als mosterd by den wyn
> => Sy die't drinkt, is eene kwezel
> => Hy die't drinkt, is ras een ezel
> =>
> => --
> => MySQL General Mailing List
> => For list archives: http://lists.mysql.com/mysql
> => To unsubscribe:
> => http://lists.mysql.com/mysql?unsub=1
> 

-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel
Thread
Complex QueryMimi Cafe19 May
  • Re: Complex QueryClaudio Nanni19 May
  • Re: Complex Queryhsv19 May
RE: Complex QueryMimi Cafe19 May
  • Re: Complex QueryGuido Schlenke19 May
  • Re: Complex QueryGuido Schlenke19 May
    • RE: Complex QueryMimi Cafe20 May
      • RE: Complex QueryAnupam Karmarkar20 May
        • RE: Complex QueryMimi Cafe20 May
          • RE: Complex QueryMimi Cafe20 May
            • Re: Complex QueryGuido Schlenke20 May
              • Re: Complex QueryJohan De Meersman20 May
                • RE: Complex QueryMimi Cafe20 May
                  • RE: Complex QueryMimi Cafe20 May
                  • Re: Complex QueryJohan De Meersman21 May
                    • Re: Complex Queryhsv21 May
                      • RE: Complex QueryMimi Cafe21 May