I have a question regarding SQL with MS SQL Server
I have a table as such
customer
id country_id post_code_id
1 AU 1
2 GB 1
I have postcode tables seperated 开发者_运维百科as such
post_codes_AU
post_codes_GB
etc..
Is there a way I can use country_id in the query to join the right table.. e.g.,
select * from customer c
inner join post_codes_ '+c.country_id+' as pc on pc.id=c.post_code_id
You can not join the query like this.
To make a proper query you can do one of the following:
- Make a partitioned view over the post_codes_... tables and join this view
- Generate the queries for each country_id in the customer table and glue them together with
UNION ALL
statements.
You will need to put []
around the table name if it really has a space in the name as your sample query suggests. This looks like a typo so you might not need to do that. Once you do that it should work fine as dynamic SQL.
Be sure that you are aware of potential pifalls of using dynamic SQL before you go that route though. This is worth a read:
http://www.sommarskog.se/dynamic_sql.html
Create a view and join that;
-- #1 create a view . . . .
create view MyPostCodes
as
select
country_id='AU',
post_code_id
from
post_codes_AU
union
select
country_id='GB',
post_code_id
from
post_codes_GB
go
-- #2 now join like this
select * from customer c
inner join MyPostCodes as pc on pc.id=c.post_code_id and pc.country_id=c.country_id
Seems your query will work, changed a little.
Select * from customer c
Inner Join post_codes
On concat('post_codes_',c.country_id)=Post_codes.post_code_id
Or have a try with below piece of code,
Select * from customer c
Inner Join post_codes
On right(1-charindex('_',reverse(post_codes_id)),post_codes_id) =c.country_id
Before comparing remove the exact code to compare to.
精彩评论