select query

Question

select query

Minnu

SSCertifiable

Points: 6451
More actions
October 21, 2015 at 2:00 am

#304436

I have a select query on table unify, here i want to display the "Desc" value from JKS table instead of "u.code" field
SELECT
u.id,
(
+ISNULL(u.desc,'')+' '
+ISNULL(u.code,'')+' '
)
from Unify u
another table 'JKS'
code | Desc
-------------
001 | LCC
002 | HHT
003 | ICH
004 | Unify

Viewing 13 posts - 1 through 12 (of 12 total)

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

Phil Parkin SSC Guru Points: 246959 More actions · Answer 1

Minnu (10/21/2015)
I have a select query on table unify, here i want to display the "Desc" value from JKS table instead of "u.code" field
SELECT
u.id,
(
+ISNULL(u.desc,'')+' '
+ISNULL(u.code,'')+' '
)
from Unify u
another table 'JKS'
code | Desc
-------------
001 | LCC
002 | HHT
003 | ICH
004 | Unify

What problem are you having?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Neil Burton SSC-Insane Points: 22999 More actions · Answer 2

Minnu (10/21/2015)
I have a select query on table unify, here i want to display the "Desc" value from JKS table instead of "u.code" field
SELECT
u.id,
(
+ISNULL(u.desc,'')+' '
+ISNULL(u.code,'')+' '
)
from Unify u
another table 'JKS'
code | Desc
-------------
001 | LCC
002 | HHT
003 | ICH
004 | Unify

Your question doesn't make a lot of sense and there are errors in the query you've posted. I think you need to join the table 'Unify' to 'JKS' in order to get the Desc value from it. With what you've posted that really is a guess and I can't do much more without some help from you. If you read the article in my signature about posting questions that will point you in the right direction.

On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher

How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

Minnu SSCertifiable Points: 6451 More actions · Answer 3

Yes wants to join the two tables, in first table am trying to get the values as a single string, and i want to get the code from second table and join to the string in first table.

Minnu SSCertifiable Points: 6451 More actions · Answer 4

Table : Unify

id desc code

1 HSBOM 002

table 'JKS'

code | Desc

-------------

001 | LCC

002 | HHT

003 | ICH

004 | Unify

i want result like below

1 HSBOM HHT

Phil Parkin SSC Guru Points: 246959 More actions · Answer 5

Minnu (10/21/2015)
Table : Unify
id desc code
1 HSBOM 002
table 'JKS'
code | Desc
-------------
001 | LCC
002 | HHT
003 | ICH
004 | Unify
i want result like below
1 HSBOM HHT

Are you saying that you don't know how to join the tables?

Some consumable DDL, data would really help.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Minnu SSCertifiable Points: 6451 More actions · Answer 6

Dont know how to join while concatenating the two strings.

Please help

Neil Burton SSC-Insane Points: 22999 More actions · Answer 7

We're trying to help but we've got no idea what your tables look like. Please do as we've asked and provide some usable data.

This link, http://www.sqlservercentral.com/articles/Best+Practices/61537/ will show you how to generate sample data to post.

On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher

How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

Simon Hundleby SSCarpal Tunnel Points: 4559 More actions · Answer 8

Are you actually saying you have visited this site 844 times and

you don't know how to JOIN two tables and concatenate a couple of fields?

Minnu SSCertifiable Points: 6451 More actions · Answer 9

i Know Joins, but wants to know the syntax to replace below query with value in another table..

+ISNULL(u.code,'')+' '

Phil Parkin SSC Guru Points: 246959 More actions · Answer 10

Minnu (10/21/2015)
i Know Joins, but wants to know the syntax to replace below query with value in another table..
+ISNULL(u.code,'')+' '

+ISNULL(x.code,'')+' '

Where x is the alias of the joined table.

I'm 99% sure that this is not what you want. But it does demonstrate that you should provide sample data, DDL etc as already requested, because you do not appear able to write out your request in a way which anyone else can comprehend.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Neil Burton SSC-Insane Points: 22999 More actions · Answer 11

You're still not making a great deal of sense.

One thing is that the first '+' sign before the ISNULL will cause an error if there's nothing before it.

On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher

How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

Simon Hundleby SSCarpal Tunnel Points: 4559 More actions · Answer 12

CREATE TABLE #Unify

(

ID INT IDENTITY(1,1),

Code CHAR(3),

DESCR VARCHAR(10)

)

CREATE TABLE #JKS

(

ID INT IDENTITY(1,1),

Code CHAR(3),

DESCR VARCHAR(10)

)

INSERT INTO #Unify

SELECT '002','HSBOM'

INSERT INTO #JKS

SELECT '001','LCC'

UNION ALL

SELECT '002','HHT'

UNION ALL

SELECT '003','ICH'

UNION ALL

SELECT '004','Unify'

SELECT u.ID, ISNULL(u.DESCR,'') + ' ' + ISNULL(j.DESCR,'') as [Description]

FROM #Unify u

INNER JOIN #JKS j

ON u.Code =j.Code

DROP TABLE #Unify

DROP TABLE #JKS