Solved

SQL Join from another table.

Posted on 2013-01-18
6
269 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 142

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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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 31

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Truncate vs Delete 63 87
MS SQL Backup 24 70
c# code 19 57
search for a string in all tables 4 15
In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

708 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now