Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Join from another table.

Posted on 2013-01-18
6
Medium Priority
?
277 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

670 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