SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 .NET Inside SQL Server (2005)
 Joining two column into single column
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

geniusvishal
Starting Member

India
3 Posts

Posted - 02/25/2012 :  01:25:49  Show Profile  Reply with Quote
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..

Edited by - geniusvishal on 02/25/2012 01:35:26

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 02/25/2012 :  03:25:56  Show Profile  Reply with Quote
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
Go to Top of Page

geniusvishal
Starting Member

India
3 Posts

Posted - 02/25/2012 :  04:58:56  Show Profile  Reply with Quote
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

Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 02/25/2012 :  08:51:58  Show Profile  Reply with Quote
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.
Go to Top of Page

geniusvishal
Starting Member

India
3 Posts

Posted - 02/26/2012 :  05:50:31  Show Profile  Reply with Quote
Please see this:

http://forums.asp.net/t/1773724.aspx/1?Unable+to+fetch+data+from+the+database
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000