Get list of contents from Table after referring a column value of table A

Databases
1

I have a couple of tables in MS SQL Server database

Table A:

PName PComment isTierI isTierII isTierIII lastValueUsed
BOX row 0 0 1 2
FREEZER row 0 1 0 1
BOTTLE row 0 0 1 3
BUILDING row 1 0 0 1

Another table that I have is as follows:

Table B:

Name Comment
BOX#1 row
BOX#2 row
BOTTLE#1 row
BOTTLE#2 row
BOTTLE#3 row
BUILDING#1 row
FREEZER#1 row

I have categorized the three types of PName column using tier columns. For example, BOX can go inside FREEZER so BOX is in tier III and FREEZER is in Tier II and since Freezer can go inside a building, building is tierI. The last value used column shows how many box, freezer, bottle or building are in table B.

In Table B - it’s pretty cear I think that since last Value used column value of BOX is 2, we have two boxes, like BOX#1 and BOX#2. Similarly for BOTTLE, FREEZER and BUILDING

Both tabes have id column as well which is primary key and I haven’t shown it above just to keep it concise. Both tables are not linked to each other.

If I want to get only the list of BOTTLE ( like BOTTLE#1, BOTTLE#2 and BOTTLE#3) from Table B based on the lastValueUsed column vaue of 3 from Table A, is it possibe to do it via a SQL?

2

i’ve seen you struggle with this setup in a previous thread, too

why are you even using numerical data suffixes at all?

and this whole tier 1/2/3 business, why? why not use a hierarchical structure, like is done for employee-supervisor relationships, where they’re all in the same table like your tableA, but each employee has a FK to their supervisor

here’s the thing – suppose you wanted to introduce another tier, say FLOOR, so that a FREEZER would be located on a FLOOR and the FLOOR would be in a BUILDING – there goes your entire tier 1/2/3 structure

the main thing that’s ringing the alarm bells is your numerical suffix

not the least problem of which is what happens if you delete BOTTLE#2 – you would then have to rename BOTTLE#3, right?

i sincerely suggest you rethink your whole approach

1 Like
3

I have a requirement to store it like that. Thanks!

4

I can’t imagine that this is true, but if so, we can’t help you as this is like trying to accept that the earth is flat and now you want to travel around it…

5

a name that depends on a count that isn’t true if some other name is deleted

i don’t think i can help any further, sorry

6

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.