How do you pull from two tables?

[dibbkd]

I've got two tables, one called "MWPATIENTS" and one called "MWTRN". There is a common field called "Chart Number" that is in both tables.

The MWPATIENTS table has all the patient information such as full name, address, etc.

I need to run a query on the MWCASE table that selects certain records and then get the patients full name from the MWPATIENT table.

select "Chart Number", "Procedure Code" from mwtrn WHERE "Procedure Code" = 'G0121' or "Procedure Code" = 'G0105'

It shows the chart number OK, but now I need to get the patients name from the MWPATIENT table.

Any suggestions please?

http://dibbsolutions.com

 

[dibbkd]

Hmm, can't figure out how to edit my post above.. I had a typo in one of the table names, it should have read:


I've got two tables, one called "MWPATIENTS" and one called "MWTRN". There is a common field called "Chart Number" that is in both tables.

The MWPATIENTS table has all the patient information such as full name, address, etc.

I need to run a query on the MWTRN table that selects certain records and then get the patients full name from the MWPATIENT table.

select "Chart Number", "Procedure Code" from mwtrn WHERE "Procedure Code" = 'G0121' or "Procedure Code" = 'G0105'

It shows the chart number OK, but now I need to get the patients name from the MWPATIENT table.

Any suggestions please?

http://dibbsolutions.com

 

[feherke]

Hi

Are you sure you are using MySQL ? In MySQL the identifiers are quoted with backticks ( `` ) not with double quotes ( "" ).

[b]select[/b]
[green][i]`Chart Number`[/i][/green][teal],[/teal] t[teal].[/teal][green][i]`Procedure Code`[/i][/green][teal],[/teal]
p[teal].[/teal][green][i]`patients name`[/i][/green]

[b]from[/b] mwtrn t
[b]left[/b] [b]join[/b] mwpatients p [b]using[/b] [teal]([/teal][green][i]`Chart Number`[/i][/green][teal])[/teal]

[b]where[/b] t[teal].[/teal][green][i]`Procedure Code`[/i][/green] [b]in[/b] [teal]([/teal][green][i]'G0121'[/i][/green][teal],[/teal] [green][i]'G0105'[/i][/green][teal])[/teal]

Feherke.

http://free.rootshell.be/~feherke/

 

[r937]

i don't think you're going to find a row in the mwtrn table with a chart number for a patient that doesn't exist

therefore it should be INNER JOIN, not LEFT OUTER JOIN



r937.com | rudy.ca
Buy my new book Simply SQL from Amazon