CODEWITHSUNDEEP
sqlsql-server
user2980316
user2980316

Reputation: 149

SQL Cross-Table Referencing

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|

Upvotes: 2

Views: 417

Answers (3)

ProfessionalAmateur
ProfessionalAmateur

Reputation: 4563

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)

Upvotes: 2

Basic
Basic

Reputation: 26766

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

Upvotes: 1

Hogan
Hogan

Reputation: 70523

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.

Upvotes: 1

Related Questions