List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:February 15 2007 9:47pm
Subject:Re: SQL Translation
View as plain text  
In the last episode (Feb 15), Trimeloni, Adam said:
> I am currently working on a project to support using a MySQL database
> along with SQL Server 2000.  The following query has me stumped:
> 
> Select * 
> from  group_mstr gm,group_payers gp
> where  gm.practice_id = '1'
> and gp.location_id = '2'
> and gp.practice_id =* gm.practice_id 
> and gp.group_id =* gm.group_id 
> order by gp.payer_id

I bet =* is shorthand for an outer join (not sure if it's left or
right).  You should be able to do the same in mysql with 

SELECT * FROM group_mstr gm LEFT JOIN group_payers gp 
  ON ( gp.practice_id = gm.practice_id AND gp.group_id = gm.group_id )
  WHERE gm.practice_id = '1' AND gp.location_id = '2'
  ORDER BY gp.payer_id

Since the column names are the same in both tables, you can even
shorten it a bit and use

SELECT * FROM group_mstr gm LEFT JOIN group_payers gp
  USING ( practice_id, group_id )
  WHERE gm.practice_id = '1' AND gp.location_id = '2'
  ORDER BY gp.payer_id

> Does anyone know how to properly convert this to use ANSI joins
> instead? I converted others, but am having trouble this with one.
> 
> In our test case, the group_payers table does not have a location id
> equal to 2.  Yet, it still returns a row but populates all the
> group_payers columns are NULL.
> 
> After our translation attempts returns no rows.  (I am running the test
> cases in SQL Server 2000 first, to show our changes will still work with
> the current setup)

-- 
	Dan Nelson
	dnelson@stripped
Thread
SQL TranslationAdam Trimeloni15 Feb
  • Re: SQL TranslationDan Nelson15 Feb
    • RE: SQL TranslationGary W. Smith15 Feb
      • RE: SQL TranslationAdam Trimeloni15 Feb
  • RE: SQL TranslationAdam Trimeloni18 Feb