database dilemma

Question

I have a database consisting of the following tables:

SourcesA:  source, country, language

SourcesB:  id, url, source

I first need to create a new table (Table1)and insert the join of the two tables where source matches.

Second table (Table2) needs to contain the records that do not match.

Third operation (Table3) is to take the first table and find all records that have matching urls with the second table.

Any ideas appreciated

Answer 1

I would take a look at using views. They are a cross between result sets and actual tables. Since all your data in these three "tables" are dependent on actual data in tables, it is a perfect place to use views.

http://www.codeproject.com/KB/database/View.aspx http://odetocode.com/Articles/299.aspx http://www.informit.com/articles/article.aspx?p=130855

Answer 2

You probably wouldn't want to create new tables, the data is already in the database. You just want some new methods to look at that data. Try these, they can also be made into views if you need them as a permanent solution. If there is a real reason to duplicate the data in the database you could use these to insert into the newly created tables as well.

--result set/table 1
SELECT
  B.id,
  B.source,
  B.url,
  A.country,
  A.language
FROM SourcesA A
  JOIN SourcesB B 
    ON A.source = B.source

--result set/table 2  
SELECT
  'SourcesA',
  B.id,
  B.source,
  B.url,
  A.country,
  A.language
FROM SourcesA A
  LEFT JOIN SourcesB B 
    ON A.source = B.source
WHERE B.source IS NULL
UNION ALL
SELECT
  'SourcesA',
  B.id,
  B.source,
  B.url,
  A.country,
  A.language
FROM SourcesB B
  LEFT JOIN SourcesA A 
    ON B.source = A.source
WHERE A.source IS NULL

--result set/table 3
SELECT
* --being lazy here
FROM
  (
    SELECT
      B.id,
      B.source,
      B.url,
      A.country,
      A.language
    FROM SourcesA A
      JOIN SourcesB B 
        ON A.source = B.source
  ) T1
  JOIN      
  (  
    SELECT
      'SourcesA',
      B.id,
      B.source,
      B.url,
      A.country,
      A.language
    FROM SourcesA A
      LEFT JOIN SourcesB B 
        ON A.source = B.source
    WHERE B.source IS NULL
    UNION ALL
    SELECT
      'SourcesA',
      B.id,
      B.source,
      B.url,
      A.country,
      A.language
    FROM SourcesB B
      LEFT JOIN SourcesA A 
        ON B.source = A.source
    WHERE A.source IS NULL
  ) T2
    ON T1.url = T2.url