Ms sql updating table column base on certain condition

Question

0.00/5 (No votes)

See more:

I've 3 tables

Table User = [id, gender, nationality, firstname, lastname ];
Table Gender = [id, name] one to many with table User
Table nationality [ id, name] - One to many with table User.

want to execute query like
"Select id, gender, form User where id = 5";

in gender column it should display gender name instead of gender's id;

gender = 1, name
nationality = 1 , name.

What I have tried:

i do have tried query like

SELECT upi.id , upi.fullname, upi.gender,
(SELECT name From Gender WHERE g.id = upi.gender ) As gndr
FROM User as upi, gender as g

it gives result like

id,fullname,gender_id, gndr
1, abc ,1 ,Male
1, abc ,1 ,Male
1, abc ,1 ,Male
1, abc ,1 ,Male
1, abc ,1 ,Male

thanking in an anticipation....

Posted 3-Mar-16 23:06pm

KanwalT

Updated 3-Mar-16 23:56pm

v2

Add a Solution

Comments

Tomas Takac 4-Mar-16 5:26am

Where do you update the table? And of course you should use joins for this. Is that your question?

1 solution

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)

Richard Deeming · Accepted Answer · 2016-03-03T23:56:00

Solution 1

Sounds like you need a JOIN:
SQL Joins | W3Schools[^]

SQL

SELECT
   U.id,
   U.firstname,
   U.lastname,
   G.name As gender,
   N.name As Nationality
FROM
    User As U
    INNER JOIN Gender As G
    ON G.id = U.gender
    INNER JOIN Nationality As N
    ON N.id = U.nationality
;

Posted 3-Mar-16 23:56pm

Richard Deeming

Comments

KanwalT 4-Mar-16 8:23am

it works but what if users have more than one nationality ?

Richard Deeming 4-Mar-16 8:27am

Then you'd need to change your table structure to have a many-to-many relationship between User and Nationality. Then, if you wanted to return a concatenated list of the user's nationalities, you would need to use one of these methods[^] to concatenate the values.

KanwalT 5-Mar-16 2:41am

like i dont want to concatenate..

i've values like

User Table

id,gender, nationality, firstname, lastname
1,2,1,abc,xyz
1,2,2,abc,xyz
2,1,1,john,smith
2,1,3,john,smith
2,1,2,john,smith

Gender Table
id, name
1, Male
2,Female

Nationality Table
1,Pakistani
2,Canadian
3,UK

I'm getting result this:

1,abc,xyz,Female,Pakistani
2,john,smith,Male,Pakisani
2,john,smith,Male,UK

thats it...
why it is not getting all rows from user table

Richard Deeming 5-Mar-16 13:33pm

With that data, and the query from my answer, I get:

1,abc,xyz,Female,Pakistani
1,abc,xyz,Female,Canadian
2,john,smith,Male,Pakistani
2,john,smith,Male,UK
2,john,smith,Male,Canadian

which is exactly what I would expect to see.

(NB: "UK" is not a nationality. You'd normally use "British" instead.)