SQL Query not Working

[eebabe]

My SQL query:

SELECT tbl_Providers.Org_Type, tbl_Providers.Providers, tbl_OBGYN_List.Providers
FROM tbl_Providers
LEFT OUTER JOIN tbl_OBGYN_List ON tbl_Providers.Providers = tbl_OBGYN_List.Providers
Where tbl_Providers.Org_Type = "OBGYN"
UNION SELECT tbl_Providers.Org_Type, tbl_Providers.Providers, tbl_OBGYN_List.Providers
FROM tbl_Providers
right OUTER JOIN tbl_OBGYN_List ON tbl_Providers.Providers = tbl_OBGYN_List.Providers;

tbl_providers.Providers is showing non-obgyn list. Does the WHERE statement needs to go somewhere else?

 

[alvechurchdata]

Please remember that we know nothing about your operating domain and have no idea what an obgyn is. We don't know what the query is supposed to be doing.

Having said that, have you tested the two halves of the union separately? Both should be working and generating identical structures.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[HarleyQuinn]

You'd need a WHERE on the second query in the union as well, unless you want to wrap the whole union in a query and then oyu'd only have to specify it in the very outer query.

Hope this helps

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before post

 

[eebabe]

So here's what I'm trying to do:

Show all obgyn type providers from tbl_providers and all providers from tbl_OBGYN. From the sql query I linked both table using providers field. But I only want to show OBGYN type providers from tbl_providers. With this query it shows other type providers as well from tbl_Providers.

So the statement "WHERE" where it's now is not working.

HarleyQuinn: I tried both your ways.With putting "WHERE" in both union, the result only shows the providers that matches in both tables. If I put where in the outer query ((select *... = tbl_OBGYN_List.Providers)where...) it gave me syntax error.

So, the problem is not sure if the "WHERE" statement is at the right spot.

 

[HarleyQuinn]

What is it you're actually wanting to achieve here? Could you show us some sample data and a sample output? It might help us to know what you want to have happen.

To be honest, from your description, it sounds like you wouldn't even need a UNION query at all (just the first one)...

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before post

 

[eebabe]

So here's the result with both unions and "WHERE" statement where it is now.

Org_Type tbl_Prov.Prov tbl_OBGYN_List.Prov
DR. A
DR. B
DR. E
HOSP HOSP_A HOSP_A
HOSP HOSP_B HOSP_B
PEDIA HOSP_C HOSP_C
OBGYN OB A OB A
OBGYN OB B OB B
OBGYN OB C
OBGYN OB D
PEDIA PEDIA Z PEDIA Z

The idea of union is to show all records from tbl_Prov where org_type is OBGYN and all records from tbl_OBGYN.But some how the "WHERE" statement is making the query show other than OBGYN type that matches the providers of tbl_OBGYN.

If I just use the first query without the union it will only list providers that matches from both tables.

I hope my explaination makes sense.

Thanks.

 

[Remou]

How about:

SELECT t.Org_Type, t.Providers, t.OBGYN_Providers
FROM (
     SELECT p.Org_Type, p.Providers, o.Providers AS OBGYN_Providers
     FROM tbl_Providers p
     LEFT OUTER JOIN tbl_OBGYN_List o
     ON p.Providers = o.Providers
     UNION 
     SELECT p.Org_Type, p.Providers, o.Providers AS OBGYN_Providers
     FROM tbl_Providers p
     RIGHT OUTER JOIN tbl_OBGYN_List o
     ON p.Providers = o.Providers) t
WHERE t.Org_Type = "OBGYN"

http://lessthandot.com