Warning: count(): Parameter must be an array or an object that implements Countable in /home/pasokime/domains/mysql.lt/public_html/forumas/include/parser.php on line 820
  • Registered: 2007-01-08
  • Posts: 256

Topic: Kažkas negerai su subqueriais

  SELECT `t3`.`id` , `t3`.`title` , `t3`.`tag` , `t4` . * 
  FROM `clans` AS `t3` 
  INNER JOIN (
    SELECT COUNT( * ) AS `num` , SUM( t2.exp ) AS `suma` 
    FROM `clan_members` AS `t1` 
    INNER JOIN `users` AS `t2` 
      ON t2.id = t1.user
    WHERE (t1.cid = t3.id)
  ) AS `t4` 
  ORDER BY `t4`.`suma` DESC

klaida: #1054 - Unknown column 't3.id' in 'where clause'

kiek pamenu, kai subqueris budavo selecte, budavo galima lentelės duomenis perduoti į subquerį, bet bėda tame, jog man reikia iš subquerio išvesti du stulpelius.

Gal kas nors žinote kaip pataisyti klaidą?

agonija - http://www.agonija.eu
neworld - http://www.neworldwar.com
neuron  - http://neuron.avita.lt

2 Reply by minde (edited by minde 2009-01-10 23:53:43)

  • minde
  • Administrator
  • Offline
  • Registered: 2006-06-14
  • Posts: 4,003

Re: Kažkas negerai su subqueriais

SELECT `t3`.`id`,
   `t3`.`title`,
   `t3`.`tag`,
   `t4` . * 
FROM `clans` AS `t3` 
   INNER JOIN (
      SELECT cid,
         COUNT( * ) AS `num`,
         SUM( t2.exp ) AS `suma` 
      FROM `clan_members` AS `t1` 
          INNER JOIN `users` AS `t2` ON t2.id = t1.user
   ) AS `t4` ON  t3.id = t4.cid 
ORDER BY `t4`.`suma` DESC

minde's Website

3 Reply by neworld (edited by neworld 2009-01-10 23:55:29)

  • Registered: 2007-01-08
  • Posts: 256

Re: Kažkas negerai su subqueriais

gražu, ačiū

Tik dar group vienoj vietoj trūko:

SELECT `t3`.`id` , `t3`.`title` , `t3`.`tag` , `t4`.`num` , `t4`.`suma` 
FROM `clans` AS `t3` 
INNER JOIN (
  SELECT `t1`.`cid` , COUNT( * ) AS `num` , SUM( t2.exp ) AS `suma` 
  FROM `clan_members` AS `t1` 
  INNER JOIN `users` AS `t2` 
    ON t2.id = t1.user
  GROUP BY `t1`.`cid`      <----- čia
) AS `t4` 
  ON t4.cid = t3.id
ORDER BY `t4`.`suma` DESC
agonija - http://www.agonija.eu
neworld - http://www.neworldwar.com
neuron  - http://neuron.avita.lt