SQL Join from another table.

Posted on 2013-01-18
Medium Priority
Last Modified: 2013-01-22
I sure this is not the way  to go about doing it.. (quick dirty back ground)  The ERP system we have allows for a creation of a single database field to be added to a Screen.  Most users only have a single location they need to see data from.  Some power users need to see serveral different locations.

I have a table called tbl_userlocations .   Each user will be assigned to a location.  Some user needs to be assigned to serveral locations. (I can not break the location into multiple lines the interface will not allow it)

userid    location (varchar(50))
ed             123
jim           111, 123

Target Db.
Pono, locationid, itemno
556        123           pr32  
323         111           pr45
219        123            pr55

What I need is to be able to report pull the data that is assigned the user.
so for  Jim it needs to report
556        123           pr32    jim
219        123            pr55  jim
323         111           pr45  jim

I can do it if the locationid was a one off.  I'm not sure how to do it when all the locations i need to report are in one field.

It goes into a report that uses a SQL statement.  

Thanks .
Question by:edwardq
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 38794847
SELECT *, userID = (select userid  from tbl_userLocations where location =locationid or location =cast(locationID as varchar) +','  or location = ','+cast(locationID as varchar)  )
from Target
LVL 35

Expert Comment

by:David Todd
ID: 38795683
Hi Ed,

What version of SQL?

That is, if 2005 or better, then can use a function to split the comma delimited list and return a table with the locations, which you can join to TargetDB.

LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 400 total points
ID: 38796218
this article's function dbo.ParmsToList() can be used to implement what dtodd is referring to:

select l.userid, t.locationid, t.pono, t.itemno
  from tbl_userlocations l
  cross apply dbo.ParmsToList(l.location, ',') f
  join target_db t
    on t.locationid = f.value

Open in new window

this said, the table design for tbl_userlocations is not normalized, you should have 1 row per user / location instead of having 1 row with the locations concatenated like this.
Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.


Author Comment

ID: 38796485
The database is SQL 2008..    I'll try the solutions on Monday.

Yes I know that the table is not normalized. Its just how I have to do it arm.  If all fails, I can create the table into the ERP system manually and normalize it..  But we really don't want to be adding tables to the ERP database.  This creates a "customized" ERP system that is not supported by the ERP company.  It also has to be maintained by IT dept unstead by the ERP select admins.   The ERP company does allow for custom fields to be put into user screen.
LVL 32

Accepted Solution

awking00 earned 1600 total points
ID: 38801720
select b.pono, b.locationid,b.itemno, a.userid
from tbl_userlocations as a,
targetdb as b
where a.location like '%' + locationid + '%'
  and a.userid = 'jim'
order by b.locationid desc;

Author Closing Comment

ID: 38807305
Thanks for your replys.   I had to use awking00's solution as I can not add functions to the ERP database.  angelIII one thing on your function to work would be be to trim the fields that you spilt up.   Your solution worked on my test server if there was no space between the delimiters.

Thanks again.

Featured Post

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

587 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question