我遇到了一个运行查询的问题，我有三个表：
categories
| id | name |
| --- | ---------- |
| 1 | Logo |
| 2 | Poster |
| 3 | Stationary |
| 4 | Web |
| 5 | Projects |
| 6 | Sporadic |
photos
| id | portid | image | preveiw |
| --- | ------ | ---------------- | ------- |
| 171 | 30 | a30preview.jpg | 1 |
| 172 | 30 | b30.jpg | |
| 173 | 30 | c30.jpg | |
| 174 | 32 | a32preview.jpg | 1 |
| 175 | 32 | b32.jpg | |
| 176 | 33 | a33preview.jpg | 1 |
| 179 | 33 | b33.jpg | |
| 180 | 41 | a41preview.jpg | 1 |
portfolios
| id | catid | type | title | text | date |
| --- | ------ | ----- | ------------ | ------------ | ------ |
| 30 | 2 | M | xxxxxxxx | xxxxxxxx | xxxxxx |
| 32 | 2 | M | xxxxxx | xxxxxx | xxxxxx |
| 33 | 2 | L | xxxxxxxxxx | xxxxxxxxxx | xxxxxx |
| 41 | 1 | L | xxxx | xxxx | xxxxxx |
| 45 | 2 | L | xxxxx | xxxxx | xxxxxx | <-(This record has no image in 'photos' table, so it's not in 'output')
我想把这些记录作为输出(其中name=‘post’)：
| id | catid | name | type | title | text | date | image <-(first image is the one that has 'photos.preview' = 1)|
| --- | ------ | -------- | ----- | ------------ | ------------ | ------ | ---------------------------------- |
| 30 | 2 | Poster | M | xxxxxxxx | xxxxxxxx | xxxxxx | a30preview.jpg, b30.jpg, c30.jpg |
| 32 | 2 | Poster | M | xxxxxx | xxxxxx | xxxxxx | a32preview.jpg, b32.jpg |
| 33 | 2 | Poster | L | xxxxxxxxxx | xxxxxxxxxx | xxxxxx | a33preview.jpg, b33.jpg |
我尝试过以下SQL语句：
select * from `portfolios`
inner join `categories` on `portfolios`.`catid` = `categories`.`id`
inner join `photos` on `portid` = `portfolios`.`id`
where `categories`.`name` = "Poster"
当然，它不起作用，因为我不知道我应该把GROUP_CONCAT放在哪里。
Considering, that GROUP_CONCAT() is an aggregate function, you have to use GROUP BY to get desired resultset. You may try this:
SELECT
`portfolios`.*,
`categories`.*,
GROUP_CONCAT(
`photos`.`image`
ORDER BY `photos`.`preveiw` DESC
SEPARATOR ', '
) as `image`
FROM
`portfolios`
INNER JOIN `categories` ON `portfolios`.`catid` = `categories`.`id`
INNER JOIN `photos` ON `portid` = `portfolios`.`id`
WHERE
`categories`.`name` = 'Poster'
GROUP BY
`portfolios`.`id`
SELECT portfolios.*, categories.*, GROUP_CONCAT(photos.image) AS image
FROM `portfolios`
inner join `categories` on `portfolios`.`catid` = `categories`.`id`
inner join `photos` on `portid` = `portfolios`.`id`
where `categories`.`name` = "Poster"
GROUP BY portfolios.id
It will be a lot easier if you spell out the columns instead of using *:
select p.id, p.catid, c.name, p.type, p.title
, p.text, p.date. group_concat(ph.image order by case when ph.previw = 1
then 0 else 1
end) as images
from `portfolios` p
join `categories` c
on p.`catid` = c.`id`
join `photos` ph
on ph.`portid` = p.`id`
where c.`name` = "Poster"
group by p.id, p.catid, c.name, p.type, p.title
, p.text, p.date