Joining two column into single column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

.NET Inside SQL Server (2005)

Joining two column into single column

Author

Topic

geniusvishal
Starting Member

3 Posts

Posted - 2012-02-25 : 01:25:49

Hello Guys,

I am trying to get the data from two columns of two different tables using Join as combining them as a whole to insert into dropdownlist...

The situation is like this:

There are two tables named tblPropertyCategory and tblPropertySubCategory.. I have PCName in tblPropertyCategory and

PSCName in tblPropertySubCategory.. I want to get the data of both PCName and PSCName and display into my dropdownlist...

What I have tried so far is like this:

select PCName,PSCNAME
from tblPropertyCategory INNER JOIN tblPropertySubCategory
ON tblPropertyCategory.PCId=tblPropertySubCategory.PCId

and I am getting result like this:
http://s19.postimage.org/xz7beuwk3/img1.jpg

and when i am using :

select (PCName+'-'+PSCNAME) as PC
from tblPropertyCategory INNER JOIN tblPropertySubCategory
ON tblPropertyCategory.PCId=tblPropertySubCategory.PCId

I am getting output like this:
http://s19.postimage.org/utmpondxv/img2.jpg

I want my output like this:

All Residential
All Commercial
Plot
Builder Apartment
Multi Storey Apartment
Bungalow / Villa / Pent House
Paying Guest / Hostel
Land (Commercial)
Office Space
Shop / Showroom
Guest House
Business Center
Industrial Building
Industrial Land

ie, Every Data from both the table should be combined which can be used to enter into the dropdownlist.

Nyhow Is it possible to use Union without revoking the Join property of these two table ie, I am getting the above result by using:

select PCName from tblPropertyCategory

union

select PSCName from tblPropertySubCategory

My Question is will it be valid ie, Is the Primary Key and Foreign Key functionality will be maintained..

coz I want to enter data using these values so have to write stored procedure accordingly but

in the above case I am not using PCId which is primary key for tblPropertyCategory and foreign Key for

tblPropertySubCategory which is not used in the above query...

Regards and Thanx in Advance..

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-02-25 : 03:25:56

You can certainly do the select query using the UNION as you described. The SELECT itself would not violate any key constraints.

But, as you indicated, your concern is what you would do after the user has selected one of the items from your list.

If you describe what you need to insert/update/delete based on the user's selection, someone on the forum would be able to give you suggestions. For example, if the user selected "All Residential" what do you want to happen? And, what do you want to happen if they selected "Builder Apartment"?

Post the DDL for the tables that you would be modifying along with the expected results for the two examples I listed above, and I am sure people on the forum would offer great solutions. Take a look at Brett's blog if you are not sure how to post the DDL: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

geniusvishal
Starting Member

3 Posts

Posted - 2012-02-25 : 04:58:56

Thank You Sunita for your suggestion...
The situation is like this I have two Property Category named ALL resedential and all commercial ... There are many sub category associated with them... When I select all resedential category it will display the details of all Property Sub Type falling under all resedential... and Builder apartment is actually a sub type of all resedential

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-02-25 : 08:51:58

quote:
Originally posted by geniusvishal

Thank You Sunita for your suggestion...
The situation is like this I have two Property Category named ALL resedential and all commercial ... There are many sub category associated with them... When I select all resedential category it will display the details of all Property Sub Type falling under all resedential... and Builder apartment is actually a sub type of all resedential

I could see this much from your screen shots in your first post. What is not clear to me is whether it is the data retrieval that you are having concerns about, or whether once you get that data, how to apply any user changes back into the database.

As for the data retrieval part, the UNION query that you posted seems fine. Or, are you trying to get the data with a parent-child relationship? Show exactly how you want to get the data.

If you need to apply some of the data updates based on user input, what kind of changes are they? Write the query to do that - at least in pseudo-code, and that would give people who are reading this a much better idea of what you are trying to do.

geniusvishal
Starting Member

3 Posts

Posted - 2012-02-26 : 05:50:31

Please see this:

http://forums.asp.net/t/1773724.aspx/1?Unable+to+fetch+data+from+the+database

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources