Search code examples
sqloracle-databasejoininsert-into

Populating a a table given the keys to two other tables in SQL

Given this original table: 'Public_Table'

U_ID city        state

1    NYC         NY
2    DET         MI
3    DEN         Co
4    NYC         NY
5    NULL        NULL

(In SQL )I had to create a new table 'Users' consisting of all unique users and another of all unique cities (and had to define city_ids to each city by using a trigger and sequence) and then populate both using the Public_table: 'Users' 

U_ID

1
2
3
4
5

and 'Cities'

City_ID    name    state
01         NYC     NY
02         DET     MI
03         DEN     Co

I then needed to define a new table 'current_user_city' which consists of every distinct user with their associated city_id and then populate it. Also, it is important to note that for a person with a NULL entry for city and state they should still be featured in the new table. For example this is how it would look in this occasion:

U_ID   City_ID

1        01
2        02
3        03
4        01
5        NULL

I have tried quite a few options thinking that this should be an easy task but I am very new to SQL and I have yet to find a possible way of making this work while also including the null values. I obviously need to someohow use the city,state of the user and the city,state of each city_id to match them and INSERT INTO the new table but I do not know the proper commands/syntax to do so. Also important to note that no additional tables can be made to solve this problem, also all of these tables must already be defined upon trying to populate them.

If you have any ideas please let me know! thanks!

Solution

  • The query you want is:

     select Users.U_Id, Cities.City_id 
    from Users Inner Join Public_Table On Users.U_Id = Public_Table.U_Id
    Left Outer Join Cities On Public_Table.city = Cities.name

    Inner Join works as intersection on sets, so you only get the results which are there in both table (depending on join condition).

    Left Outer Join gets everything an inner join would and also gets values from the 'left' table which are null is the 'right' side. It has another variant called right outer join.