我遇到了一个运行查询的问题,我有三个表:
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放在哪里。