CODEWITHSUNDEEP
mysqlsql
ocinisme
ocinisme

Reputation: 301

Matching Different Value within One Row

I want to perform a query whereby I want to check whether on the columns A has either certain values. A could have only X , X and Y or a combination of X Y and Z.

To give a better understanding. I am checking a book's author within a table itself. The table has the BOOK_ID , BOOK_TITLE , AUTHOR_NAME, AUTHOR_ORDER.

So a book might have 1,2 or 3 authors, listed in order written inside the AUTHOR_ORDER row. I am trying very hard to reach an output where if a book has 3 authors, it will display accordingly from the first author to the third author. I am now stuck in the part where I need to compare the value and present it in the output.

Any idea how to achieve this in MYSQL output?

Sample :

enter image description here

The output result is more or less like this:

If the title has au_ord of 1,2 and 3, there shall be a new column with all the authors name listed in ascending.

So for example, for title BU1032, the Author row will be Bennet, Green

Upvotes: 1

Views: 72

Answers (1)

GarethD
GarethD

Reputation: 69819

I think GROUP_CONCAT is what you are after:

SELECT  Title_ID, 
        Title,
        GROUP_CONCAT(au_LName ORDER BY au_Ord) AS Authors
FROM    Books
GROUP BY Title_ID, Title;

SQL FIDDLE

Upvotes: 2

Related Questions