1
MS-2001
SQL String - Question
  • 2005/7/3 9:36

  • MS-2001

  • Quite a regular

  • Posts: 204

  • Since: 2004/7/27


$xcgalAlbums $xoopsDB->prefix("xcgal_albums");
$xcgalPictures $xoopsDB->prefix("xcgal_pictures");
$select_columns "COUNT(pid) AS cpid, MIN(pid) AS pid, $xcgalAlbums.description, filepath, filename, url_prefix, filesize, pwidth, pheight, $xcgalPictures.aid, ctime, $xcgalAlbums.title, $xcgalAlbums.thumb";
$result $xoopsDB->query("SELECT $select_columns FROM $xcgalPictures INNER JOIN $xcgalAlbums ON $xcgalAlbums.aid=$xcgalPictures.aid WHERE approved = 'YES' GROUP BY $xcgalPictures.aid ORDER BY $xcgalAlbums.category DESC, $xcgalAlbums.pos LIMIT $count");


Thats a sql-query for the xcgal-block. But i need an other inner join (?) in it to order the results a little different.

i want it to be ordered by $xcgalCategories.pos

join criteria would be: $xcgalAlbums.category=$xcgalCategories.cid

So what should the SQL look like?

2
MS-2001
Re: SQL String - Question
  • 2005/7/4 15:54

  • MS-2001

  • Quite a regular

  • Posts: 204

  • Since: 2004/7/27


It's very important. So please, could you help me?

3
Mithrandir
Re: SQL String - Question

"SELECT $select_columns FROM $xcgalPictures INNER JOIN $xcgalAlbums ON $xcgalAlbums.aid=$xcgalPictures.aid INNER JOIN $xcgalCategories ON $xcgalAlbums.category=$xcgalCategories.cid WHERE approved = 'YES' GROUP BY $xcgalPictures.aid ORDER BY $xcgalCategories.pos DESC, $xcgalAlbums.pos LIMIT $count"
"When you can flatten entire cities at a whim, a tendency towards quiet reflection and seeing-things-from-the-other-fellow's-point-of-view is seldom necessary."

Cusix Software

4
MS-2001
Re: SQL String - Question
  • 2005/7/4 18:09

  • MS-2001

  • Quite a regular

  • Posts: 204

  • Since: 2004/7/27


Thanks, it works. I just didn't know that it is possible to use two INNER JOINs

5
Mithrandir
Re: SQL String - Question

You can use as many as you want :)
"When you can flatten entire cities at a whim, a tendency towards quiet reflection and seeing-things-from-the-other-fellow's-point-of-view is seldom necessary."

Cusix Software

6
MS-2001
Re: SQL String - Question
  • 2005/7/4 18:18

  • MS-2001

  • Quite a regular

  • Posts: 204

  • Since: 2004/7/27


Btw. its "ORDER BY $xcgalCategories.pos", not "ORDER BY $xcgalCategories.pos DESC"
but thanks anyway

Login

Who's Online

149 user(s) are online (113 user(s) are browsing Support Forums)


Members: 0


Guests: 149


more...

Donat-O-Meter

Stats
Goal: $100.00
Due Date: Nov 30
Gross Amount: $0.00
Net Balance: $0.00
Left to go: $100.00
Make donations with PayPal!

Latest GitHub Commits