473,595 Members | 2,619 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

help with SQL coding question - 3 tables with outer join needed

I have three tables:

table1:
table2_ID
table3_ID
complete

table3:
table3_ID
name

table2:
table2_ID
table4_ID

Given table3.table3_I D, I need to retrieve the value of table1.complete OR
"Not Complete".

I have tried this in several different iterations without success.

SELECT
IF(ISNULL(e.com plete), e.complete,'Not Complete') as complete
FROM table3 s
RIGHT OUTER JOIN table1 e ON e.table3_ID = s.table3_ID
INNER JOIN table2 t ON t.table2_ID = e.table2_ID
WHERE s.table3_ID = 2993
AND t.table4_ID = 10029

Any ideas are much appreciated!
Jan 23 '06 #1
2 1802
"Notgiven" <no*********@in valid.invalid> wrote in message
news:aT******** *********@bigne ws1.bellsouth.n et...
I have three tables:

table1:
table2_ID
table3_ID
complete

table3:
table3_ID
name

table2:
table2_ID
table4_ID

Given table3.table3_I D, I need to retrieve the value of table1.complete OR
"Not Complete".

I have tried this in several different iterations without success.

SELECT
IF(ISNULL(e.com plete), e.complete,'Not Complete') as complete
FROM table3 s
RIGHT OUTER JOIN table1 e ON e.table3_ID = s.table3_ID
INNER JOIN table2 t ON t.table2_ID = e.table2_ID
WHERE s.table3_ID = 2993
AND t.table4_ID = 10029


I think you have RIGHT OUTER JOIN when you mean LEFT OUTER JOIN. You're
trying to find real values in s, even when there is no matching value in e.
But the RIGHT OUTER JOIN in the order you are using it above is the reverse
of that -- all rows of e, and show NULLs in s if there are no matching rows.

Also, by using WHERE t.table4_ID = 10029, you've limited the query to rows
where you have values in t, and therefore because you've used INNER JOIN,
there must be values in e. So you've omitted the cases where the outer join
gives you NULLs.

I'd do it this way:

SELECT COALESCE(e.comp lete, 'Not Complete')
FROM table3 AS s
LEFT OUTER JOIN table1 AS e ON e.table3_ID = s.table3_ID
LEFT OUTER JOIN table2 AS t ON t.table2_ID = e.table2_ID AND t.table4_ID =
10029
WHERE s.table3_ID = 2993

This may not be exactly what you intended; I can't tell from your
description whether you want all rows of e that match s, or only rows of e
that match both s and the subset of rows in t matching 10029.

Regards,
Bill K.
Jan 23 '06 #2
"Bill Karwin" <bi**@karwin.co m> wrote in message
news:dr******** *@enews4.newsgu y.com...
"Notgiven" <no*********@in valid.invalid> wrote in message
news:aT******** *********@bigne ws1.bellsouth.n et...
I have three tables:

table1:
table2_ID
table3_ID
complete

table3:
table3_ID
name

table2:
table2_ID
table4_ID

Given table3.table3_I D, I need to retrieve the value of table1.complete
OR "Not Complete".

I have tried this in several different iterations without success.

SELECT
IF(ISNULL(e.com plete), e.complete,'Not Complete') as complete
FROM table3 s
RIGHT OUTER JOIN table1 e ON e.table3_ID = s.table3_ID
INNER JOIN table2 t ON t.table2_ID = e.table2_ID
WHERE s.table3_ID = 2993
AND t.table4_ID = 10029


I think you have RIGHT OUTER JOIN when you mean LEFT OUTER JOIN. You're
trying to find real values in s, even when there is no matching value in
e. But the RIGHT OUTER JOIN in the order you are using it above is the
reverse of that -- all rows of e, and show NULLs in s if there are no
matching rows.

Also, by using WHERE t.table4_ID = 10029, you've limited the query to rows
where you have values in t, and therefore because you've used INNER JOIN,
there must be values in e. So you've omitted the cases where the outer
join gives you NULLs.

I'd do it this way:

SELECT COALESCE(e.comp lete, 'Not Complete')
FROM table3 AS s
LEFT OUTER JOIN table1 AS e ON e.table3_ID = s.table3_ID
LEFT OUTER JOIN table2 AS t ON t.table2_ID = e.table2_ID AND t.table4_ID
= 10029
WHERE s.table3_ID = 2993

This may not be exactly what you intended; I can't tell from your
description whether you want all rows of e that match s, or only rows of e
that match both s and the subset of rows in t matching 10029.

Regards,
Bill K.


Thanks so MUCH!
Jan 23 '06 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

9
3120
by: netpurpose | last post by:
I need to extract data from this table to find the lowest prices of each product as of today. The product will be listed/grouped by the name only, discarding the product code - I use SUBSTRING(ProductName, 1, CHARINDEX('(', ProductName)-2). I can get this result, but I had to use several views (totally inefficient). I think this can be done in one efficient/fast query, but I can't think of one. In the case that one query is not...
3
6409
by: Prem | last post by:
Hi, I am having many problems with inner join. my first problem is : 1) I want to know the precedance while evaluating query with multiple joins. eg. select Employees.FirstName, Employees.LastName, TerritoryID, Employees.EmployeeID, RegionID, ProductID from Employees
5
3261
by: David Logan | last post by:
Hello, I am trying to construct a query across 5 tables but primarily 3 tables. Plan, Provider, ProviderLocation are the three primary tables the other tables are lookup tables for values the other tables. PlanID is the primary in Plan and Plan Provider ProviderLocation Lookups -------- ---------- ---------------- ----------- PlanID ProviderID ProviderID LookupType
28
3287
by: stu_gots | last post by:
I have been losing sleep over this puzzle, and I'm convinced my train of thought is heading in the wrong direction. It is difficult to explain my circumstances, so I will present an identical make-believe challenge in order to avoid confusing the issue further. Suppose I was hosting a dinner and I wanted to invite exactly 12 guests from my neighborhood. I'm really picky about that... I have 12 chairs besides my own, and I want them all...
7
31547
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.")
5
1168
by: khan | last post by:
i have 2 queries totaldues and totalPayments totaldues has 2 fileds(custID (Group By) amountDue(sum of all invoices dues) and totalPayments consist of CustID(Group By) and Amount_Paid(sum of all payment) thses queries calculate total dues and total payments customer made. then i have another query on the base of these 2 queries which is called custGrandBalance and it consist of customerID,amountDue(which is sum of all dues about this...
2
1427
by: Notgiven | last post by:
I have three tables: table1: table2_ID table3_ID complete table3: table3_ID name
4
1447
by: CK | last post by:
Good Morning, I have a person table with personID. I have a personRate table with personID, rateID, and effectiveDate. I need to select fields from personRate, but I want the fields from the proper record. I need the one child record that has the most current date of the largest rateID. For example a person may have many rate records. I need the record that has
4
2014
by: n | last post by:
Hello! Here is a problem I hope you can point me to a solution. It Problem: A teacher needs to know which lesson to teach. A school has a curriculum with 26 lessons, A-Z. For a given class, a random number of students arrive, each of which has completed a random number of lessons taken at random from the curriculum.
0
7882
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
8260
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. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
8379
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...
1
8017
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 most users, this new feature is actually very convenient. If you want to control the update process,...
0
8250
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
6671
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 launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
3872
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
2391
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
1
1490
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.