Get text from table and join with BIT column on table 2

Question

Get text from table and join with BIT column on table 2

DotNetCoder

Old Hand

Points: 338
More actions
December 15, 2023 at 4:01 pm

#4346674
Ok, not sure how to explain this 100%.
Here is the issue. I have 2 tables (can't post actual schema) and I need to determine if the value in table 2 is true if the value in table 1 exists.
small snippet of the table schema:
```
table 1

id int
field varchar

id        field
1          NE
2          SE
3          NW
4          NE

table 2
id int
NE  bit
SE  bit 
NW  bit


id    NE      SE     NW     
1     1        0      0
2     0        1      0
3     0        0      1 
4     1        0      0
```
Is there a way to join those tables some how, so if NE - 1 show that row, SE - show that row and so on. The tables have over 5k rows in them so only showing a very small snippet of the data. At the end, I need to look at the { field } column in table one, and then look at that column in table 2, if its [ true ] then show that row.
- This topic was modified 1 year ago by DotNetCoder.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Ed B Hall of Fame Points: 3669 More actions · Answer 1

I'm not sure which table you need to validate or whether an id can have multiple Field values. One solution might be to create a map table (this assumes table 2 can only have a single column = 1).

CREATE TABLE dbo.Table3
(NE  bit, SE  bit, NW  bit, Field VARCHAR(2))
INSERT dbo.Table3 VALUES  (1,0,0,'NE'),(0,1,0,'SE'),(0,0,1,'NW')
SELECT * FROM dbo.Table3

Unpivot table 2 could work. I rarely use pivot or unpivot so I don't know if it's a good idea. Again I don't know which table you need to validate so this could be backwards.

DROP TABLE IF EXISTS dbo.Table1
DROP TABLE IF EXISTS dbo.Table2

CREATE TABLE dbo.Table1
( id int,
  Field varchar(20)
 )

INSERT dbo.Table1 VALUES 
(1,  'NE'),(2, 'SE'),(3, 'NW'),(4, 'NE'),(5, 'NE'), (6, 'NE')

CREATE TABLE dbo.Table2
(
id int,
NE  bit,
SE  bit ,
NW  bit
)

INSERT  dbo.Table2 VALUES 
(1,1,0,0),
(2,0,1,0),
(3,0,0,1),
(4,1,0,0),
(5,1,1,1);

WITH cteDir AS
(
SELECT ID, Direction
FROM
(
  SELECT ID, NE,SE,NW
  FROM dbo.Table2
) p
UNPIVOT
(
  Val for Direction IN
  ([NE],[SE],[NW])
) AS upvt
WHERE upvt.Val = 1
)

SELECT a.id, a.Field, b.Direction, 
        IIF(a.Field=b.Direction,1,0) AS IsMatch,
        c.*
FROM dbo.table1 AS a
LEFT OUTER JOIN cteDir AS b ON a.id = b.id
LEFT OUTER JOIN dbo.table2 AS c ON a.id = c.id

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 2

If I understand correctly, could you do something like this:

SELECT [T1].* -- or could do [T2].*, not certain which table row you are trying to show, you may just want *
FROM [table_1] AS [T1]
INNER JOIN [table_2] AS [T2] ON 
    ([T1].[field] = 'NE' AND [T2].[NE] = 1) OR 
    ([T1].[field] = 'SE' AND [T2].[SE] = 1) OR 
    ([T1].[field] = 'NW' AND [T2].[NW] = 1)

NOTE I did not test the above code... I could be completely out to lunch on this, but I think that should work. Also no clue on how the performance will be on that query. This could be a very inefficient way to do it. I just don't like working with pivot and unpivot if I don't have to. I could also be completely misunderstanding the request. Do you happen to have expected output?

EDIT - edit was done to fix the code block as apparently having a space in between square brackets in the table name resulted in weird output on SSC.

This reply was modified 1 year ago by Mr. Brian Gale.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

DotNetCoder Old Hand Points: 338 More actions · Answer 3

I need to show all of the data from table 2

I'll check out both of these examples over the weekend, thanks

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 4

As a bit of a sidebar, this is why people need to make sure they have normalized data in their tables instead of "convenient" data. Table 2 needs to be normalized or this kind of issue is going to continue to be an issue forever.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

DotNetCoder Old Hand Points: 338 More actions · Answer 5

I agree with you 10000%, however, the DB was created before I joined the project, so I'm kind of stuck with some of the table structures at this time.

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 6

DotNetCoder wrote:

I agree with you 10000%, however, the DB was created before I joined the project, so I'm kind of stuck with some of the table structures at this time.

TruDat!

To be sure, did your weekend testing show the code solutions provided worked or is there something still missing?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

DotNetCoder Old Hand Points: 338 More actions · Answer 7

After a meeting yesterday, I can add a new column to the table and use it with a lookup table. I can't fully modify the table due to other apps using it, however, I can add a new column and use that for my app and get the table normalized. The other apps are using a proc, so a small tweak to that proc will have to be made, but nothing major.