473,581 Members | 2,694 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

left outer join/inner join statement

46 New Member
Here I have a code for a view that has been created by a developer on my team. I am trying to use the very same code to create a view for a different formid/quesid. But I cannot figure out how this one starts and ends. can someone please help.
here's the code from the developer.
Expand|Select|Wrap|Line Numbers
  1. SELECT     a.EvalRecNo, w1.q1, w2.q2, w3.q3, w4.q4, w5.q5, w6.comment
  2. FROM         (SELECT DISTINCT u.EvalRecNo
  3.                        FROM          dbo.UData AS u INNER JOIN
  4.                                               dbo.EvalIssue AS e ON u.EvalRecNo = e.RecNo
  5.                        WHERE      (e.FormNo = 'R507') AND (e.DateComplete IS NOT NULL)) AS a LEFT OUTER JOIN
  6.                           (SELECT     u.EvalRecNo, u.NumAns AS q1
  7.                             FROM          dbo.UData AS u INNER JOIN
  8.                                                    dbo.UFormQuestion AS fq ON u.QuesFormID = fq.QuesFormID
  9.                             WHERE      (fq.QuesID = 858)) AS w1 ON a.EvalRecNo = w1.EvalRecNo LEFT OUTER JOIN
  10.                           (SELECT     u.EvalRecNo, u.NumAns AS q2
  11.                             FROM          dbo.UData AS u INNER JOIN
  12.                                                    dbo.UFormQuestion AS fq ON u.QuesFormID = fq.QuesFormID
  13.                             WHERE      (fq.QuesID = 859)) AS w2 ON a.EvalRecNo = w2.EvalRecNo LEFT OUTER JOIN
  14.                           (SELECT     u.EvalRecNo, u.NumAns AS q3
  15.                             FROM          dbo.UData AS u INNER JOIN
  16.                                                    dbo.UFormQuestion AS fq ON u.QuesFormID = fq.QuesFormID
  17.                             WHERE      (fq.QuesID = 860)) AS w3 ON a.EvalRecNo = w3.EvalRecNo LEFT OUTER JOIN
  18.                           (SELECT     u.EvalRecNo, u.NumAns AS q4
  19.                             FROM          dbo.UData AS u INNER JOIN
  20.                                                    dbo.UFormQuestion AS fq ON u.QuesFormID = fq.QuesFormID
  21.                             WHERE      (fq.QuesID = 861)) AS w4 ON a.EvalRecNo = w4.EvalRecNo LEFT OUTER JOIN
  22.                           (SELECT     u.EvalRecNo, u.NumAns AS q5
  23.                             FROM          dbo.UData AS u INNER JOIN
  24.                                                    dbo.UFormQuestion AS fq ON u.QuesFormID = fq.QuesFormID
  25.                             WHERE      (fq.QuesID = 862)) AS w5 ON a.EvalRecNo = w5.EvalRecNo LEFT OUTER JOIN
  26.                           (SELECT     u.EvalRecNo, u.TxtAns AS comment
  27.                             FROM          dbo.UData AS u INNER JOIN
  28.                                                    dbo.UFormQuestion AS fq ON u.QuesFormID = fq.QuesFormID
  29.                             WHERE      (fq.QuesID = 865)) AS w6 ON a.EvalRecNo = w6.EvalRecNo
  30.  
*************** ***************

I'd like to do the exact same thing with this code:
Expand|Select|Wrap|Line Numbers
  1. select b.quesid, b.formno, b.questext, d.numans, d.txtans from uquestionbank b, uformquestion f, udata d
  2. where b.quesid = f.quesid
  3. and f.quesformid = d.quesformid
  4. and b.formno = 'R102'
  5. and f.quesid in (486,519,522)
Can someone please tell me how the first query works because everytime I try to interpret it and make my own - i end up in circles.

Thank you,
Dec 11 '07 #1
1 4594
debasisdas
8,127 Recognized Expert Expert
The first query is a inline view and the view is based on a number of joins.

Refer the person who has developed it because i don't have any idea of your table structure.
Dec 13 '07 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

1
6914
by: Justin Hennessy | last post by:
Hi all, I am working with a computer hardware asset database and I am trying to get information out of it for each PC in my organisation. Here is the basic table structure: Table1 -> Table2 -> Table3 -> Table4 -> Table5
6
9004
by: Thomas Beutin | last post by:
Hi, i've a speed problem withe the following statement: SELECT DISTINCT pz.l1_id, pz.l2_id, pz.l3_id, pz.l4_id FROM ot_adresse AS a, ot_produkt AS p LEFT OUTER JOIN ot_kat_prod AS pz ON ( p.p_id = pz.p_id ) WHERE p.a_id = a.id AND a.id = '105391105424941' AND a.m_id = '37'; This is terrible slow compared to the inner join: SELECT...
1
2866
by: Bob Alston | last post by:
I am trying to use an access sql statement in a module that does a left outer join on one table - joined to a table that has a selection criteria. The result is an inner join. If I do this in two pieces using queries, it works correctly. If I remove the criteria, it works correctly but of course gives me more matches than I want. ...
3
17829
by: Doug | last post by:
Hi, I'm more familiar with MSSQL than Access syntax and have run into a problem with correctly putting ( )'s around the joins in a 3 table query. I want to INNER JOIN lenders and accounts and LEFT OUTER JOIN that result with prospects. (I want to receive all the results of the inner join and any pertinent info from table 3 that is...
3
6631
by: media.opslag | last post by:
Hi, How can i get this to work in access / jet sql ??? Someone?? SELECT tbl1., tbl2. FROM tbl1 left outer join tbl2 on
2
3135
by: tricard | last post by:
Good day all, I have a large outer joined query that I want to have some criteria. The select query is gathering all part numbers from tblPartNumbers, left joining to tblPartNumberVendor (since more than one vendor can make the part), then left joining to tblPartNumberSupplier (since more than one supplier can distribute the vendor's part),...
3
2687
by: rrstudio2 | last post by:
If I have two tables and need to do a left outer join and include a where statement on the second table, it seems like the left outer join becomes an inner join. For example: Table: Names id Name StatusCode 1 John 11 2 Henry 22 3 Bob 33
4
2708
by: polycom | last post by:
Assistance needed to optimize this query SELECT SD.content_id AS Id, SD.title AS Title, CT.name AS Contenttype, PV.content_id AS SponsorId, PV.display_name AS Sponsor, CONCAT_WS("", NT.title, " - ", N.pubdate, " ") AS ListGroup,
9
11913
by: shapper | last post by:
Hello, I am used to SQL but I am starting to use LINQ. How can I create Left, Right and Inner joins in LINQ? How to distinguish the different joins? Here is a great SQL example: http://www.codinghorror.com/blog/archives/000976.html
0
7854
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
8132
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8296
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7878
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
8157
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6533
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
0
3790
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3806
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1394
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.