Best Query to Divide Data - Use Self-Join v. UNION?

[mickeyj2]

Hi,

I’m a little rusty on my join logic, and was wondering if someone could help me with the following. I think I need to use either a Self-Join or a Union for this but I’m not sure.

I have a mapping table that currently houses three main products. There are cables associated with those products. As you can see below, Product #3 also has a cable (#10) that isn’t available for products #2 and #3.

Ultimately in my application, I want to list out each cable, however, if there’s a cable that’s only applicable to a certain product and not shared by all products, I’d like that to stand out as well. I can just asterisk it and say 'Only available with Product X.'

What’s the best way to list out each cable individually, but also list out an cables who have an “orphan” product?

I looked at it for most of the morning this morning, and I’m fluctuating between using a Self-Join, Union or Right Join. My tablename is CableMapping and the fields are CableMapID, ProductModelID and CableID.


Table: CableMapping

Cable Product CableID
MapID ModelID
------- ------- ------

1 1 1
2 1 2
3 1 3



4 2 1
5 2 2
6 2 3




7 3 1
8 3 2
9 3 3




10 3 10


Thanks in advance for any help.

Mickeyj2

 

[vbkris]

>>Ultimately in my application, I want to list out each cable, however, if there’s a cable that’s only applicable to a certain product and not shared by all products, I’d like that to stand out as well. I can just asterisk it and say 'Only available with Product X.'


how do you want the output to be for those cables that have multiple products? count???

a self join is the best way here...

Known is handfull, Unknown is worldfull

 

[mickeyj2]

Thanks vbkris, for your post. I think I didn't define the situation clearly enough. Here's what I'm trying to do. (I also have more than this table.)

I have two tables that hold IDs, CABLES and PRODUCT.
The tables are defined as follows:

CABLES.CableID
--------------
1
2
3
10

PRODUCT.ProductID
-----------------
1
2
3

I also have that mapping table, called CABLEMAPPING that maps the ProductID to the CableID. (This tells me which Products use which cables.)


CABLEMAPPING

Cable Product CableID
MapID ModelID
------- ------- ------

1 1 1
2 1 2
3 1 3



4 2 1
5 2 2
6 2 3




7 3 1
8 3 2
9 3 3

10 3 10



Now, consider this scenario. According to the mapping table, only Cable 10 applies to Product 3. Therefore, I need a way to tell my users that "Cable 10 only applies to Product 3.".

Now here's where I'm stumped. I'm having trouble formulating the actual query. Can I handle all of this in SQL Server and obtain a query that tells me what I want to know, or should I keep my three tables as-is, adn handle this logic processing in my application?

Currently, I'm able to do this processing in my application by using a 2-Dimensional array that holds the CableID and its respective list of Product Models, but I'm wondering if there's a more effective method to be done in SQL Server like possibly through a UNION or a Self-Join, etc. Also, I only want to list my cables once, and I'm telling the user that unless otherwise stated, the cables listed apply to all Products.

Thanks in advance for any help you can provide.

mickeyj2

 

[gmmastros]

Based on the 3 tables you show above... What would your ideal output be? I'm beginning to understand, but showing the desired output would be helpful to us.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[vbkris]

try this query:

select CableID,ProductId from CABLEMAPPING inner join
(
select CableID,count(CableID) CableCount from CABLES inner join CABLEMAPPING on CABLEMAPPING.CableID=CABLES.CableID group by CableID having count(CableID)=1) AllCablesThatHaveOnlyOneProduct
on
CABLEMAPPING.CableID=AllCablesThatHaveOnlyOneProduct.CableID
inner join
PRODUCT
on
PRODUCT.ProductId =CABLEMAPPING.ProductId


not tested. will test it when i get in front of a comp that has SQL Server...

Known is handfull, Unknown is worldfull

 

[mickeyj2]

Hi vbkris,
Thanks for the post. I'll work through this query. As of yet, I didn't get it to run. I am getting ambiguous columns for CableID and ProductID. I'll review again. I think the assignment of aliases will work this problem out.

Here's the ideal data that I'm looking for:

CableID ProductIDList
1 1,2,3
2 1,2,3
3 1,2,3
10 3

See, this way I can run through my query in my application and compare this product list "1,2,3" against all of the products that are available. If this list contains anything less than the entire product list (as in the case of Cable 10), then I'll notify my users that this particular cable is only available for Product 3.

Thanks in advance.

mickeyj2

 

[vbkris]

>>this way I can run through my query in my application and compare this product list "1,2,3"

how do you plan to compare this comma seperated list with the products table?

since you want to alert only the 1 product cable, my suggestion would be use the join above and get that list, this can be left joined with the main cable table. this can give you the difference between cables that have more than one product.

however if you still want that kind of resultset then you will have to go in for a userdefined function that returns a varchar.

sample:
create function FNNAME(@CABLE_ID bigint)
retruns varchar(8000)
as
begin
declare @the_prods varchar(8000)
select @the_prods=@the_prods+cast(ProductId,varchar) from CABLEMAPPING where CableId=@CABLE_ID
return @the_prods
end


in your query:
select CableId,FNNAME(CableId) from CableTable

note:
once again the SQL queries have not been tested for syntax. but thats the general idea...

Known is handfull, Unknown is worldfull

 

[mickeyj2]

Thanks, vbkris,

I'll try this out and let you know how it turns out.
Thanks again.

 

[mickeyj2]

Hi vbkris,

I wasn't able to get that function to work or be created. SQL server complains about the CAST statement and says its expecting 'AS'.

I have really had a lot of trouble generating a script/procedure that tells me what I need to find out.

I'll state it again as a real-world scenario.

Suppose I have 4 cables with Cable IDs = 1,2,3,10.
Now suppose I have 3 television product with ProductID = 1,2,3.
Cables 1,2,3 go to all three Products (1,2,3), however, Cable 10 can only be used for Product 3.

I just need a way to list out to my browser users the following statements:

Cable 1
Cable 2
Cable 3
Cable 10 *only good for Product 3.
(I'm implying that the other cables are good for all products unless otherwise stated.)

Ideally, I'd like a procedure/function that accepts a list of product IDs or cableids, and for each cableID, whether or not its NOT VALID for all of the products its mapped to.

I have three main tables:
PRODUCT - which contains ProductID.
CABLE - which contains CableID.
CABLEMAPPING - which contains both CableID and ProductID (described above in color). (This table tells me which products use which cables. But the problem is there are many products listed for many cables and I need to condense this down.)

I have tried many different scenarios, procedures, self-joins, unions, but nothing seems to stick.

Please help. I'm really beginning to get lost.

Thanks in advance for any help you can provide.

mickeyj2

 

[mickeyj2]

Hi All,

Just wanted you to know that I am handling this with my application instead of SQL Server. I had hoped to do this processing at the data level, but I think its safer for me to go to the application. I have been able to perform what I was looking for.

Thanks to everyone who tried to help.
mickeyj2