0

I have a table with the structure

book_id int(11),
section_id int(11),
note_id int(11),
item_number int(11),
text varchar(255),
UNIQUE(book_id,section_id,note_id,item_number)

item_number can be 1 or 2 only.

How can I make a SELECT query to get text for item_number 1 and 2 together for each book_id,section_id,note_id

SELECT book_id, select_id, note_id, TEXT_number_1, TEXT_number_2

TEXT_number_1 = text (WHERE item_number=1)
TEXT_number_2 = text (WHERE item_number=2)

I considered two options:

  1. GROUP BY, which can give me a CONCAT(TEXT_number_1,TEXT_number_2)

  2. JOIN, which I am not sure what is the best query.

  • Question is unclear - where does number_2 come from? I'm assuming number_1 is number INT (11)? BTW, I would call number INT(11) something like book_number INT(11) as NUMBER is a reserved word - although perhaps not for MySQL (yet...) – Vérace Sep 1 '18 at 20:54
  • @Vérace sorry about that. I edited the question to clarify. – Googlebot Sep 1 '18 at 21:00
1
SELECT t0.book_id, t0.select_id, t0.note_id, t1.TEXT_number_1, t2.TEXT_number_2
FROM (SELECT DISTINCT book_id, select_id, note_id
      FROM `table`) t0
LEFT JOIN `table` t1 ON t0.book_id=t1.book_id
                     AND t1.item_number=1   
                  /* AND t0.select_id=t1.select_id 
                     AND t0.note_id=t1.note_id */
LEFT JOIN `table` t2 ON t0.book_id=t2.book_id 
                     AND t2.item_number=2   
                  /* AND t0.select_id=t2.select_id 
                     AND t0.note_id=t2.note_id */
1

Use GROUP_CONCAT, but be sure to increase group_concat_max_len.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.