Fetch info from two tables 3

[dreampolice]

I have two Access 2003 tables:

TableMain

MID  PO   TE   INFO
1    2    3    ABC
2    2    1    EREER
3    1    3    OIUOI
4    3    3    PERE
5    2    2    DFE

TablePerson

PID   FIRSTNAME   LASTNAME
1     Joe         Smith
2     Cal         Jones
3     Bill        Carson

I would like to get this output if I query for a MID number such as 3:

Firstname  Lastname   Firstname2 Lastname2  Info
Joe        Smith      Bill       Carson     OIUOI

My attempt below only fetches one name and ignores the TE value.

select * from TablePerson INNER JOIN TableMain on
TablePerson.PID = TableMain.PO
where TableMain.MID = 3

Another example if I query for MID number 5 I should get this:

Firstname  Lastname   Firstname2 Lastname2  Info
Cal        Jones      Cal        Jones      DFE

Please advise

 

[Remou]

Do you mean:

SELECT TableMain.MID, TablePerson.PID, TablePerson.FIRSTNAME, TablePerson.LASTNAME, TablePerson_1.FIRSTNAME, TablePerson_1.LASTNAME, TableMain.INFO
FROM TablePerson INNER JOIN (TableMain INNER JOIN TablePerson AS TablePerson_1 ON TableMain.TE = TablePerson_1.PID) ON TablePerson.PID = TableMain.PO
WHERE TableMain.MID=3

 

[dreampolice]

Thanks it works great!

I was wondering if I have a third (and final) table called NotesTable how would I include that in the query??

For example if I query for MID number 3, I should get this:

Firstname Lastname Firstname2 Lastname2 Info   Notes      
Joe       Smith    Bill       Carson    OIUOI  other data

Here is NotesTable where Notes field is sometimes null and MID is a Foreign key:

NotesID  MID   Notes 
1        1    
2        3     other data 
3        2    
4        5     miscl notes here

 

[Remou]

You could use:

SELECT TableMain.MID, TablePerson.PID, TablePerson.FIRSTNAME, TablePerson.LASTNAME, TablePerson_1.FIRSTNAME, TablePerson_1.LASTNAME, TableMain.INFO, TableNotes.Notes
FROM (TablePerson INNER JOIN (TableMain INNER JOIN TablePerson AS TablePerson_1 ON TableMain.TE = TablePerson_1.PID) ON TablePerson.PID = TableMain.PO) INNER JOIN TableNotes ON TableMain.MID = TableNotes.MID
WHERE TableMain.MID=3

Which I built using the Query Design Window. It would be a good idea to read

http://www.devshed.com/c/a/MySQL/Understanding-SQL-Joins,

because you are likely to run into problems if one of the tables is missing a related record.

 

[dreampolice]

Thanks it works perfect. I added left join for TableNotes to handle a missing related record.

Is Query Design Window the same as Access 2003 Query wizard?

 

[Remou]

Is Query Design Window the same as Access 2003 Query wizard?"

More or less. Just choose design view, rather than selecting a wizard.