How do I assign correct ID value based on whether shipping address is same as mailing address?

Transact-SQL
1

Hello super experts, we have an application where users are asked to provide personal information like shipping address and mailing address.
There is a checkbox on the form that asks users if the shipping address they are providing is the same as mailing address. I did ask a small subset of this question last week.

If the user checks the box, it means shipping and mailing are the same.

On the DB side which is where my question comes from, there are two related tables.
There are several tables but these are the two significant ones.
One is called Addresses and the other is called Applications.

The Addresses table has several columns but but are relevant here, ID, Mailing and Shipping.

For a disclaimer, I did not build the app, neither did I design the database.
I have been with this company now for two years and the author of this app / db as I understand it, has been gone for over 5 years.

Both Shipping and Mailing fieldnames are of bit data type.
If Shipping and Mailing are both 1 (true), then both shipping and mailing are the same
If Shipping is 1 and mailing is 0 or mailing is 1 and shipping is 0, then shipping and mailing are different.
Then there is another field on Addresses table called ID, Surprisingly, this is not an identity seed.
To insert a value into this field, you have to select max(ID) + 1.

Then on the Applications table, the relevant fieldnames there are calling ShippingAddress and MailingAddress.
If shipping and mailing addresses are same, then both ShippingAddress and MailingAddress get the value of Shipping. If not, ShippingAdress gets the value of Shipping and MailingAdress gets the value of Mailing.

I have been able to successfully insert and query the correct records when shipping address and mailing address are same but struggling mightly to figure out how to assign values to both ShippingAddress and MailingAddress when the shipping and mailing addresses are different.

I am wondering if any of you super DB experts can help with how to handle this?

2

hi hope this helps

drop create sample data

drop table if exists #Addresses
drop table if exists #Applications
create table #Addresses(ID int , Mailing bit , Shipping bit )
create table #Applications ( AddressID int , ShippingAddress varchar(20) , MailingAddress varchar(20) )
insert into #Addresses select 1, 1, 0
insert into #Applications select 1, '234 New York Street','456 Dublin Ireland'
insert into #Addresses select 2, 0, 1
insert into #Applications select 2, '7th Plaza Canberra','809 Flat 65 Nigeria'
insert into #Addresses select 3, 1, 1
insert into #Applications select 3, '67 Avenue Russia','4th Avenue Spain'

-- WHAT does this mean ? I have been able to successfully insert ? what are you trying to do ?

image
image860×371 13.2 KB

3

Lousy thread title. Please use a meaningful title specific to what the subject is.

4

How to post a T-SQL question on a public forum | spaghettidba

1 Like
5

Hi harishgg1, thanks for your response but arrggh, I screwed up a bit in my descriptions of what I needed help on.
Very sorry about that.
In my original post, I said, "If shipping and mailing addresses are same, then both ShippingAddress and MailingAddress get the value of Shipping. If not, ShippingAdress gets the value of Shipping and MailingAdress gets the value of Mailing."

I didn't quite describe it correctly. So, let me give some examples.
If Shipping address and mailing address are same, Shipping fieldname gets value of 1 and mailing fieldname gets value of 1 and ID (that's the part missing) gets its value assigned to both ShippingAddress and MailingAddress fields in Applications table.
To illustrate:

Addresses table
Shipping bit
Mailing bit
ID int

Applications
ShippingAddress int
MailingAddress int

Sample Data
Shipping address and mailing address are same:

Mailing             Shipping           ID                                ShippingAddress         MailingAddress
1                     1                 209                                     209                      209

Shipping address is different from mailing address

 Mailing      Shipping           ID                                   ShippingAddress         MailingAddress
 1              0            220 (shipping) and 221 (mailing)           220                         221
6

This looks as though you have managed to create some sort of quantum database where the value of the integer column ID is both 220 and 221 until it is actually selected.

If you want help please read the link I posted above. I suspect more columns and/or tables are required to solve your poorly defined problem.

1 Like
7

I don 't need your help, thank you