Inline view with a left join

Hi all,

I have three tables A, B and C.

I first want to run a simple select on tables B&C with a join and then
run a left join the result with A.

So:

Select A.a1, A.a2, A.a3..., BC.bc1, BC.bc2...
From A, (select bc1, bc2, bc3,... from B, C, where B.some_col =
C.some_col) BC
left join on A.another_col = BC.another_col.

What is the right syntax for this?
TIA,
Sashi
0
Sashi
5/20/2010 4:24:58 PM
sqlserver.programming 1873 articles. 0 followers. Follow

1 Replies
1274 Views

Similar Articles

[PageSpeed] 44

Here is one way to write the query:

SELECT A.a1, A.a2, A.a3..., BC.bc1, BC.bc2...
FROM A
LEFT JOIN (
SELECT bc1, bc2, bc3,... 
FROM B
JOIN C 
  ON B.some_col = C.some_col) AS BC
  ON A.another_col = BC.another_col;

-- 
Plamen Ratchev
http://www.SQLStudio.com
0
Plamen
5/20/2010 5:08:28 PM
Reply:

Similar Artilces:

Exclude Records based on a joined field?
Hi, I have a table containing transaction data which includes a 'SoldDate' field. This table contains records relating to all days of the week. However, I want to exclude records relating to Saturdays in some of my queries. So I have imported another table containing the dates of all Saturdays. I can add this table to my queries and join the two date fields to select all Saturdays, but how do I do likewise to *exclude* all Saturdays. In effect, not include records where the joined fields are equal. Any help would be greatly appreciated. Regards Jason You do not need a ...

MSDNs "Recordset: Performing a Join"
I'm using http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vccore98/HTML/_core_recordset.3a_.performing_a_join_.28.odbc.29.asp to perform some actions that I would like to perform. Since I don't want to have to rely on the user creating views for the application, I'm dynamically figuring out how to connect to different tables using a join. I have all of the SQL statement construction complete for different types of joins...but I've tried to insert an SQL statement into CRecordset::Open(), and this overwrites the m_strFilter, so I can't run SQL statements i...

New View
I'd like to create a new account view exactly like the New Leads-This week view. How do you tell CRM the data range or is this a view that can be copied from leads to accounts? when you create the new account view you can click the edit filter criteria button and choose the fields you want to filter on. If you choose the field called createdon, you can choose a condition called Last Week or This Week etc. hope this helps -- John O'Donnell Microsoft CRM MVP "debbie" <debbie@discussions.microsoft.com> wrote in message news:2d1eb01c46ab1$71747630$a601280a@phx.gb...

Recordset with data joined from 2 tables?
Hi to all the Gurus out there, I originally developed a database for keeping record of registered competitors & their matching as competitive couples. Now I'm developing a database for recording the points earned at competitions & I use the same back end from the Register database. My difficulty is dealing with couples who are not registered with our organization. I've started by getting the registered couples data & points calculations working correctly. Now I need to incorporate the non registered. I need to keep record of them as I also need to know ...

Strange result when joining tables
Hi, When I'm executing the below query, I get some strange records returned from one of the joined tables. As you can see, the query is very simple and I simply can't see why I get some wrong results back for some of the rows. This is the query that I'm executing: SELECT SR.CustomerNo, SR.Code, SA.CustomerNo, SA.Name, C.Name, C.[No] FROM #SourceRows SR JOIN LinkedServer.sourceDB.dbo.Extract_ShipToAddress SA ON SR.CustomerNo = SA.customerNo AND SR.Code = SA.Code JOIN LinkedServer.SourceDB.dbo.Staging_OTAU.dbo.Extract_Customer C ON C.[No] = SR.CustomerNo ...

Price update in portfolio view
Using Money 2007 Deluxe with Win XP SP2 --- When I open the portfolio view, the "Last Price" column will automatically update if there is no price for the current day. The "Change" column always indicates 'unch' and the "Last Updated" column does not change. How can I prevent this automatic price update? I am not using a Windows Live ID and "Update prices every __ minutes" is not checked. ...

How do I view complete text content in an Excel Comment box?
When scrolling over comment boxes in Excel, I can only partially view note content. Is it possible to specifiy the size of the comment box OR is there any simple method available to view complete contents of comment box while scrolling over? Right-click>Edit Comment>Drag to re-size. Or re-size all using code. http://www.contextures.on.ca/xlcomments03.html#Resize Gord Dibben MS Excel MVP On Sun, 25 Apr 2010 02:41:01 -0700, Deaglan1 <Deaglan1@discussions.microsoft.com> wrote: >When scrolling over comment boxes in Excel, I can only partially view note ...

Can't see inline image with Microsoft Outlook.
Hi, I am sending html message having embedded image in it, When I receive it using Outlook Express it shows me message properly with image, but if I use MS Outlook then I can't see image :( I am adding image as Inline in message. Here is my header for same: Content-Type: application/octet-stream; name=ms_logo.gif Content-Transfer-Encoding: base64 Content-Disposition: inline; filename=ms_logo.gif Content-ID: ms_logo.gif Am I doing anything wrong? I have tried by removing Content-Disposition header. But no use :( Any help would be greatly appreciated. Thanks, Shilpa ...

Format Date in Data Analysis view
When I use the MS Portfolio Analyzer cube and use any of the Time fields, it shows up in a wierd format like "Feb Day 25, Feb Day 26 and so on...Is there a way to make it show only the dates and take out the DAY out of it? ...

View row and column headings
I have a sheet in a workbook that does not display row and column headings and the select all button. How do I re- display? Tools>Options>View tab>check the Row and Column Headers box>OK. -- Greeting from the Gulf Coast! http://myweb.cableone.net/twodays "Rob" <anonymous@discussions.microsoft.com> wrote in message news:072b01c39939$984acb50$a501280a@phx.gbl... > I have a sheet in a workbook that does not display row and > column headings and the select all button. How do I re- > display? ...

Can you view tomorrow's todo items in Outlook Today view?
I have set up a filter that allows me to look at tomorrow's todo items when I use the Task view, but it would be convenient to have them also appear in the Outlook Today view. I can have tomorrow's calendar appear, but not the tasks. Can this be done? Charles ...

Unable to view HTML messages
I am unable to view HTML messages in HTML. All I get is code. I have looked all through the options and do not see anything that says turn HTML off or read email in text only format. What am I missing? ...

join two spreadsheets
How can I join 2 spreadsheets if I want both to update the fields of the main file by the fielda of the update file and to add the records of the update file which aren't in the main file (I vannot use VLOOKUP in this case, or I don't know how): In my case I have the main file with records: F1 F2 F3 .... 1 2 31 2 3 32 3 4 33 7 7 37 and I have to update it from the update file with fields F1 F3 1 51 3 53 5 55 where F1 is the key field as a result I must receive: F1 F2 F3 .... 1 2 51 2 3 32 3 4 53 5 55 7 7 37 How can I do it? I'd create a new worksheet. Copy...

FullText outer joins
I have a procedure I am looking at that can take about 1 - 3 minutes to run depending on how much data is passed back. This is part of the query that is taking most of the time. Sometimes it will take 10 seconds if you put something that passes back about 10 rows. But if it passes back 2400 rows, it takes about a minute. The procedure actually has about 12 CASE statements and 12 LEFT OUTER JOINS (one for each). INSERT INTO @Search_Results SELECT CSrchTbl.Rank, CSrchTbl.ItemID, CSrchTbl.Title,CSrchTbl.ProductID, CSrchTbl.ProductDesc,CSrchTbl.ModuleID,CSrchTbl.ModuleDesc,CSr...

newbie question: Need to create a month-calendar view report
I'm creating a database to track assignments. They are tracked by dates: date assigned, expected completion date, draft completion dates, approvals, etc. Most reports are default types---assignments by staff member, completed in past month/week/etc. The question is 1. How due I plot assignments in a "monthly calendar" view/report? This report should display the assignment titles on the "expected completion" date. 2. Is it possible to change the "expected completion" date from within this "monthly calendar" view? Thanks in advance for any and all a...

Text join formulas working but act as straight text when modified
I have a spreadsheet in which I am using left and right functions to populate a new column. This seems to work if I key the whole thing in at once. When I try to modify the formula, it stops working and displays as straight text. Format of all cells is general. The formula in question is as follows: =LEFT(B5,2)&RIGHT(B5,2)&(A5). It works after I key it, but if I key any change to it, it displays as text only and stops working. Thanks for the help! Ken K. -- akkrug Ken, The behavior you are describing occurs when a cell has a Text format applied after the entry of the form...

unable to join domain
Dear All Pls Help me I having Windowds vista Business edi. on my laptop I trying to join in domain which have sever 2008 std. edi. I got the error " an attempt to resolve the dns name of a dc in the domain being joined has failed.please verify this client is configured to reach a dns server". I diable UAC setting, off windows firewall , made doimain entery in hosts file, also made host entry in AD. ( But when I trying to join windows laptop in domain I join domain without error ). so please help me.Thanks in Advance Thanks & regards Tsachin 9920576393 ...

Left Click
Hia! How are you friends? I have read from a site that there is no VBA code for sending the 'fn' key. Is that right? I use a laptop and without that I can't write codes for left click. If someone knows a way around could I please know it? I have not tried it but on a PC sendkeys "5" should do the left click! Am I right? Many Thanks. I have some documentation here that says SendKeys can send function keys using "{F2}" (for example). I don't see anything in there about mouse buttons, and it isn't obvious to me how you could use ...

Missing Form View
I have a front end containing some 60 forms with subforms. Somehow all the forms connected to one Tabular object (?) show blank. I can see the forms in design view, can see the headers in datasheet view, but cannot see anything in Form view which is where we want to input our data. I have checked all the hidden properties, imported to a new DB and checked my tape backups. It seems as though everytime I open a backup, the forms disapear again. I am using a backup from a date where I know the forms were there because it is our monthly metrics and I did an upload from them last month. I ca...

join exch 5.5 in exch 2000 organization
i will installl exch 5.5 in a winnt domain. should i install ADC on NT before install exch 5.5? should i establish a bidirectional trust releationship or is enough one way? in what direction if is one way? does anyone known an article in Microsoft KB about this? The ADC is only needed if you have an existing Exchange 5.5 organization and wish to migrate the 5.5 directory information into Active Directory in preparation for an upgrade to Exchange 2000 or 2003. Why would you need a trust when it sounds like you only have one NT domain? If your environment is more complex than that, plea...

Viewing all mailboxes with Service Account
Hi, My administrator account has Service Admin Account rights on my exchange 5.5 and if I am logged in as the Admin, I can view anyones Outlook Mailbox. I gave my own email account Service Admin Account rights at the Org, Site, and Config levels but if I try to access someone elses mailbox it says I don't have the right to view it. My login is part of the domain admins group on the DC. Anyone know why it's not working? Basically what I'm hoping to accomplish... Owner of the company wants to be able to view employees mailboxes, I can give him rights on their folders thro...

Joins 02-18-08
I know this is a very simple question but... I'm just learning SQL and have been going through a step by step book. I'm confused about what the difference is between an equi-join and an inner join. Are both the inner join and the outer join just 2 different types of equi-joins? Or is the equi-join something different than both? Thank you, -- RJF An equi join is a join implying only the operation equal, =. A Cartesian join, or Cross join, produces a result where each row of the first table is horizontally merged with each row of the second table. An inner join is logical...

when the size of view change,how can I also change size of the font,chart in this view
all: I want to change the size of font and chart go with the view's size changing. what can I do . thx for anyone respone. &best regards. terrcy.j terrcy wrote: > all: > > I want to change the size of font and chart go with the view's size > changing. > > what can I do . > > > thx for anyone respone. > > &best regards. > > terrcy.j > > Handle the WM_SIZE message in the view. It is passed the new size. To change the font you will have to call the CFont's DeleteOjbect, then cal...

View forwarded headers
I'm using Outlook 2000, I was forwarded an email with previous correspondance. Is there any way that I can view someones email address that was in the previous emails? I can only seem to view the header details of the person that sent me the email and no-one elses. Alex <alex.geo@nospam.com> wrote: > I'm using Outlook 2000, I was forwarded an email with previous > correspondance. Is there any way that I can view someones email > address that was in the previous emails? I can only seem to view the > header details of the person that sent me the email and no-one el...

Customize Product Associated View
In product customization page "Edit Associated view" task does not work ? How can we customize Associated view of Product in Quotes ,Orders invoices. ? best regards Timucin Devirmis Mobitek I am not sure what you mean by "does not work", would you please provide additional details (error messages, etc). I believe that editing that View should change how Products are listed in all Associated Views you described (Q/O/I). Thanks, Aaron Elder =============================================== Microsoft Customer Relationship Management Core Application Development Team ...