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.
| 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 |
|
|
|
|
|
|
Joining two column into single column
