2

Okay, so I've got two tables. One table (table 1) contains a column Books_Owned_ID which stores a series of numbers in the form of 1,3,7. I have another table (table 2) which stores the Book names in one column and the book ID in another column.

What I want to do is create an SQL code which will take the numbers from Books_Owned_IDand display the names of those books in a new column. Like so: 

|New Column  |
 Book 1 Name
 Book 2 Name
 Book 3 Name

I can't wrap my head around this, it's simple enough but all the threads I look on get really confusing.

Table1 contains the following columns:

|First_Name| Last_Name| Books_Owned_ID |

Table2 contains the following columns:

|Book_Name|Book_ID|
Taryn
  • 242,637
  • 56
  • 362
  • 405
user2980316
  • 139
  • 1
  • 3
  • 13
  • What DB are you using? – user2989408 Jan 09 '14 at 18:14
  • are the tags `Books_Owned_Id` and `Book_ID` the same? if so you can join on them – MCP_infiltrator Jan 09 '14 at 18:14
  • 1
    Are the values in "Books_Owned_ID" really a comma delimited list of "Book_ID"'s? If so, that's why you are having trouble. You need a 3rd 1-to-many table that connects "table 1" with "table 2". – David Jan 09 '14 at 18:16
  • I'm using MS SQL Server with the MS Management Studio. – user2980316 Jan 09 '14 at 18:18
  • Wait, no, uh... This is a design issue in your datastructure. You probably want three tables instead of two: People, jtPeople_Books, and Books. The current design will probably cause you difficulties in the future. – Dylan Brams Jan 09 '14 at 18:19
  • I'm thinking separating by commas is a bad idea, is there a better way to separate the numbers in the one column? – user2980316 Jan 09 '14 at 18:21
  • @DylanB - you don't need a cursor for this, you can use FOR XML – Hogan Jan 09 '14 at 18:21
  • separating by commas is a bad idea, you need another table – Hogan Jan 09 '14 at 18:21
  • In TSQL you can use [STUFF and FOR XML](http://stackoverflow.com/questions/12671117/comma-separated-values-with-sql-query) to get a comma separated list. – user2989408 Jan 09 '14 at 18:22
  • @user2989408 - that is only useful to go from a relational to a comma separated list -- this data is starting as comma separated -- FOR XML won't help – Hogan Jan 09 '14 at 18:27

3 Answers3

2

You need to do an inner join. This is a great example/reference for these 

SELECT Book_Name FROM Table2
INNER JOIN Table1
ON Table1.Books_Owned_ID = Table2.Book_ID

EDIT SQL Fiddle

I will work on getting the column comma split working. It wont be a lot extra for this.

EDIT 2 See this answer to build a function to split your string. Then you can do this:

SELECT Book_Name FROM Table2 
WHERE Book_ID IN(SELECT FN_ListToTable(',',Table1.Books_Owned_ID) FROM Table1 s)
Community
  • 1
  • 1
ProfessionalAmateur
  • 4,447
  • 9
  • 46
  • 63
1

You need a function which takes a comma separated list and returns a table. This is slow and fundamentally a bad idea. Really all this does is convert this way of doing it to be like the data model I describe below. (see ProfessionalAmateur's answer for an example of this).

If you are just starting change your data model. Make a linking table. Like this:

Okay, so I've got two tables. One table (table 1) contains a column Books_Owned_ID which stores a series of numbers in the form of 1,3,7. I have another table (table 2) which stores the Book names in one column and the book ID in another column.

What I want to do is create an SQL code which will take the numbers from Books_Owned_IDand display the names of those books in a new column. Like so:

Person Table 

 |First_Name| Last_Name| Person_ID |

Book Table

 |Book_Name|Book_ID|

PersonBook Table

 |PersonID|BookID|

This table can have more than one row for each person.

Hogan
  • 69,564
  • 10
  • 76
  • 117
1

The core of this centers around data normalisation... Each fact is stored only once (and so is "authoritative"). You should also get into the habit of only storing a single fact in any field.

So, imagine the following table layouts...

Books
    Id, Name, Description

Users
    Id, Username, EmailAddress, PasswordHash, etc....

BooksOwned
    UserId, BookId

So if a single user owns multiple books, there will be multiple entries in the BooksOwned table...

UserId, BookID
1, 1
1, 2
1, 3

Indicates that User 1 owns books 1 through 3.

The reason to do it this way is that it makes it much easier to query in future. You also treat BookId as an Integer instead of a string containing a list - so you don't need to worry about string manipulation to do your query.

The following would return the name of all books owned by the user with Id = 1

SELECT Books.Name
FROM   BooksOwned
       INNER JOIN Books
           ON BooksOwned.BookId = Books.Id
WHERE  BooksOwned.UserId = 1
Basic
  • 26,321
  • 24
  • 115
  • 201