One other thought regarding your comment that the join condition on the names returned zero records, you may have to trim the names before concatenating them, (trim(c.first_name) + trim(c.last_name) = ...). Either that or there are no name matches in the tables.
~Dina ----- Original Message ----- From: "Dina Hess" <[EMAIL PROTECTED]> To: "SQL" <[EMAIL PROTECTED]> Sent: Tuesday, August 05, 2003 12:03 PM Subject: Re: oops! > OK, looks like I was wrong about the OR in the join condition. This works: > > SELECT > c.Invalid_User, > v.Last_Name, > v.First_Name > FROM > CH_Employee_ATT_feb c JOIN Valid_Employees v ON > ((c.ISP_Employee_Badge_Number = v.[Badge Number]) > OR (c.ISP_User_First_Name + c.ISP_User_Last_Name = v.First_Name + > v.Last_Name)) > > ~Dina > > > ----- Original Message ----- > From: "Conaway, Amy C" <[EMAIL PROTECTED]> > To: "SQL" <[EMAIL PROTECTED]> > Sent: Tuesday, August 05, 2003 11:15 AM > Subject: RE: oops! > > > > Thanks guys,...but I tried both ways, and neither worked > > > > In Dina's, the combination statement alone produces a zero query, and > > then because it is ANDed with the badge segment...the total query is > > still zero. > > The only change I made was to make the JOIN an INNER JOIN because im > > using MS Access. Your logic made sense, and because there aren't any > > syntax errors im still at a loss. > > > > When I tried Eric's I was confident too that it would work because I had > > done something very similar in another query where I simply UNIONed > > multiple statements. But again, only the badge related section of the > > union worked. The name combination alone produced a zero query. > > > > If you have any more ideas...i'm all ears. Thanks again! > > > > > > -----Original Message----- > > From: Dina Hess [mailto:[EMAIL PROTECTED] > > Sent: Tuesday, August 05, 2003 11:28 AM > > To: SQL > > Subject: Re: oops! > > > > Amy, > > > > In your WHERE clause you're saying "I want to join on the badge number > > OR > > the name." SQL's confused. You can have it match badge numbers AND > > names, > > but it doesn't know what you want when you say you want to match badge > > numbers OR names. My advice? Move the join to the FROM clause for > > clarity: > > > > SELECT > > c.Invalid_User, > > v.Last_Name, > > v.First_Name > > FROM > > CH_Employee_ATT_feb c JOIN Valid_Employees v ON > > (c.ISP_Employee_Badge_Number = v.[Badge Number]) > > AND (c.ISP_User_First_Name + c.ISP_User_Last_Name = v.First_Name + > > v.Last_Name) > > > > The reason I changed your test for a name match is because SQL doesn't > > know > > you want it to look for the concatenated first and last name of each > > record > > unless you tell it to do that. > > > > Also, since your tables are joined on name, you will only need to SELECT > > the > > first and last names from one of the tables. > > > > Hope that's what you were looking for. BTW, this is untested. > > > > ~Dina > > > > > > > > ----- Original Message ----- > > From: "Conaway, Amy C" <[EMAIL PROTECTED]> > > To: "SQL" <[EMAIL PROTECTED]> > > Sent: Tuesday, August 05, 2003 9:50 AM > > Subject: oops! > > > > > > > ..it would help if I pasted the code.... > > > > > > SELECT CH_Employee_ATT_feb.ISP_User_Last_Name, > > CH_Employee_ATT_feb.ISP_User_First_Name, > > CH_Employee_ATT_feb.ATT_Usage_feb.Invalid_User, > > Valid_Employees.Last_Name, > > Valid_Employees.First_Name > > > FROM CH_Employee_ATT_feb, Valid_Employees > > > WHERE CH_Employee_ATT_feb.ISP_Employee_Badge_Number = > > Valid_Employees.[Badge Number] OR > > (CH_Employee_ATT_feb.ISP_User_Last_Name = > > Valid_Employees.Last_Name AND CH_Employee_ATT_feb.ISP_User_First_Name = > > Valid_Employees.First_Name) ; > > > > > > > > > > > > > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=6 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=6 Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
