query question, possible subquery?

Question

query question, possible subquery?

SQL_NuB

Hall of Fame

Points: 3666
More actions
March 2, 2015 at 1:43 pm

#294949

I have 2 tables that are laid out as:
JobCode JobDescription JobFamilyCode
1a developer 2m
3b accounting 2a
1b marketing 1m
and table 2 looks like
JobParent Description
1a_2m developer, builds and maintains, .......
3b_2a provide CFO all financial .........
1b_1M create company brand for online and.....
I'm able to combine JobCode and JobFamilyCode so it looks like [1a_2m], is there a way to get that result and then
do a select on table 2 within the same query. I'd like to keep the call if possible in one query.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

ScottPletcher SSC Guru Points: 101087 More actions · Answer 1

You really should leave the columns separate in the other table as well, but here's how to do the join with the combined column:

SELECT ...

FROM table1 t1

INNER JOIN table2 t2 ON

t2.JobParent = t1.JobCode + '_' + t1.JobFamilyCode

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

SQL_NuB Hall of Fame Points: 3666 More actions · Answer 2

ScottPletcher (3/2/2015)
You really should leave the columns separate in the other table as well, but here's how to do the join with the combined column:
SELECT ...
FROM table1 t1
INNER JOIN table2 t2 ON
t2.JobParent = t1.JobCode + '_' + t1.JobFamilyCode

thanks, when I tried that I'm getting an error on the { incorrect syntax near '_' }.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

SQL_NuB (3/2/2015)
ScottPletcher (3/2/2015)
You really should leave the columns separate in the other table as well, but here's how to do the join with the combined column:
SELECT ...
FROM table1 t1
INNER JOIN table2 t2 ON
t2.JobParent = t1.JobCode + '_' + t1.JobFamilyCode
thanks, when I tried that I'm getting an error on the { incorrect syntax near '_' }.

What RDBMS are you using?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

SQL_NuB Hall of Fame Points: 3666 More actions · Answer 4

Luis Cazares (3/2/2015)
SQL_NuB (3/2/2015)
ScottPletcher (3/2/2015)
You really should leave the columns separate in the other table as well, but here's how to do the join with the combined column:
SELECT ...
FROM table1 t1
INNER JOIN table2 t2 ON
t2.JobParent = t1.JobCode + '_' + t1.JobFamilyCode
thanks, when I tried that I'm getting an error on the { incorrect syntax near '_' }.
What RDBMS are you using?

SQL 08

ScottPletcher SSC Guru Points: 101087 More actions · Answer 5

I assume you replaced the '...' with your own columns :-).

What specific error are you getting?

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

SQL_NuB Hall of Fame Points: 3666 More actions · Answer 6

I get:

Query:

SELECT Jobs.JobFamily, JobDescriptions.[description]

FROM Jobs t1

INNER JOIN JobDescriptions t2 ON

t2.ParentRole = t1.JobFamily + '_' + t1.[description]

Error

Msg 4104, Level 16, State 1, Line 1

The multi-part identifier "Jobs.JobFamily" could not be bound.

Msg 4104, Level 16, State 1, Line 1

The multi-part identifier "JobDescriptions.description" could not be bound.

teas44 SSC Enthusiast Points: 129 More actions · Answer 7

You've added aliases Jobs->t1 and JobDescriptions->t2 so in SELECT SQL see tables Jobs, JobDescriptions as t1, t2.

Try:

SELECT t1.JobFamily, t2.[description]

FROM Jobs t1

INNER JOIN JobDescriptions t2 ON

t2.ParentRole = t1.JobFamily + '_' + t1.[description]