the_photon the_photon - 4 months ago 10x
SQL Question

"merging" two columns of two SQL databases into a third (with a catch)

In connection with another more complicated problem, I am doing some SQL database prototyping. I want to do the following:

  1. Take an existing SQL database that has a column called 'studentNames'

  2. Take another existing SQL database that has a column called 'ACTvocabularyWords'

  3. "Merge" them together in new database in such a way that I can keep up with which students have and have not mastered which ACT vocabulary words.

To clarify, here is an example of what the two original databases look like (roughly):

adam applewhite
betty boop
carl creepy . . .

and here is the database with the vocab words:

stygian . . .

The merged table (if you will) should look like:

studentName: actVocabWord: mastered:
adam applewhite androgynous T
adam applewhite ctenoid T
adam applewhite stygian F
betty boop androgynous T
betty boop ctenoid F
betty boop stygian F
carl creepy androgynous T
carl creepy ctenoid T
carl creepy stygian T

(my apologies for being so bad at creating tables on this site)

I'm sure there's a fairly easy way to do this kind of merging/joining, but I have no idea what to call it (so that I can search the documentation for it).

As far as I am aware, UNION, UNION ALL, OUTER JOIN, INNER JOIN, BATCH INSERT and the like were never really meant to do this kind of thing. It has also been suggested to me NOT to try to extract the data from the vocab database, load it into a list or array (in say, Java), process the merging manually, and then create a new database.

Does anyone know what I should call this strange type of "merging" so that I can read the SQL documentation?



You don't explain how to get the last column, mastered.

You get the rows with a cross join:

select studentName, actVocabWord
from StudentNames sn cross join actVocabWords vw;

This would often be combined with another table, say mastered to get the flag:

select sn.studentName, vw.actVocabWord,
       (m.studentName is null) as mastered
from StudentNames sn cross join
     actVocabWords vw left join
     mastered m
     on m.studentName = sn.studentName and m.actVocabWord = vw.actVocabWord