Joins in Query Design

[pickslides]

I have 2 tables. Table A has 52832 records. I made a join in design mode such that

Table A ----------> Table B

When I look at the datasheet view there are only 52831 records, why would one drop off? I thought this type of join preserved all records in Table A?

MQ

 

[plog]

What record is it? Specifically, what value does it have for your joined field(s)?

If that doesn't help, post your SQL. Perhaps you have criteria that is excluding that record.

 

[pickslides]

Here is the SQL

SELECT Cases.reference_number, Fddvs.field_name, Fddvs.display_text
FROM Fddvs RIGHT JOIN Cases ON Fddvs.entity_id = Cases.id
GROUP BY Cases.reference_number, Fddvs.field_name, Fddvs.display_text
HAVING (((Fddvs.field_name)="case_sub_type"));

I can do the same task in excel using a VLOOKUP and each record is appended with a value from the other table.

 

[arnelgp]

can LEFT JOIN help you.

 

[Grumm]

http://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins

 

[plog]

When you apply criteria to a field in the FROM clause of a RIGHT JOIN, you've effectively undone your RIGHT JOIN. So, in the query you posted you have a regular INNER JOIN. Meaning only matching fields between your two tables will be returned.

Also, it might be possible that you have 2 records that have the same Cases.reference_number, Fddvs.field_name, Fddvs.display_text values. When you GROUP BY you roll data up and essentially elminate duplicated records, so that's a possibility as well.

So, you have 2 things working against you that may be the issue. To help, you really need to tell us what you are trying to accomplish. There's really no reason to have that GROUP BY clause in there if you want all results, nor should you have a RIGHT JOIN if you need to apply criteria to a field in a table that isn't required.

My advice is to post 2 sets of data for us:

A. Starting sample data from your tables. Include table and field names and enough sample data to cover all cases.

B. Expected results of your query. Based on the data you provide in A, show us what you expect your query to return.