BirdsOfFire1
asked on
Help with Sql Statement
Help with Sql Statement. Thanks.
My tables are:
Table A Table B Table C
Fields: Fields: Fields:
CarID CarID CarID
OrderNum OrderNum OrderNum
TransOil TransOil TransOil
EngineOil EngineOil EngineOil
Dte_Last_Change Dte_Last_Change
VehicleMileage VehicleMileage
Manuf
Supplier
Three select statements connected with two Union All statements:
{First Statement: This statement gets all records that have a match with Table A and Table B. Records
that exist in both tables should be displayed. Two asterisks are placed beside the CarID in the control. This is OK.}
SELECT a.CarID + '**', a.OrderNum, a.TransOil, a.EngineOil, b.Dte_Last_change, b.VehicleMileage,
FROM (Table_A as a inner join Table_B as b on a.CarID = b.CarID and
a.OrderNum = b.OrderNum)
Union All
{Second Statement: I want to get all records in Table A and Table C records that have a match
less a record that is inTable B that have a match. The primary key is the CarID and OrderNum.
SELECT a.CarID, a.OrderNum, a.TransOil, a.EngineOil, b.Dte_Last_change, b.VehicleMileage
FROM (Table_A as A inner join Table_C as B on a.jon = b.jon and a.task = b.task)
Union All
{Third Statement: I want to get all records that are only in Table A that are not in Table B or Table C.
The primary key is the CarID and OrderNum. One asterisk is placed beside the CarID in the control. }
SELECT distinct a.CarID + '*', a.OrderNum, a.TransOil, a.EngineOil, b.Dte_Last_change, b.VehicleMileage
FROM Table_A as A
LEFT JOIN Table_C as B ON a.CarID = b.CarID and a.OrderNum = b.OrderNum
RIGHT JOIN Table_B as C on a.CarID <> c.CarID and a.OrderNum <> c.OrderNum
WHERE b.CarID is null order by 1
When I run the Statement separately:
Results of First SQL Statement:
CarId OrderNum TransOil EngineOil Dte_Last_change VehicleMileage
A11** 784 good good 04/02/02 8483
B12** 834 good bad 04/15/02 3434
Need Records that have matches in Table A and Table B. The primary key is the CarID and OrderNum.
Results of Second SQL Statement:
CarId OrderNum TransOil EngineOil Dte_Last_change VehicleMileage
A11 784 good good 04/02/02 8483
B12 834 good bad 04/15/02 3434
Need all records from Table A and Table C records that have a match less a record that is inTable B that have a match.
The primary key is the CarID and OrderNum.
Results of Third SQL Statement:
CarId OrderNum TransOil EngineOil Dte_Last_change VehicleMileage
A11* 784 good good 04/02/02 8483
B12* 834 good bad 04/15/02 3434
C34* 344 good good 04/02/02 8433
D45* 232 good bad 04/15/02 3334
Need all records that are only in Table A that are not in Table B or Table C.
The primary key is the CarID and OrderNum.
The recordset should look like this:
CarId OrderNum TransOil EngineOil Dte_Last_change VehicleMileage
A11** 784 good good 04/02/02 8483
B12** 834 good bad 04/15/02 3434
C34* 344 good good 04/02/02 8433
D45* 232 good bad 04/15/02 3334
Not this: Note:
CarId OrderNum TransOil EngineOil Dte_Last_change VehicleMileage
A11* 784 good good 04/02/02 8483 This record is from Table A
A11** 784 good good 04/02/02 8483
B12* 834 good bad 04/15/02 3434 This record is from Table A
B12** 834 good bad 04/15/02 3434
C34* 344 good good 04/02/02 8433
D45* 232 good bad 04/15/02 3334
Thanks
The Firebird
My tables are:
Table A Table B Table C
Fields: Fields: Fields:
CarID CarID CarID
OrderNum OrderNum OrderNum
TransOil TransOil TransOil
EngineOil EngineOil EngineOil
Dte_Last_Change Dte_Last_Change
VehicleMileage VehicleMileage
Manuf
Supplier
Three select statements connected with two Union All statements:
{First Statement: This statement gets all records that have a match with Table A and Table B. Records
that exist in both tables should be displayed. Two asterisks are placed beside the CarID in the control. This is OK.}
SELECT a.CarID + '**', a.OrderNum, a.TransOil, a.EngineOil, b.Dte_Last_change, b.VehicleMileage,
FROM (Table_A as a inner join Table_B as b on a.CarID = b.CarID and
a.OrderNum = b.OrderNum)
Union All
{Second Statement: I want to get all records in Table A and Table C records that have a match
less a record that is inTable B that have a match. The primary key is the CarID and OrderNum.
SELECT a.CarID, a.OrderNum, a.TransOil, a.EngineOil, b.Dte_Last_change, b.VehicleMileage
FROM (Table_A as A inner join Table_C as B on a.jon = b.jon and a.task = b.task)
Union All
{Third Statement: I want to get all records that are only in Table A that are not in Table B or Table C.
The primary key is the CarID and OrderNum. One asterisk is placed beside the CarID in the control. }
SELECT distinct a.CarID + '*', a.OrderNum, a.TransOil, a.EngineOil, b.Dte_Last_change, b.VehicleMileage
FROM Table_A as A
LEFT JOIN Table_C as B ON a.CarID = b.CarID and a.OrderNum = b.OrderNum
RIGHT JOIN Table_B as C on a.CarID <> c.CarID and a.OrderNum <> c.OrderNum
WHERE b.CarID is null order by 1
When I run the Statement separately:
Results of First SQL Statement:
CarId OrderNum TransOil EngineOil Dte_Last_change VehicleMileage
A11** 784 good good 04/02/02 8483
B12** 834 good bad 04/15/02 3434
Need Records that have matches in Table A and Table B. The primary key is the CarID and OrderNum.
Results of Second SQL Statement:
CarId OrderNum TransOil EngineOil Dte_Last_change VehicleMileage
A11 784 good good 04/02/02 8483
B12 834 good bad 04/15/02 3434
Need all records from Table A and Table C records that have a match less a record that is inTable B that have a match.
The primary key is the CarID and OrderNum.
Results of Third SQL Statement:
CarId OrderNum TransOil EngineOil Dte_Last_change VehicleMileage
A11* 784 good good 04/02/02 8483
B12* 834 good bad 04/15/02 3434
C34* 344 good good 04/02/02 8433
D45* 232 good bad 04/15/02 3334
Need all records that are only in Table A that are not in Table B or Table C.
The primary key is the CarID and OrderNum.
The recordset should look like this:
CarId OrderNum TransOil EngineOil Dte_Last_change VehicleMileage
A11** 784 good good 04/02/02 8483
B12** 834 good bad 04/15/02 3434
C34* 344 good good 04/02/02 8433
D45* 232 good bad 04/15/02 3334
Not this: Note:
CarId OrderNum TransOil EngineOil Dte_Last_change VehicleMileage
A11* 784 good good 04/02/02 8483 This record is from Table A
A11** 784 good good 04/02/02 8483
B12* 834 good bad 04/15/02 3434 This record is from Table A
B12** 834 good bad 04/15/02 3434
C34* 344 good good 04/02/02 8433
D45* 232 good bad 04/15/02 3334
Thanks
The Firebird
Éric Moreau
Use "UNION" instead of "UNION ALL" and don't concatenate stars to CarID and you should get the correct result.
ASKER
To: emoreau
Would you take a closer look at the second statement.
Need all records where there is a match in Table A and Table C but exclude the record from the recordset when that record is in Table B.
If a record is in Table A and Table C, then take the record out of the recordset where Table A = Table C.
Do I need some kind of join statement here?
The Firebird
Would you take a closer look at the second statement.
Need all records where there is a match in Table A and Table C but exclude the record from the recordset when that record is in Table B.
If a record is in Table A and Table C, then take the record out of the recordset where Table A = Table C.
Do I need some kind of join statement here?
The Firebird
do you mean that you don't want A11 and B12 in your final recordset? Or do you want them only once? If you want them once, follow the directives of my previous comment.
Okay I get what you're doing now. Rather than appending Asteriks to the carid field to identify the table, I would generate a table field from my select statments. Now, if you only want the record from table A join table B where there is a duplicate in table A join table C you'll need an outer join. Also I cleaned up the third query a little to do a left join where = Null like the second. This allows you to eliminate the distinct . . .
SELECT A.CarID, A.OrderNum, A.TransOil, A.EngineOil, B.Dte_Last_change, B.VehicleMileage, 'B' Table
FROM Table_A as A inner join Table_B as B on
A.CarID=B.CarID and A.OrderNum = B.OrderNum
Union All
SELECT A.CarID, A.OrderNum, A.TransOil, A.EngineOil, B.Dte_Last_change, B.VehicleMileage, 'C' Table
FROM Table_A as A inner join Table_C as C on A.jon = B.jon and A.task = B.task
Left Join Table_B B ON
A.CarID=B.CarID and A.OrderNum = B.OrderNum
WHERE B.CarID Is Null AND
B.OrderNum IsNull
Union ALL
SELECT A.CarID, A.OrderNum, A.TransOil, A.EngineOil, B.Dte_Last_change, B.VehicleMileage, 'A' Table
FROM Table_A as A LEFT JOIN Table_C as C ON
A.CarID = C.CarID and A.OrderNum = C.OrderNum
LEFT JOIN Table_B as B on
A.CarID = B.CarID and A.OrderNum = B.OrderNum
WHERE B.CarID Is Null and B.OrderNum Is Null AND
C.CarID Is Null and C.OrderNum Is Null
One last question: Why is the join to table_C in the second query different that all the rest. Should it be Table_A join Table_C ON A.CarID = C.CardID and A.OrderNum = B.OrderNum ? Just a thought . . . .
SELECT A.CarID, A.OrderNum, A.TransOil, A.EngineOil, B.Dte_Last_change, B.VehicleMileage, 'B' Table
FROM Table_A as A inner join Table_B as B on
A.CarID=B.CarID and A.OrderNum = B.OrderNum
Union All
SELECT A.CarID, A.OrderNum, A.TransOil, A.EngineOil, B.Dte_Last_change, B.VehicleMileage, 'C' Table
FROM Table_A as A inner join Table_C as C on A.jon = B.jon and A.task = B.task
Left Join Table_B B ON
A.CarID=B.CarID and A.OrderNum = B.OrderNum
WHERE B.CarID Is Null AND
B.OrderNum IsNull
Union ALL
SELECT A.CarID, A.OrderNum, A.TransOil, A.EngineOil, B.Dte_Last_change, B.VehicleMileage, 'A' Table
FROM Table_A as A LEFT JOIN Table_C as C ON
A.CarID = C.CarID and A.OrderNum = C.OrderNum
LEFT JOIN Table_B as B on
A.CarID = B.CarID and A.OrderNum = B.OrderNum
WHERE B.CarID Is Null and B.OrderNum Is Null AND
C.CarID Is Null and C.OrderNum Is Null
One last question: Why is the join to table_C in the second query different that all the rest. Should it be Table_A join Table_C ON A.CarID = C.CardID and A.OrderNum = B.OrderNum ? Just a thought . . . .
Please review this query because you join Table_C but you never use it.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
instead of checking for NULLs, you can do 2 INNER JOIN this way:
SELECT A.CarID, A.OrderNum, A.TransOil, A.EngineOil, C.Dte_Last_change, C.VehicleMileage, 'C' Table
FROM Table_A as A
inner join Table_C as C
on A.jon = C.jon and A.task = C.task
INNER Join Table_B B
ON A.CarID=B.CarID and A.OrderNum = B.OrderNum
I also notice that you didn't remove the "ALL" of the UNION clause.
And now, what is the current result and what differs from what you want?
SELECT A.CarID, A.OrderNum, A.TransOil, A.EngineOil, C.Dte_Last_change, C.VehicleMileage, 'C' Table
FROM Table_A as A
inner join Table_C as C
on A.jon = C.jon and A.task = C.task
INNER Join Table_B B
ON A.CarID=B.CarID and A.OrderNum = B.OrderNum
I also notice that you didn't remove the "ALL" of the UNION clause.
And now, what is the current result and what differs from what you want?
Won't the double inner join give you records in A that have a match are in B and C? In the last query we want records that are in A and not in C Nor In B.
I left the "Union All" because the outer joins ensure that each query is mutually exclusive. There will be no dupes from query to query to query - as there were before. What you had suggested, Emoreau, using union to eliminate the dupes, works except that there would be no way to distinguish which table the records originated from after removing the asteriks. I wanted to preserve this information in the final result via the Table field. Thus, in my solution using Union is no different than using Union All because the Table field makes each record unique. Make Sense? =)
I left the "Union All" because the outer joins ensure that each query is mutually exclusive. There will be no dupes from query to query to query - as there were before. What you had suggested, Emoreau, using union to eliminate the dupes, works except that there would be no way to distinguish which table the records originated from after removing the asteriks. I wanted to preserve this information in the final result via the Table field. Thus, in my solution using Union is no different than using Union All because the Table field makes each record unique. Make Sense? =)
ASKER
Thanks again. It seems to be working fine.