Trying to write a query to pull data matching 2 tables and2 variables 1

[teamssc]

I'm fairly new using SQL Queries. The query below works fine until I add the last line "and mail6.folder like 'X-GM%'". I'm guessing that I'm not using "and" correctly and thus it is not seeing the additional variable. Any help would be appreciated. Thanks!


select *
from gm6.dbo.mailbox as mail6
left join goldmine8.dbo.mailbox as mail8
on (coalesce(mail6.recid,'') <> coalesce(mail8.recid, ' '))
and mail6.folder like 'X-GM%'

 

[bborissov]

You JOIN table with LEFT join. That means you will have ALL records from Mail6 and only matching records from Mail8.
If you join tables like you do that means you want want ONLY these records from Mail8 which have different RecId from Mail6 BUT Only for these records from Mail6 which starts with X-GM. If you want to filter your records to get only X-GM started records you must use WHERE clause:

select * 
       from gm6.dbo.mailbox as mail6
left join goldmine8.dbo.mailbox as mail8
on (coalesce(mail6.recid,'') <> coalesce(mail8.recid, ' '))
WHERE mail6.folder like 'X-GM%'

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP