1

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

Dharman
  • 30,962
  • 25
  • 85
  • 135
ocinisme
  • 301
  • 2
  • 10

1 Answers1

2

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

GarethD
  • 68,045
  • 10
  • 83
  • 123
  • Thanks mate, I just realized that the **book_title** is from another table. Does using a JOIN is advisable for this case? – ocinisme Oct 29 '12 at 17:16
  • Yes, it won't affect it much. You should be able to apply the same select/grouping to the query you have used to produce the screenshot in your question. – GarethD Oct 29 '12 at 17:18
  • is there any workaround for this question? Like probably making the use of **IN** syntax? – ocinisme Oct 30 '12 at 03:17
  • It still works with a join, see [this example](http://www.sqlfiddle.com/#!2/07fe4/1) where books and authors are stored in different tables. Hope this helps. – GarethD Oct 30 '12 at 08:46
  • How if I want it to be done in MSSQL? Since GROUP_CONCAT is not applicable there. – ocinisme Oct 30 '12 at 08:52
  • I've done a previous answer listing how to do it in various DBMS. http://stackoverflow.com/questions/10791247/sql-server-possible-pivot-solution/10796492#10796492 – GarethD Oct 30 '12 at 09:13