CODEWITHSUNDEEP
mysqldatabase
briandonor
briandonor

Reputation: 61

Fields in Mysql to point to other table fields

I have multiple tables that have addresses listed in them. I need to be able to quickly access the address from all the tables. I was going to have code that puts the addresses into a separate table of only just the addresses (Addresses_table). The problem is if any of the addresses get changed I have to change it in the Addresses_table. Is there a way to add a record to the Address_table that would point to the information in any of the other tables?

The multiple tables have different structures and the Addresses_table would have to have additional information attached.The best way i could describe it would be how in excel you could use G:25 to select the data in that cell.

EDIT:

So the table structure would look something like this when done.

ID, UserID, Street, City, State, Zip, Status
1     25    124    city1  state1 zip1 1
2     25    235    city2  state2 zip2 1
3     34    546    city3  state3 zip3 1
4     34    235    city2  state2 zip2 1

but ideally i would like to do something like this:

ID   UserID    Street                      City                     State    Zip    Status
1      25      tableA->recordID->street   tableA->RecordID->City  etc...              1
2      25      tableB->recordID->street   tableB->RecordID->City  etc...              1
3      34      tableC->recordID->street   tableC->RecordID->City  etc...              1
4      34      tableB->recordID->street   tableB->RecordID->City  etc...              1

record 2 and 4 would point to the same reacord in table b

Upvotes: 1

Views: 626

Answers (1)

StuartLC
StuartLC

Reputation: 107267

Creating a view which combines the addresses from the various source tables will help.

CREATE OR REPLACE VIEW AllAddresses AS
  SELECT Address1, Address2, City, Zip
  FROM Table1

  UNION 

  -- Change the names and types of different table columns
  SELECT Address1, foo + bar, City, Zip
  FROM Table2

  UNION

  -- Join to fetch associated data
  SELECT SomeOtherField, null, t4.CityName
  FROM Table3 t3 
    INNER JOIN Table4 t4 
    ON t3.CityID = t4.CityID;

Filters applied to the view will be applied to the source tables.

Upvotes: 1

Related Questions