473,855 Members | 2,068 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

left outer join with subselect

1 New Member
Hi, I am trying to perform a single select of data from 2 tables, table A and table B.

Table B may have none, one or many corresponding rows.

If table B has no corresponding rows then table B values should be set to null.

If table B has one or more corresponding rows then table B values should be set to the corresponding table B row with the lowest DATE.

I think I should do a left outer join on table A and table B but can't work out how to code the MIN(DATE) subselect.

I've tried the following but it doesn't work:

Select
from TABLE_A A LEFT OUTER JOIN TABLE_B B
ON A.REF_NO = B.REF_NO
AND B.DATE =
(SELECT MIN(DATE)
FROM TABLE_B C
WHERE B.REF_NO = C.REF_NO)

Does anyone have any ideas?

Thanks.
Apr 17 '07 #1
4 23959
frozenmist
179 Recognized Expert New Member
I think this small change should work...

Select
from TABLE_A A LEFT OUTER JOIN TABLE_B B
ON A.REF_NO = B.REF_NO
where B.DATE =
(SELECT MIN(DATE)
FROM TABLE_B C
WHERE B.REF_NO = C.REF_NO)

Try this .. hope this was useful
May 2 '07 #2
fgaudin
1 New Member
If you want rows from table A when there are no corresponding rows in B that satisfy the condition then you'll need to add a clause:

Select
from TABLE_A A LEFT OUTER JOIN TABLE_B B
ON A.REF_NO = B.REF_NO
where B.DATE =
(SELECT MIN(DATE)
FROM TABLE_B C
WHERE B.REF_NO = C.REF_NO)
or not exists (select date from table_b b2 where b2.ref_no = a.ref_no)
Oct 28 '09 #3
larry wolf
1 New Member
fgaudin, your answer is perfect. i had the same question and your solution solved it.
Jul 15 '11 #4
mgperson
1 New Member
You helped me as well, fgaudin. Thanks!
Jul 23 '20 #5

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

Similar topics

9
6413
by: Chris Greening | last post by:
I'm seeing a very strange problem with outer joins. The example below replicates the problem: create table data1 (dim1 integer, stat1 float); create table data2 (dim1 integer, stat2 float); insert into data1 values (1,1); insert into data1 values (1,2); insert into data1 values (2,2);
3
10065
by: Dam | last post by:
Using SqlServer : Query 1 : SELECT def.lID as IdDefinition, TDC_AUneValeur.VALEURDERETOUR as ValeurDeRetour FROM serveur.Data_tblDEFINITIONTABLEDECODES def, serveur.Data_tblTABLEDECODEAUNEVALEUR TDC_AUneValeur where def.TYPEDETABLEDECODES = 4
7
31575
by: Steve | last post by:
I have a SQL query I'm invoking via VB6 & ADO 2.8, that requires three "Left Outer Joins" in order to return every transaction for a specific set of criteria. Using three "Left Outer Joins" slows the system down considerably. I've tried creating a temp db, but I can't figure out how to execute two select commands. (It throws the exception "The column prefix 'tempdb' does not match with a table name or alias name used in the query.")
3
23104
by: Ian Boyd | last post by:
i know nothing about DB2, but i'm sure this must be possible. i'm trying to get a client to create a view (which it turns out is called a "Logical" in DB2). The query needs a LEFT OUTER JOIN, but he doesn't know how to do that, or even if he can, and i don't have to time to learn DB2 from scratch right now. The following SQL Query is a trimmed sample of the full View (i.e. Logical) definition - and i would create it on an SQL based...
5
8221
by: Todd | last post by:
Data related to the query I'm working on is structured such that TableA and TableB are 1-many(optional). If an item on TableA has children on TableB, I need to use the Max(tstamp) from Table B in a condition, otherwise I need to use a tstamp from TableA (note:there are additional tables and conditions for this query, but this problem is based around these 2). I attempted having TableB (as B) "left outer joined" to TableA, and a condition...
14
5740
by: mike | last post by:
I'm using postgresl 7.3.2 and have a query that executes very slowly. There are 2 tables: Item and LogEvent. ItemID (an int4) is the primary key of Item, and is also a field in LogEvent. Some ItemIDs in LogEvent do not correspond to ItemIDs in Item, and periodically we need to purge the non-matching ItemIDs from LogEvent. The query is: delete from LogEvent where EventType != 'i' and ItemID in
2
7368
by: Morten K. Poulsen | last post by:
(re-post) Dear list, Please let me know if this is not the list to ask this kind of question. I am trying to optimize a query that joins two relatively large (750000 rows in each) tables. If I do it using a subselect, I can "force" the planner to choose the fastest path. Now, my question is:
1
3288
by: nico3334 | last post by:
I have a query that currently pulls data from a main table and a second table using LEFT OUTER JOIN. I know how to do make another LEFT OUTER JOIN with the main table, but I want to add another LEFT OUTER JOIN to the second table. So I want the third table to be joined through the second table, not the main table. Here is my original code that joins the main table and the second table SELECT t1.supply, t2.inventory, FROM MAIN_TABLE...
1
4614
by: teneesh | last post by:
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. SELECT a.EvalRecNo, w1.q1, w2.q2, w3.q3, w4.q4, w5.q5, w6.comment FROM (SELECT DISTINCT u.EvalRecNo FROM dbo.UData AS u...
0
9903
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 usage, and What is the difference between ONU and Router. Letís take a closer look ! Part I. Meaning of...
0
9754
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10692
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 captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10375
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 choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7084
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5754
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 the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
4567
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4168
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3194
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.