Join Issues - Data Inconsistency

Question

Post reply

Join Issues - Data Inconsistency

info 88249

SSC Enthusiast

Points: 119
More actions
June 1, 2016 at 11:00 am

#327331

Hello there...
Today I'm building a Sales Report using information from 3 tables:
Table: Soldview - transaction information
Table: cust - Customer Information
Table: Items - product details
when I run everything together the report excludes a few records; the items NON-inventory (items that are billed/sold but never included on inventory).
Here is a better example (25 records in Soldview Table):
SELECT TICKETNUM FROM Soldview
TICKETNUM
1
4
4
6
7
8
9
11
13
16
18
19
20
21
22
22
23
23
24
25
26
27
29
2
2
And here is the Report with the 3 tables (ONLY showing 19 records):
SELECT
S.TICKETNUM as 'Receipt',
S.DATEin as 'Sold On',
S.INVNUM as 'SKU1',
S.TicketNum as 'Ticket',
S.Cost as 'Cost',
S.Amount as 'Amount',
I.NEWITEM as 'Condition',
I.MODELNUM as 'Reference #',
I.SERIALNUM as 'Serial #',
H.DESCRIPT as 'Brand',
C.CUS_FNAME as 'Customer 1',
C.CUS_MNAME as 'Customer 2',
C.CUS_LNAME as 'Customer 3'
FROM Soldview as S
inner join Items as I
on S.INVNUM = I.INVNUM
inner join Level5 as H
on I.LEVEL5_FK = H.lv5_PK
inner join Sold as A
on S.TICKETNUM = A.TICKETNUM
inner join cust as C
on A.CUS_FK = C.Cus_PK
ReceiptSoldSKU1TicketCostAmountConditionReference #Serial #BrandCustomer 1Customer 2Customer 3
12016-05-16 14:05:00I-523 13000.003000.000GMT40SG 1131 BVLGARI CASH CUSTOMER
42016-05-19 13:35:00I-564 45720.006948.001W2CL0002 3850258124VX CARTIER Joyeria Universal Luis Kuri
62016-05-23 10:06:00I-429 64600.005900.000W69009Z3 3001168486MX CARTIER Puerto Rico P.R.
72016-05-23 15:34:00I-582 731100.0033650.001326935 25D189Y5 ROLEX RICARDO QUILES Joyeria Santa Juanita
82016-05-24 10:04:00I-634 83900.003940.001W69011Z4 3005373417TX CARTIER True Facet Online
92016-05-25 13:47:00I-515 90.0025500.000218238 329M14Q3 ROLEX CASH CUSTOMER
112016-05-26 14:10:00I-574 1118000.0021500.001116655 E6347R9 ROLEX RICARDO QUILES Joyeria Santa Juanita
182016-05-27 16:30:00I-506 189700.0010800.000116613 V804363 ROLEX CRM Jewelers CARLITOS
192016-05-31 08:45:00I-1034 196000.007000.000IW371482 2678586 IWC IWJG SHOW VEGAS 05.16
202016-05-31 08:52:00I-481 2012000.0014500.00016618 A118331 ROLEX IWJG SHOW VEGAS 05.16
222016-05-31 13:11:00I-59 223500.004895.000C23340 230560 BREITLING True Facet Oran Mesica
222016-05-31 13:11:00I-520 222725.003303.000A13370 2472932 BREITLING True Facet Oran Mesica
232016-05-31 13:32:00I-573 2318000.0019500.001116655 0S303142 ROLEX IWJG SHOW VEGAS 05.16
242016-05-31 14:00:00I-555 246450.007800.000W50014N2 MG275361 CARTIER IWJG SHOW VEGAS 05.16
262016-05-31 15:18:00I-594 2619360.0020000.001116655 6Q94X765 ROLEX IWJG SHOW VEGAS 05.16
272016-06-01 08:29:00I-1036 2718000.0019000.001116655 063M5375 ROLEX Past Time Inc
292016-06-01 10:32:00I-367 297200.007946.500116660 V754852 ROLEX True Facet Oran Mesica
22016-05-17 14:43:00I-528 22850.004500.000230.8.77 2472637 JAEGER-LECOULTRE eBay Customer EBAY SALE
22016-05-17 14:43:00I-528 22850.004500.000230.8.77 2472637 JAEGER-LECOULTRE eBay Customer EBAY SALE
I know as a FACT that the discrepancy is generated because the 6 items that were billed as NON-Inventory, so there is not information about those items in 'items' table.
How to I get the Full Report to Include those items/transactions as well?
Thank You

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Change the join to the Items table from an inner join to a left outer join, see if that helps.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

You will also need to change the inner join to Level5 to an outer join as well.

info 88249 SSC Enthusiast Points: 119 More actions · Answer 3

Lynn Pettis (6/1/2016)
You will also need to change the inner join to Level5 to an outer join as well.

I changed them all 😉

this is my final query:

SELECT

S.TICKETNUM as 'Receipt',

S.DATEin as 'Sold On',

S.INVNUM as 'SKU1',

S.TicketNum as 'Ticket',

S.Cost as 'Cost',

S.Amount as 'Amount',

I.NEWITEM as 'Condition',

I.MODELNUM as 'Reference #',

I.SERIALNUM as 'Serial #',

H.DESCRIPT as 'Brand',

S.DESCRIPT as 'Description',

C.CUS_FNAME as 'Customer 1',

C.CUS_MNAME as 'Customer 2',

C.CUS_LNAME as 'Customer 3'

FROM Soldview as S

left outer join Items as I

on S.INVNUM = I.INVNUM

left outer join Level5 as H

on I.LEVEL5_FK = H.lv5_PK

left outer join Sold as A

on S.TICKETNUM = A.TICKETNUM

left outer join cust as C

on A.CUS_FK = C.Cus_PK

thank you so much for your help!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

info 88249 (6/1/2016)
Lynn Pettis (6/1/2016)
You will also need to change the inner join to Level5 to an outer join as well.
I changed them all 😉
this is my final query:
SELECT
S.TICKETNUM as 'Receipt',
S.DATEin as 'Sold On',
S.INVNUM as 'SKU1',
S.TicketNum as 'Ticket',
S.Cost as 'Cost',
S.Amount as 'Amount',
I.NEWITEM as 'Condition',
I.MODELNUM as 'Reference #',
I.SERIALNUM as 'Serial #',
H.DESCRIPT as 'Brand',
S.DESCRIPT as 'Description',
C.CUS_FNAME as 'Customer 1',
C.CUS_MNAME as 'Customer 2',
C.CUS_LNAME as 'Customer 3'
FROM Soldview as S
left outer join Items as I
on S.INVNUM = I.INVNUM
left outer join Level5 as H
on I.LEVEL5_FK = H.lv5_PK
left outer join Sold as A
on S.TICKETNUM = A.TICKETNUM
left outer join cust as C
on A.CUS_FK = C.Cus_PK
thank you so much for your help!

Do you understand the difference between an inner join and an outer join?

info 88249 SSC Enthusiast Points: 119 More actions · Answer 5

Lynn Pettis (6/1/2016)
info 88249 (6/1/2016)
Lynn Pettis (6/1/2016)
You will also need to change the inner join to Level5 to an outer join as well.
I changed them all 😉
this is my final query:
SELECT
S.TICKETNUM as 'Receipt',
S.DATEin as 'Sold On',
S.INVNUM as 'SKU1',
S.TicketNum as 'Ticket',
S.Cost as 'Cost',
S.Amount as 'Amount',
I.NEWITEM as 'Condition',
I.MODELNUM as 'Reference #',
I.SERIALNUM as 'Serial #',
H.DESCRIPT as 'Brand',
S.DESCRIPT as 'Description',
C.CUS_FNAME as 'Customer 1',
C.CUS_MNAME as 'Customer 2',
C.CUS_LNAME as 'Customer 3'
FROM Soldview as S
left outer join Items as I
on S.INVNUM = I.INVNUM
left outer join Level5 as H
on I.LEVEL5_FK = H.lv5_PK
left outer join Sold as A
on S.TICKETNUM = A.TICKETNUM
left outer join cust as C
on A.CUS_FK = C.Cus_PK
thank you so much for your help!
Do you understand the difference between an inner join and an outer join?

Lynn,

Honestly my SQL skills are close to NULL, everything I know is based on information I find online...

that is what I know about inner join and outer join:

Inner join will only return rows in which there is a match based on the join predicate, and left or right outer join, the result set will retain all of the rows from either the left or right table.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

info 88249 (6/1/2016)
Lynn Pettis (6/1/2016)
info 88249 (6/1/2016)
Lynn Pettis (6/1/2016)
You will also need to change the inner join to Level5 to an outer join as well.
I changed them all 😉
this is my final query:
SELECT
S.TICKETNUM as 'Receipt',
S.DATEin as 'Sold On',
S.INVNUM as 'SKU1',
S.TicketNum as 'Ticket',
S.Cost as 'Cost',
S.Amount as 'Amount',
I.NEWITEM as 'Condition',
I.MODELNUM as 'Reference #',
I.SERIALNUM as 'Serial #',
H.DESCRIPT as 'Brand',
S.DESCRIPT as 'Description',
C.CUS_FNAME as 'Customer 1',
C.CUS_MNAME as 'Customer 2',
C.CUS_LNAME as 'Customer 3'
FROM Soldview as S
left outer join Items as I
on S.INVNUM = I.INVNUM
left outer join Level5 as H
on I.LEVEL5_FK = H.lv5_PK
left outer join Sold as A
on S.TICKETNUM = A.TICKETNUM
left outer join cust as C
on A.CUS_FK = C.Cus_PK
thank you so much for your help!
Do you understand the difference between an inner join and an outer join?
Lynn,
Honestly my SQL skills are close to NULL, everything I know is based on information I find online...
that is what I know about inner join and outer join:
Inner join will only return rows in which there is a match based on the join predicate, and left or right outer join, the result set will retain all of the rows from either the left or right table.

Good textbook answer. Now, look at your data. Where to you see the need for an outer join and where to you not see a need? Based on your OP (original post) where you only mention 3 tables not 5, I see the need for only one outer join. Looking at your query, only two outer joins.

info 88249 SSC Enthusiast Points: 119 More actions · Answer 7

Lynn Pettis (6/1/2016)
info 88249 (6/1/2016)
Lynn Pettis (6/1/2016)
info 88249 (6/1/2016)
Lynn Pettis (6/1/2016)
You will also need to change the inner join to Level5 to an outer join as well.
I changed them all 😉
this is my final query:
SELECT
S.TICKETNUM as 'Receipt',
S.DATEin as 'Sold On',
S.INVNUM as 'SKU1',
S.TicketNum as 'Ticket',
S.Cost as 'Cost',
S.Amount as 'Amount',
I.NEWITEM as 'Condition',
I.MODELNUM as 'Reference #',
I.SERIALNUM as 'Serial #',
H.DESCRIPT as 'Brand',
S.DESCRIPT as 'Description',
C.CUS_FNAME as 'Customer 1',
C.CUS_MNAME as 'Customer 2',
C.CUS_LNAME as 'Customer 3'
FROM Soldview as S
left outer join Items as I
on S.INVNUM = I.INVNUM
left outer join Level5 as H
on I.LEVEL5_FK = H.lv5_PK
left outer join Sold as A
on S.TICKETNUM = A.TICKETNUM
left outer join cust as C
on A.CUS_FK = C.Cus_PK
thank you so much for your help!
Do you understand the difference between an inner join and an outer join?
Lynn,
Honestly my SQL skills are close to NULL, everything I know is based on information I find online...
that is what I know about inner join and outer join:
Inner join will only return rows in which there is a match based on the join predicate, and left or right outer join, the result set will retain all of the rows from either the left or right table.
Good textbook answer. Now, look at your data. Where to you see the need for an outer join and where to you not see a need? Based on your OP (original post) where you only mention 3 tables not 5, I see the need for only one outer join. Looking at your query, only two outer joins.

Using the textbook answer and your explanation... it should be something like this:

because records from Soldview wont be able to match up with Items & Level5 records (Non-Inventory items)

FROM Soldview as S

left outer join Items as I

on S.INVNUM = I.INVNUM

left outer join Level5 as H

on I.LEVEL5_FK = H.lv5_PK

inner join Sold as A

on S.TICKETNUM = A.TICKETNUM

inner joi cust as C

on A.CUS_FK = C.Cus_PK

gfoxxy93 Right there with Babe Points: 776 More actions · Answer 8

gfoxxy93

Right there with Babe

Points: 776

June 1, 2016 at 11:57 am

#1882584

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

info 88249 (6/1/2016)
Lynn Pettis (6/1/2016)
info 88249 (6/1/2016)
Lynn Pettis (6/1/2016)
info 88249 (6/1/2016)
Lynn Pettis (6/1/2016)
You will also need to change the inner join to Level5 to an outer join as well.
I changed them all 😉
this is my final query:
SELECT
S.TICKETNUM as 'Receipt',
S.DATEin as 'Sold On',
S.INVNUM as 'SKU1',
S.TicketNum as 'Ticket',
S.Cost as 'Cost',
S.Amount as 'Amount',
I.NEWITEM as 'Condition',
I.MODELNUM as 'Reference #',
I.SERIALNUM as 'Serial #',
H.DESCRIPT as 'Brand',
S.DESCRIPT as 'Description',
C.CUS_FNAME as 'Customer 1',
C.CUS_MNAME as 'Customer 2',
C.CUS_LNAME as 'Customer 3'
FROM Soldview as S
left outer join Items as I
on S.INVNUM = I.INVNUM
left outer join Level5 as H
on I.LEVEL5_FK = H.lv5_PK
left outer join Sold as A
on S.TICKETNUM = A.TICKETNUM
left outer join cust as C
on A.CUS_FK = C.Cus_PK
thank you so much for your help!
Do you understand the difference between an inner join and an outer join?
Lynn,
Honestly my SQL skills are close to NULL, everything I know is based on information I find online...
that is what I know about inner join and outer join:
Inner join will only return rows in which there is a match based on the join predicate, and left or right outer join, the result set will retain all of the rows from either the left or right table.
Good textbook answer. Now, look at your data. Where to you see the need for an outer join and where to you not see a need? Based on your OP (original post) where you only mention 3 tables not 5, I see the need for only one outer join. Looking at your query, only two outer joins.
Using the textbook answer and your explanation... it should be something like this:
because records from Soldview wont be able to match up with Items & Level5 records (Non-Inventory items)
FROM Soldview as S
left outer join Items as I
on S.INVNUM = I.INVNUM
left outer join Level5 as H
on I.LEVEL5_FK = H.lv5_PK
inner join Sold as A
on S.TICKETNUM = A.TICKETNUM
inner joi cust as C
on A.CUS_FK = C.Cus_PK

Does that query return the expected results? You can compare it the query with all outer joins.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 10

gfoxxy93 (6/1/2016)

It might be me, but I think two or three of those diagrams may be wrong. I'll have to set up some tests to verify.

info 88249 SSC Enthusiast Points: 119 More actions · Answer 11

Thank you both!

The report is working excellent now!

this is my final code...

SELECT

S.Sstatus as 'Status',

S.TICKETNUM as 'Receipt',

S.DATEin as 'Sold On',

S.INVNUM as 'SKU1',

S.TicketNum as 'Ticket',

S.Cost as 'Cost',

S.Amount as 'Amount',

I.NEWITEM as 'Condition',

I.MODELNUM as 'Reference #',

I.SERIALNUM as 'Serial #',

H.DESCRIPT as 'Brand',

S.DESCRIPT as 'Description',

C.CUS_FNAME as 'Customer 1',

C.CUS_MNAME as 'Customer 2',

C.CUS_LNAME as 'Customer 3'

FROM Soldview as S

left outer join Items as I

on S.INVNUM = I.INVNUM

left outer join Level5 as H

on I.LEVEL5_FK = H.lv5_PK

inner join Sold as A

on S.TICKETNUM = A.TICKETNUM

inner join cust as C

on A.CUS_FK = C.Cus_PK

where s.Sstatus <> 'V'

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 12

Lynn Pettis (6/1/2016)
gfoxxy93 (6/1/2016)
It might be me, but I think two or three of those diagrams may be wrong. I'll have to set up some tests to verify.

Okay, I'm good. Just took a little more thinking each through. That's what I get for trying to think hard after eating lunch.