iknowyerbad iknowyerbad - 1 year ago 66
SQL Question

Need to add a phone number to a SQL query to add to a person's name and address

Okay, so this is my situation.

I am using Microsoft SQL Server Management Studio I currently have a query that pulls a person's license number, address, first name, last name and ID. All I need to add to complete this is to add the phone number. I just recently came into this environment and I am not entirely sure what tables contain what data. There are almost 5000 tables and I simply don't have time to go through them.
How can I see if there are any relationships between the two tables?
I tried to do

where f.phn_int_id = a.psn_int_id
, but that does not work because the person id from table 1 does not match the phone id from table 2.

I should also mention that the table with the phone numbers contains only
. The table with the person name contains

I am pulling a seperate address from a funtion, which is
person_address_line = dbo.fn_get_ent_pri_adr (b.psn_int_id, 'L'), person_address_csz = dbo.fn_get_ent_pri_adr (b.psn_int_id, 'C')

*Note that b = our person name table.

**Note this is my first ever question, so I am going to probably miss some important information.

Everywhere that I have searched hasn't been able to help me solve my issue.

Edit 2 This is the query, with names of tables changed to be column 1, column 2 etc.

`SELECT a.car_gvr_ext_id, b.lst_nm, b.fst_nm, c.car_gvr_lic_no, e.cod_dtl_ds, concat(f.phn_ara_cd, f.phn_exc_no, f.phn_lcl_no) AS phn_no, person_address_line = dbo.fn_get_ent_pri_adr (b.psn_int_id, 'L'), person_address_csz = dbo.fn_get_ent_pri_adr (b.psn_int_id, 'C')
FROM column1 a, column2 b, column3 c, column4 d, column5 e, column6 f
WHERE a.psn_int_id = b.psn_int_id
AND a.car_gvr_int_id = c.car_gvr_int_id
AND a.car_gvr_int_id = d.car_gvr_int_id
AND d.func_int_id = e.cod_dtl_int_id
AND f.phn_int_id = a.car_gvr_int_id`

If I leave the code like this, I will get the phone number with phn_int_id = 1 to tie itself to car_gvr_int_id = 1 so on and so forth. The problem is that when I do that, I will get a line where someone from Florida will have a local number and someone with a local number will have an out of state number.

Answer Source

There are many ways to list columns from tables:

1- Using Information Schema Views



2- Using System tables sys.columns And sys.Tables And sys.schemas


SELECT cols.name as column_name, tbls.name as Table_name,schm.name as schema_name  FROM sys.columns  AS cols INNER JOIN sys.tables  AS tbls
ON cols.object_id = tbls.object_id INNER JOIN sys.schemas as schm ON tbls.schema_id = schm.schema_id
WHERE tbls.type = 'U'

When listing columns it is easier to work with your situation

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download