Joining multiple column of different table into single column

[geniusvishal]

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..

[geniusvishal]

What about This code??

select PSCNAME as PC
from tblPropertyCategory INNER JOIN tblPropertySubCategory
ON tblPropertyCategory.PCId=tblPropertySubCategory.PCId
union
select PCNAME
from tblPropertyCategory INNER JOIN tblPropertySubCategory
ON tblPropertyCategory.PCId=tblPropertySubCategory.PCId

[BitBlt]

Getting data into the list is not a problem. A simple select and union from each table would take care of that. It's figuring out which key belongs with what that is the problem. There are a couple of different possibilities on that, including having a "shadow list" array in memory to keep track of which list entry belongs with which table reference. Not the prettiest but it would work.

I started to try and figure out what you were trying to accomplish by combining these two lists of category/subcategory. I honestly could not come up with a valid reason to combine them. You could wind up with a missing level of hierarchy if you try to just refer to the sub-category and hope to follow RI up to category...unless the table into which data is to be inserted has separate referential integrity to each of the two category tables, with appropriate nullability constraints.

Can what you propose be done? Sure, but not without a bunch of assumptions which may or may not be your actual circumstance.

We need a little more detail on why you want to combine these lists rather than have separate drop-downs, and more information about the data structures that you're trying to populate and how they relate to each other, before we can reasonably offer help appropriate to your situation.

[adam_k]

@BitBlt: Although I don't disagree with a thing you've said, it's easy enough to figure the keys and their owners by concatenating with the key a hardcoded value that would show it's origin. ie :

select id,PCNAME as PC
from tblPropertyCategory 
union
select 'S' + convert(varchar(10),id), PSCNAME
from tblPropertySubCategory

Now provided that the id is a numeric then all ids returned that start with S (or are not numeric) belong to the subcategory. With a simple case and a substring or with a substring and a charindex you've got the id.
If you don't like mixing numbers and chars then an alternative is to figure it out by length or id value. This has a lot of ways to implement, but the easier one would be to just multiply one of the ids by a million or -1 or something that would really make it distinct. Then if it's greater than a million (or less than 0 or whatever) then the id belongs to x table).

[BitBlt]

@adam_k: Although I don't disagree with a thing YOU'VE said, :) I personally wouldn't go that way for 2 reasons.
1. As soon as you start altering the data to trick your front-end, you are playing "russian roulette" with your database integrity. Granted, it probably won't manifest if your data volumes are small, but I don't condone the practice.
2. I was thinking in terms of a drop-down list Windows control, which has a handy associated long integer array referred to by "ItemData". As soon as you make the id value into a string, you can't use it anymore. At that point, you have to have some other way to keep the various values coordinated (which is why I mentioned the in-memory array...I've used that technique in many apps very effectively).

I'm not saying that your solution wouldn't work, I just wouldn't do it that way myself. I will leave it to the OP to decide which method to use. And I promise not to frown if he chooses your way over mine. :D