Solved

Help with Sql Statement

Posted on 2002-07-10
9
212 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
  • 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

774 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