Trouble With A SQL Select Incorporating 2 Child Tables For A Grid

[drosenkranz]

Hello,

I'm having rouble with a SQL Select statement incorporating 2 child tables for a Grid's source. I'm using VFP 6.0. Any suggestions would be greatly appreciated.

SELECT Phonemast.emp_no, Phonemast.dept, Phonemast.phone_no,;
Phonemast.last_name, Phonemast.first_name, Phonemast.first_titlecode;
Title.*, Dept.*;
FROM phonemast RIGHT OUTER JOIN title;
RIGHT OUTER JOIN dept ;
ON Phonemast.dept = Dept.dept ;
ON Phonemast.titlecode = Title.titlecode;
ORDER BY Phonemast.last_name, Phonemast.first_name

Thanks,

Dave

The 2nd mouse gets the cheese.

 

[aharrisreid]

drosenkranz,

>>I'm having rouble with a SQL Select statement...<<

Could you be a bit more precise and explain exactly what problems you are having? Is the statement failing completely, or are you just getting unexpected results?

>>SELECT Phonemast.emp_no, Phonemast.dept, Phonemast.phone_no,;
Phonemast.last_name, Phonemast.first_name, Phonemast.first_titlecode;
Title.*, Dept.*;
FROM phonemast RIGHT OUTER JOIN title;
RIGHT OUTER JOIN dept ;
ON Phonemast.dept = Dept.dept ;
ON Phonemast.titlecode = Title.titlecode;
ORDER BY Phonemast.last_name, Phonemast.first_name<<

The first thing that strikes me regarding your SQL statement is the JOIN syntax. Do you really want all Title and Dept. records included, even if there is no matching PhoneMast record? If you want all PhoneMast records, plus related Title and Dept details (if they exist), you whould try LEFT OUTER JOIN instead of RIGHT OUTER JOIN. The LEFT refers to all records in your initial table, while RIGHT refers to all records in your joined tables. If you want only those masts for which Title and Dept data exists, use INNER JOIN instead.

Hope this helps.

Regards,
Alan Harris-Reid

 

[drosenkranz]

Hi,

The statement now reads as:

SELECT Phonemast.emp_no, Phonemast.phone_no, Phonemast.last_name,;
Phonemast.first_name, Phonemast.titlecode, Title.*, Phonemast.dept, Dept.*;
FROM phonemast LEFT OUTER JOIN title;
LEFT OUTER JOIN dept ;
ON Phonemast.dept = Dept.dept ;
ON Phonemast.titlecode = Title.titlecode;
ORDER BY Phonemast.last_name, Phonemast.first_name

The Title table has a Title field (C25) and a Titlecode fielc (C5)
The Dept table has a Dept field (C4) and a Dept_name field (C25)

The query returns data. The Phonemast.Dept field is correct in the display but the corresponding Dept.Dept field data is always the same Dept ( 9001) and the corresponding Dept.Dept_name is the correct department name for Dept 9001.

Any suggestions would be appreciated?

Dave

The 2nd mouse gets the cheese.

 

[drosenkranz]

Hi,

The statement now reads as:

SELECT Phonemast.emp_no, Phonemast.phone_no, Phonemast.last_name,;
Phonemast.first_name, Phonemast.titlecode, Title.*, Phonemast.dept, Dept.*;
FROM phonemast LEFT OUTER JOIN title;
LEFT OUTER JOIN dept ;
ON Phonemast.dept = Dept.dept ;
ON Phonemast.titlecode = Title.titlecode;
ORDER BY Phonemast.last_name, Phonemast.first_name

The Title table has a Title field (C25) and a Titlecode fielc (C5)
The Dept table has a Dept field (C4) and a Dept_name field (C25)

The query returns data. The Phonemast.Dept field is correct in the display but the corresponding Dept.Dept field data is always the same Dept ( 9001) and the corresponding Dept.Dept_name is the correct department name for Dept 9001.

Any suggestions would be appreciated.

Dave

The 2nd mouse gets the cheese.

 

[drosenkranz]

I noticed one more thing... If I reverse the order of the clauses from

ON Phonemast.dept = Dept.dept ;
ON Phonemast.titlecode = Title.titlecode;

to
ON Phonemast.titlecode = Title.titlecode;
ON Phonemast.dept = Dept.dept ;


Then the Dept info is correct but the Title info is now all the same record.

Whichever &quot;ON&quot; clause is placed last in the SQL Statement is the one that displays the correct data from the child table. The &quot;ON&quot; clause that is placed first does not return correct info - just the same value for the matching field in the child table.

Thanks again for your time.

Dave

The 2nd mouse gets the cheese.

 

[aharrisreid]

Dave,

>>The Phonemast.Dept field is correct in the display but the corresponding Dept.Dept field data is always the same Dept ( 9001)<<

Have you checked that your join fields are exactly the same length in both tables? If not, this could give misleading results.

Regards,
Alan

 

[drosenkranz]

Hello,

I figured out that the clauses were actually in the wrong order. By changin it to read:

LEFT OUTER JOIN title;
ON Phonemast.titlecode = Title.titlecode;
LEFT OUTER JOIN dept ;
ON Phonemast.dept = Dept.dept ;

The data is now correct.

Thanks for the assistance on the Joins.

Dave


The 2nd mouse gets the cheese.

 

[MadTown]

drosenkranz,

Your Select statement looks very much like a statement that the view designer would create. The view designer can't handle this type of query. I think changing the statement to read like the following should fix your problem.

SELECT Phonemast.emp_no, Phonemast.phone_no, Phonemast.last_name,;
Phonemast.first_name, Phonemast.titlecode, Title.*, Phonemast.dept, Dept.*;
FROM phonemast LEFT OUTER JOIN title;
ON Phonemast.titlecode = Title.titlecode;
LEFT OUTER JOIN dept ;
ON Phonemast.dept = Dept.dept ;
ORDER BY Phonemast.last_name, Phonemast.first_name