i have following tables events.
events
+---------+---------------+ | column | type | +---------+---------------+ | id | int(11) | | title | varchar(255) | | content | longtext | +---------+---------------+ event_attachment
+----------+---------------------------------+ | column | type | +----------+---------------------------------+ | id | int(11) | | caption | varchar(255) | | type | enum('audio', 'video', 'image') | | path | varchar(255) | | position | int(11) | +----------+---------------------------------+ event_gallery
+---------------+---------+ | column | type | +---------------+---------+ | event_id | int(11) | | attachment_id | int(11) | +---------------+---------+ i can event attachments separate query -
select ea.id, ea.caption, ea.type, ea.path, ea.position `event_attachment` ea ea.id in (select eg.attachment_id `event_gallery` eg eg.event_id = {$event_id}) order ea.position asc; but can not perform join on event_attachment , events directly.
also can event , it's single attachment -
select e.title , eg.attachment_id `events` e join `event_gallery` eg on e.id = eg.event_id group e.id i can not these above 2 queries work , give me entire event details along it's attachments in single query.
what want event list along event attachment @ position 1 every event. event may or may not attachments.
desired output -
+----+-------------------+-------------------------------------------------------+------------------------------------------+----------+--+ | id | title | content | path | position | | +----+-------------------+-------------------------------------------------------+------------------------------------------+----------+--+ | 1 | barclay delacruz | soluta minim eiusmod laborum minima cumque fugiat | e2d80cd9-7e4f-4a3d-92b5-d5c600367499.jpg | 1 | | | 2 | lesley strickland | rem culpa dolor doloremque modi nisi esse exercita... | 2b15b00f-f094-469b-a56f-de7460326110.jpg | 1 | | +----+-------------------+-------------------------------------------------------+------------------------------------------+----------+--+ note question may sound duplicate because english not first language , it's difficult put entire scenario in single question.
this basic stuff!
select e.id event_id , e.title , case when length(e.content) > 10 concat(trim(substr(e.content,1,7)),'...') else e.content end content , ea.id attachment_id , case when length(ea.caption) > 10 concat(trim(substr(ea.caption,1,7)),'...') else ea.caption end caption , ea.type , case when length(ea.path) > 10 concat(trim(substr(ea.path,1,7)),'...') else ea.path end path events e join event_gallery eg on eg.event_id = e.id join event_attachment ea on ea.id = eg.attachment_id ea.position = 1; +----------+------------------+------------+---------------+------------+-------+------------+ | event_id | title | content | attachment_id | caption | type | path | +----------+------------------+------------+---------------+------------+-------+------------+ | 1 | barclay delacruz | soluta... | 1 | c | image | e2d80cd... | | 2 | jeremy ballard | sapient... | 4 | ipsum q... | image | 2b15b00... | +----------+------------------+------------+---------------+------------+-------+------------+ 
Comments
Post a Comment