Solved

SQL Join from another table.

Posted on 2013-01-18
6
273 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 100 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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 400 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: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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

Suggested Solutions

Title # Comments Views Activity
Freeze portion of datamart 2 21
Parsing this XML works but the other one doesn't 9 27
SQL works but want to get the XML node data separately 11 26
Need SSIS project 2 24
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
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…
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.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

763 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