Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL Join from another table.

Posted on 2013-01-18
6
Medium Priority
?
279 Views
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.

*Details*
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)

tbl_userlocations
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 .
Ed.
0
Comment
Question by:edwardq
6 Comments
 
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
0
 
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.

HTH
  David
0
 
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:
http://www.experts-exchange.com/Database/Miscellaneous/A_1536-delimited-list-as-parameter-what-are-the-options.html

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.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 4

Author Comment

by:edwardq
ID: 38796485
Thanks,
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.
0
 
LVL 32

Accepted Solution

by:
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;
0
 
LVL 4

Author Closing Comment

by:edwardq
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.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

885 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