bizarre querie

  • Thread starter Thread starter Mike Fellows
  • Start date Start date
M

Mike Fellows

i have a two tables i need to create two queries from

i have a personal table which i will be using the ID from and i have a
contact record table which has an ID

the relationship between the two tables is one to many (1 record to many
contact records)

im trying to create a query that shows everything in the Personal Table and
everything from the Contact Record table even if there isnt a record in the
contact record table

the contact record table also has a datetime field that i need to order by
17:30:00 and in the second querie <17:30:00 (which i cant achieve either)
Click to expand...

my table examples are as follows

Personal:

ID (autonumber)
Surname (text)
Firstname (text)
contact1 (datetime)

ContactRecord

ID (Number)
ContactTime (DateTime)

basically the querie should be something like

select Personal.ID, Personal.Surname, Personal.Contact1,
ContactRecord.ContactTime where ContactRecord.ContactTime < 17:30 and
Personal.Contact1 is null

but obvioulsy this wont do exactly what i want due to theone to many
relationships and that there may not be a record in the contactrecord table

anyhelp with this will be greatly appreciated

Thanks

Mike Fellows
 
hi,
sounds like you need to left join your tables too.

SELECT Personal.ID, Personal.Surname, Personal.Contact1,
ContactRecord.ContactTime FROM Personal LEFT JOIN
ContactRecord ON ContactRecord.contacttime =
Personel.contacttime WHERE ContactRecord.ContactTime =
17:30 AND Personal.Contact1 isnull

I chose contacttime because that was the only common field
you showed in your tables. I would assume that employee id
would be a better one or name. you may wish to change that
part.
 
oh, by the way, this is not a bazarre querry. just a
standard, routine, run of the mill left join query.
 
You need to use a LEFT JOIN in your query so that all parent records (on
left side of join) are returned even if there is no match in the child
records (on right side of join):

SELECT Personal.ID, Personal.Surname, Personal.Contact1,
ContactRecord.ContactTime
FROM Personal LEFT JOIN ContactRecord
ON Personal.ID = ContactRecordID;

The above example returns all records in Personal and matching records
(based on ID value) from ContactRecord.

I am not sure that I understand what you are wanting to accomplish with the
WHERE conditions that you list. But, let me take a guess:

SELECT Personal.ID, Personal.Surname, Personal.Contact1,
ContactRecord.ContactTime
FROM Personal LEFT JOIN ContactRecord
ON Personal.ID = ContactRecordID
WHERE ContactRecord.ContactTime < #17:30#;
 
Back
Top