Solved

Help with Sql Statement

Posted on 2002-07-10
9
225 Views
Last Modified: 2008-02-26
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
0
Comment
Question by:BirdsOfFire1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
9 Comments
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 7144966
Use "UNION" instead of "UNION ALL" and don't concatenate stars to CarID and you should get the correct result.
0
 

Author Comment

by:BirdsOfFire1
ID: 7144989
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
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 7145006
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.
0
Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

 
LVL 5

Expert Comment

by:spcmnspff
ID: 7146127
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 . . . .
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 7146191
Please review this query because you join Table_C but you never use it.
0
 
LVL 5

Accepted Solution

by:
spcmnspff earned 200 total points
ID: 7147029
Yeah the select lists were a liitle screwed up. After I edited the joins, I didn't edit the select lists.  In the last statement the join to both Table_B and Table_C are done only to ensure that there is no record in either table.  Including them in the Sleect list would just get nulls.  Anyway here's the correction:


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, 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
   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, A.Dte_Last_change, A.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
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 7147057
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?
0
 
LVL 5

Expert Comment

by:spcmnspff
ID: 7147082
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? =)

0
 

Author Comment

by:BirdsOfFire1
ID: 7151191
Thanks again. It seems to be working fine.
0

Featured Post

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question