normalization sql join

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

hi good day,

I have table named "organization" with two culumns "id" "OrgName"
Now, i am using that "organization" table in another table named "JobTable" with columns: id, jobId, pickOrgname, DropOrgName.
In the pickOrgname and DropOrgName i'm storing the "id" value of "Orgnization" table so later i'll use it to display in web page to get the pick/orgname by using the id

so now how to query by joins in sql

i tried, but failed

SQL

select jobId,  O.OrgName as PickOrg,O .OrgName as DropOrg from organization O join JobTable J on
J.pickOrgname =O.id and J.DropOrgName

pls help me to get query like

jobid PickOrg DropOrg 
 1      X Org  Y org 
 2      A Org  B Org

I can do it by sub query but is there to do with joins

Posted 3-Jun-15 5:48am

Member 10476757

Updated 3-Jun-15 8:59am

Maciej Los

v2

Add a Solution

Comments

CHill60 3-Jun-15 12:04pm

When you say "failed" what happened? (specifically report the error message you got)

2 solutions

Solution 1

If I understand do you want to use the same table "jobtable" with diferent IDs maybe you can add the jobtable again and join with different columns try this

SQL

select jobId,  J.OrgName as PickOrg, J1.OrgName as DropOrg from organization O join JobTable J on
J.IDJobTable =O.id  join
JobTable J1 on J1.IDJobTable = O.id

Greetings
Hanssel Navarro

Posted 3-Jun-15 6:15am

Hanss1492

Updated 3-Jun-15 6:48am

v2

Comments

CHill60 3-Jun-15 17:33pm

When I trial this I get 5 errors reported ... do you want to tidy this up?

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Accepted Answer · 2015-06-03T11:50:00

You need to drive your query from the JobTable, then do an INNER JOIN to the organisation table just to get the name (of the organisation)
But you need to do that join twice because you have 2 foreign keys in your jobtable table. E.g.

SQL

select jobId, OPICK.OrgName, Odrop.OrgName
from jobtable J
inner join organization Odrop on j.DropOrgName = odrop.id
inner join organization Opick on j.pickOrgname = opick.id

Note - the first join to organization is to get the name for the drop organisation, the second is to get the name for the pick organisation. The thing that you are really "interested in" is the Job ... so put that into your query first.

Yes - people will argue that it doesn't matter what order the tables appear in if you are using inner joins ... but when you come back to your code in days, weeks, months, years to come it makes it clearer what you were trying to do (bit of advice from an old lag)