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
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...
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
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.
@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
select 'S' + convert(varchar(10),id), PSCNAME
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).
@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