Query Puzzle?

[andy_dyer]

I have two queries a before and after...

Query1

SELECT tblAddresses.txtID, tblAddresses.SURNAME, tblAddresses.NAME, tblAddresses.[HOUSE NUMBER], tblAddresses.[ADDRESS 1], tblAddresses.[ADDRESS 2], tblAddresses.TOWN, tblAddresses.COUNTY, tblAddresses.[POST CODE], tblAddresses.[HOME PHONE], tblAddresses.[MOBILE PHONE], tblAddresses.[WORK PHONE], tblAddresses.[PERSONAL EMAIL], tblAddresses.[WORK EMAIL], tblAddresses.BIRTHDAYDATEID, tblAddresses.BIRTHDAYMONTHID, tblAddresses.[COUPLE?], tblAddresses.PARTNER, tblAddresses.ANNIVERSARYDATEID, tblAddresses.ANNIVERSARYMONTHID, tblAddresses.TITLE
FROM tblAddresses;

Query2

SELECT tblAddresses.txtID, tblAddresses.TITLE, tblAddresses.SURNAME, tblAddresses.NAME, tblAddresses.[HOUSE NUMBER], tblAddresses.[ADDRESS 1], tblAddresses.[ADDRESS 2], tblAddresses.TOWN, tblAddresses.COUNTY, tblAddresses.[POST CODE], tblAddresses.[HOME PHONE], tblAddresses.[MOBILE PHONE], tblAddresses.[WORK PHONE], tblAddresses.[PERSONAL EMAIL], tblAddresses.[WORK EMAIL], tblBirthdayDate.Date, tblBirthdayMonth2.Month
FROM tblBirthdayDate INNER JOIN (tblAnniversaryDate INNER JOIN (tblBirthdayMonth2 INNER JOIN (tblAnniversaryMonth2 INNER JOIN tblAddresses ON tblAnniversaryMonth2.AnniversaryMonthID = tblAddresses.ANNIVERSARYMONTHID) ON tblBirthdayMonth2.BirthdayMonthID = tblAddresses.BIRTHDAYMONTHID) ON tblAnniversaryDate.AnniversaryDateID = tblAddresses.ANNIVERSARYDATEID) ON tblBirthdayDate.BirthdayDateID = tblAddresses.BIRTHDAYDATEID;

I am trying to get all my data out on one query for a mail merge, but instead of pulling the ID's for Birthday Date & Month and Anniversary Date & Month I want the actual values...

Query 1 pulls all my data but has the ID's

Query 2 pulls only those with birthdays in July??

Can anyone see something that I am missing that could be causing this??

As soon as

 

[namliam]

Your innerjoining all tables... Thus all tables must have values, check the table values for this... That MUST be (part) of the problem....

Regards

 

[andy_dyer]

Yes these are also the only records with something in each of the fields...

Is there any way I can adjust my joins so that it will show all the records??

 

[FoFa]

Visualize your tables as listed from left to right

INNER JOIN means this join has to return data
LEFT JOIN means table on left will return data, table on right may not
RIGHT JOIN opposite of LEFT JOIN

Usually INNER and LEFT joins are the most used, rarley have I used a Right Join.

SO FROM Table1 T1 Left Join Table2 T2 on T1.col = T2.col
will return all rows from Table1, and any that are available for Table2. If no match to Table2, any Table2 columns referenced will be NULL, but Table1 columns will contain data.

 

[andy_dyer]

Thanks FoFa!!

It was a right join that I wanted that gave me all my data from tblAddresses and also those from my other tables as and when it was there!!

Thanks ever so much for your help!!

 

[namliam]

Fofa beside the point but...

Have you ever seen something like:

FROM Table1 T1 Left Join Table2 T2 on T1.col <> T2.col
FROM Table1 T1 Left Join Table2 T2 on T1.col > T2.col
FROM Table1 T1 Left Join Table2 T2 on T1.col like "*" & T2.col & "*"

etc

Regards

 

[FoFa]

I have used FROM Table1 T1 Left Join Table2 T2 on T1.col > T2.col before (in Sql Server), but LIKE's I usually put in the where clause, but if it is the only condition on a join, you should put it in the join. Sql is pretty easy either way, I find that taking the join criteria out of the where clause makes the SQL easier to read. Sometime the line between join criteria and where criteria is a tad fuzzy however. Typically I find the not equal join is in the where clause, where you have a self joined table and don't want the current row you have (like history as an example). Say you have a job table.

With EmpNo, JobTitle, JobDate

01 Clerk 01/23/2002
01 Assistant 02/02/2003

I want the last job they held, not the current, so
SELECT J2.JobTitle, J2.JobDate from Job J1
inner join Job J2 on J1.EmpNo = J2.Empno
Where J1.JobDate = (SELECT MAX(JobDate) from Jobs J3 where J3.EmpNo = J1.EmpNo)
AND J2.JobDate = (SELECT MAX(JobDate) from Jobs J4 where j4.JobDate <> J1.Jobdate and J4.Empno = J1.Empno)
AND J1.EmpNo = 01

As a rather poor example

 

[namliam]

I was just wondering, i do it quite a lot but never see anybody do it....

They all seem unable to imagine it... Ending up asking me to do it....

*Geez* like i have nothing else to do all day (but be on the forum )

regards