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
