Select statement
-
Hi anybody,
Can anyone pls help to figure a select statement for the following situation:
I have four tables:
Customers
CustReport
ReqRef
B2BError
I have written a inner join select statement from three tables and it works well
SELECT Customers.EntKey, Customers.Name, Customers.IDNo1, Customers.IDNo2, Customers.DateBR, Customers.BasicGroup, Customers.Nationality,
ReqRef.ReqID, ReqRef.ReqDate, B2BError.warning
FROM Customers INNER JOIN
CustReport ON Customers.EntKey = CustReport.EntKey INNER JOIN
ReqRef ON CustReport.ReqID = ReqRef.ReqID
My problem now is that i want to join the rows of records from select statement up there with other rows of records from B2BError with the following select statement
SELECT * FROM B2BError WHERE B2BError.Warning=2
I have tried to use FULL OUTER JOIN by writing the following:
SELECT Customers.EntKey, Customers.Name, Customers.IDNo1, Customers.IDNo2, Customers.DateBR, Customers.BasicGroup, Customers.Nationality,
ReqRef.ReqID, ReqRef.ReqDate, B2BError.warning
FROM Customers INNER JOIN
CustReport ON Customers.EntKey = CustReport.EntKey INNER JOIN
ReqRef ON CustReport.ReqID = ReqRef.ReqID FULL OUTER JOIN
B2BError ON ReqRef.ReqID=B2BError.ReqID
But this selection will return rows from first part selection with all records from B2BError. What i want is to return rows from first part and only records of second part where B2BError.Warning=2.
Can anyone pls help me? I need help urgently.
Thanks alot.
=cheongww=
CheongWW
CheongWW -
Please take a look again at the first select statement...it references B2BError.warning but does not include the B2BError table. My guess is that this was a typo. Where is the warning code held in the three tables identified? (Customers, CustReport and ReqRef)
Guarddata-
-
quote:
...What i want is to return rows from first part and only records of second part where B2BError.Warning=2....Not understanding what is the "first part" and what is the "second part". Please explain. What is wrong with this:
SELECT
Customers.EntKey
, Customers.Name
, Customers.IDNo1
, Customers.IDNo2
, Customers.DateBR
, Customers.BasicGroup
, Customers.Nationality
, ReqRef.ReqID
, ReqRef.ReqDate
FROM Customers
INNER JOIN CustReport
ON Customers.EntKey = CustReport.EntKey
INNER JOIN ReqRef
ON CustReport.ReqID = ReqRef.ReqID
INNER JOIN B2BError
ON B2BError.ReqID = ReqRef.ReqID
WHERE B2BError.Warning = 2 -
Thanks Jay - I didn't read the second SQL in enough detail. Cheongww - I would suspect jpipes has given you the answer.
Guarddata-
-
I think cheongww actually means he wants all records from the 2nd query regardless of whether they fit the criteria of the 1st (has a matching record in all tables).
If this is the case a union statement would work. As long as both queries return the same number of columns there will be no problem.
quote:
--------------------------------------------------------------------------------
...What i want is to return rows from first part and only records of second part where B2BError.Warning=2....
--------------------------------------------------------------------------------
Not understanding what is the "first part" and what is the "second part". Please explain. What is wrong with this:
SELECT Customers.EntKey, Customers.Name, Customers.IDNo1, Customers.IDNo2, Customers.DateBR, Customers.BasicGroup, Customers.Nationality, ReqRef.ReqID, ReqRef.ReqDateFROM Customers INNER JOIN CustReport ON Customers.EntKey = CustReport.EntKey INNER JOIN ReqRef ON CustReport.ReqID = ReqRef.ReqID
Union All
SELECT * FROM B2BError WHERE B2BError.Warning=2 --Note
Signature is NULL
-
I think cheongww actually means he wants all records from the 2nd query regardless of whether they fit the criteria of the 1st (has a matching record in all tables).
If this is the case a union statement would work. As long as both queries return the same number of columns there will be no problem.
quote:
--------------------------------------------------------------------------------
...What i want is to return rows from first part and only records of second part where B2BError.Warning=2....
--------------------------------------------------------------------------------
Not understanding what is the "first part" and what is the "second part". Please explain. What is wrong with this:
SELECT Customers.EntKey, Customers.Name, Customers.IDNo1, Customers.IDNo2, Customers.DateBR, Customers.BasicGroup, Customers.Nationality, ReqRef.ReqID, ReqRef.ReqDateFROM Customers INNER JOIN CustReport ON Customers.EntKey = CustReport.EntKey INNER JOIN ReqRef ON CustReport.ReqID = ReqRef.ReqID
Union All
SELECT * FROM B2BError WHERE B2BError.Warning=2 --Note
Signature is NULL
-
Add the table in with a LEFT JOIN and use the B2BError.Warning = 2 in the ON instead of the WHERE clause:
SELECT Customers.EntKey,
Customers.Name,
Customers.IDNo1,
Customers.IDNo2,
Customers.DateBR,
Customers.BasicGroup,
Customers.Nationality,
ReqRef.ReqID,
ReqRef.ReqDate,
CASE WHEN B2BError.warning IS NULL THEN 'No Warning'
ELSE 'Warning'
END
FROM Customers
JOIN CustReport
ON Customers.EntKey = CustReport.EntKey
JOIN ReqRef
ON CustReport.ReqID = ReqRef.ReqID
LEFT JOIN B2BError
ON B2BError.ReqID = ReqRef.ReqID AND B2BError.Warning = 2
-
Calvin, You might want to check why you are submitting duplicate responses. This happened in another topic as well.
Only Cheongww knows for sure, but it is my bet the format of B2BError is different and would not work in a UNION. I think jpipes has the solution here.
Guarddata-
Added later -
Stuck my foot in my mouth with that one. I should have waited until Coutant posted his message which is a viable option as well.
Edited by - guarddata on 07/08/2003 1:49:08 PM
-
Quote:
Calvin, You might want to check why you are submitting duplicate responses. This happened in another topic as well.
Yeah, sorry about that...our ISP's been ridiculously slow today and I clicked submit twice. And my response was garbled...silly me.
Signature is NULL
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply