Solved

Help with Sql Statement

Posted on 2002-07-10
9
200 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 69

Expert Comment

by:Éric Moreau
Comment Utility
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
Comment Utility
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 69

Expert Comment

by:Éric Moreau
Comment Utility
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
 
LVL 5

Expert Comment

by:spcmnspff
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 69

Expert Comment

by:Éric Moreau
Comment Utility
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
Comment Utility
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 69

Expert Comment

by:Éric Moreau
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks again. It seems to be working fine.
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

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 …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

744 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now