Solved

How to display values from joined tables

Posted on 2008-10-27
13
180 Views
Last Modified: 2012-05-05
I would like to display information from the tbl_Pass table based on a value from the tbl_Ship table.  This will occur multiple times in my ASP page.

I have a view_ship.asp request that displays the original person that submitted the ship request as well as the last person to modify the request.  I can display the tbl_Ship.ID_P and the tbl_Ship.ID_P_Mod however I want to display the actual name instead of the ID.  My initial thought is have the SQL statements separate for each occurrence however I am not sure how to do this in ASP classic.

Table Structure for tbl_Pass:
ID_P
fld_Full_Name

Table Structure for tbl_Ship:
ID_S
ID_P
ID_P_Mod
fld_Address
0
Comment
Question by:ritschel
[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
  • 7
  • 4
  • 2
13 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 250 total points
ID: 22817399
what about this:
select s.*
   , p.fld_Full_name
   , pmod.fld_Full_name mod_Full_name
  from tbl_Ship s
  join tbl_pass p
    on p.ID_P = s.ID_P
  join tbl_pass pmod
    on pmod.ID_P = s.ID_P_MOD

Open in new window

0
 

Author Comment

by:ritschel
ID: 22817523
Am I wrong in thinking I can do this like the select boxes where I lookup the value; individually?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22817575
>Am I wrong in thinking I can do this like the select boxes where I lookup the value; individually?
the 2 values ID_P and ID_P_mod are the key to the same table, right?
so, you can have 2 select boxes with the same list of people, and store the relevant ID_P into the 2 fields...
I don't really understand your question about your "thinking", I must assume you are thinking loud?
0
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

 

Author Comment

by:ritschel
ID: 22817810
>the 2 values ID_P and ID_P_mod are the key to the same table, right?
>so, you can have 2 select boxes with the same list of people, and store the relevant ID_P into the 2 fields...
>I don't really understand your question about your "thinking", I must assume you are thinking loud?

Yes the 2 values ID_P and ID_P_Mod are the key to the same table.
I am trying display tbl_Pass.fld_Full_name with relation to tbl_Ship.ID_P with a response.write
Then in a separate SQL statement display tbl_Pass.fld_Full_name with relation to tbl_Ship.ID_P_Mod with a response.write

I mentioned the select box because we use a separate SQL statement for each select box.  I would like to use a separate SQL statement for each response.write display if possible.  Just do not know how to write the code.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22817864
did you try out my above query?
0
 

Author Comment

by:ritschel
ID: 22818197
Tried the above query and it crashes the page with a Syntax error in FROM clause:
strsql = "Select s.*, p.fld_Full_Name, pmod.fld_Full_Name from tbl_Ship s join tbl_Pass p on p.ID_P = s.ID_P join tbl_Pass pmod on pmod.ID_P = s.id_P_Mod WHERE [ID_S]=" & tkey
0
 

Author Comment

by:ritschel
ID: 22818490
I am trying to do something like this however I receive a data mismatch error when I do:
<% 
Dim p_rs, x_fld_Full_Name
set adoCon = Server.CreateObject("ADODB.Connection")
adoCon.open(strCon)
sql = "SELECT DISTINCT ID_P, fld_Full_Name from tbl_Pass where ID_P = '" & Server.HTMLEncode("x_ID_P") & "'"
Set rs = adoCon.execute(sql)
p_rs = rs.GetRows()
rs.close
set rs = nothing
adoCon.close
set adoCon = nothing
%>                    
  
<input type="hidden" name="x_ID_P" size="30" maxlength="50" value="<%= Server.HTMLEncode(x_ID_P&"") %>">
 
<%'Build Array 
for i = 0 to UBound(p_rs,2) 
x_fld_Full_Name = p_rs(0, i)
%>
<%Next%>
<%=x_fld_Full_Name%>

Open in new window

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22819673
what data type is the field ID_P ?
0
 
LVL 5

Expert Comment

by:jfmador
ID: 22821364
Hi, under access you need to add () on each join, it is weird but it is probably why the query crash on your from clause.

strsql = Select s.*, p.fld_Full_Name, pmod.fld_Full_Name from (tbl_Ship s join tbl_Pass p on p.ID_P = s.ID_P) join tbl_Pass pmod on pmod.ID_P = s.id_P_Mod WHERE [ID_S]=" & tkey
0
 
LVL 5

Assisted Solution

by:jfmador
jfmador earned 250 total points
ID: 22821369
Sorry I forgot a " while doing a copy past

strsql = "Select s.*, p.fld_Full_Name, pmod.fld_Full_Name from (tbl_Ship s join tbl_Pass p on p.ID_P = s.ID_P) join tbl_Pass pmod on pmod.ID_P = s.id_P_Mod WHERE [ID_S]=" & tkey
0
 

Author Comment

by:ritschel
ID: 22828414
The last sql select statements gives me a: Syntax error in JOIN operation.  

What I would really like to do is call the value to be displayed similar to the code I posted.  The problem with my code is it gives me two values; the top value and the value I am looking for.  I would prefer to call the values individually as needed.
0
 

Author Comment

by:ritschel
ID: 22828616
Found the answer by using Left Outer Join.  Thanks for all the ideas and help.
strsql = "Select * From (tbl_Ship s Left Outer Join tbl_Pass p ON s.ID_P = p.ID_P) Left Outer Join tbl_Pass pmod on s.ID_P_Mod = pmod.ID_P WHERE [ID_S]=" & tkey

Open in new window

0
 

Author Closing Comment

by:ritschel
ID: 31510550
Thanks for your help.  My lack of knowledge prevented me from seeing what you where trying to show me.  Always learn something from you experts.
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Suggested Solutions

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:   The Exchange of informatio…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

710 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