query missing some data

[Guest]

Hello,

I have two tables that share one identical column named "code" (which also
has been given a relationship to one another). I did a unmatched query to
see if all codes in table A were listed in table B. The query came back with
zero unmatched items.

I ran another query to join that goes:
invoice date - table 1
sold to - table 1
code - table 2
size - table 2
quantity - table 1

Table 1 has 2086 records some records have the same code. Table 2 has 866
codes. When the query is run, only 936 records appear. I am not sure where
I went wrong?

Any suggestions?

Thanks

 

[Guest]

Pay attention to the following :

I guess that Table1 contains the details for Table2

So you have 2 things to check:

1) do all records of table1 a record in table2 : the result should return no
records and is the most important one
SELECT Table1.* FROM Table1 LEFT JOIN Table2 ON Table1.Code=Table2.Code
WHERE Table2.Code IS NULL
2) do all table2 records have details in table1
SELECT Table2.* FROM Table1 RIGHT JOIN Table2 ON Table1.Code=Table2.Code
WHERE Table1.Code IS NULL

By using these queries you can check the relations between the tables.

- Raoul

 

[[MVP] S.Clark]

Select a.*, b.* from Table A LEFT Join Table B on a.code = b.code
is the general syntax.

Without knowing your exact table structures and the SQL, it's hard to give
much more than that.