Combine Two Tables into a New One So That Select Rows from the Other One Are Ignored

Combine two tables into a new one so that select rows from the other one are ignored

UNION simply doesn't do what you describe. This query should:

CREATE TABLE AS 
SELECT date, location_code, product_code, quantity
FROM   transactions_kitchen k

UNION  ALL
SELECT h.date, h.location_code, h.product_code, h.quantity
FROM   transactions_admin h
LEFT   JOIN transactions_kitchen k USING (location_code, date)
WHERE  k.location_code IS NULL;

LEFT JOIN / IS NULL to exclude rows from the second table for the same location and date. See:

• Select rows which are not present in other table

Use CREATE TABLE AS instead of SELECT INTO. The manual:

CREATE TABLE AS is functionally similar to SELECT INTO. CREATE TABLE AS is the recommended syntax, since this form of SELECT INTO
is not available in ECPG or PL/pgSQL, because they interpret the
INTO clause differently. Furthermore, CREATE TABLE AS offers a
superset of the functionality provided by SELECT INTO.

Or, if the target table already exists:

INSERT INTO transactions_combined (<list names of target column here!>)
SELECT ...

Aside: I would not use date as column name. It's a reserved word in every SQL standard and a function and data type name in Postgres.

Join two tables and return non-matching rows Mysql

Thanks, @Arihant with your query l managed to find the solution, had to add a few things to make it work, check below.

SELECT 
a.name,
a.subject,
a.grade,
b.email
FROM topic a
LEFT JOIN payment b
ON a.subject = b.subject
AND a.grade = b.grade
AND a.name = b.topic
AND b.email = 'user.gom@gmail.com'
WHERE a.subject = 'Accounting'
AND a.grade = 'Grade 10'
HAVING b.email IS NULL

Compare 2 MySQL tables and move new rows to second table

Don't think Minus is available in my sql but an outer join should work

http://sqlfiddle.com/#!2/e73e4/1/0

Insert into tb2 (
Select tb1.ID, tb1.Col1, tb1.Col2, tb1.Col3 from tb1 
LEFT JOIN tb2 on Tb1.col1=tb2.col1 and Tb1.col2=tb2.col2
Where tb2.col1 is null);

But I'm likely missing something you were after...

MySQL: I have two tables, 1 production and 1 archive. I need to select distinct records from both as some records are identical

I suspect that you want union rather than a join:

select order_no, date_of_sale, cost from prod
union 
select order_no, date_of_sale, cost from archive

union removes duplicates between the resultsets (and within the resultsets as well).

Best way to combine two tables, remove duplicates, but keep all other non-duplicate values in SQL

If I understand your question correctly you want to join two large tables with thousands of columns that (hopefully) are the same between the two tables using the email column as the join condition and replacing duplicate records between the two tables with the records from Table 2.

I had to do something similar a few days ago so maybe you can modify my query for your purposes:

WITH only_in_table_1 AS(
SELECT *
FROM table_1 A
WHERE NOT EXISTS
(SELECT * FROM table_2 B WHERE B.email_field = A.email_field))
SELECT * FROM table_2
UNION ALL
SELECT * FROM only_in_table_1

If the columns/fields aren't the same between tables you can use a full outer join on only_in_table_1 and table_2

Combine two tables that have no common fields

There are a number of ways to do this, depending on what you really want. With no common columns, you need to decide whether you want to introduce a common column or get the product.

Let's say you have the two tables:

parts:              custs:
+----+----------+   +-----+------+
| id | desc     |   |  id | name |
+----+----------+   +-----+------+
|  1 | Sprocket |   | 100 | Bob  |
|  2 | Flange   |   | 101 | Paul |
+----+----------+   +-----+------+

Forget the actual columns since you'd most likely have a customer/order/part relationship in this case; I've just used those columns to illustrate the ways to do it.

A cartesian product will match every row in the first table with every row in the second:

> select * from parts, custs;
      id desc     id  name
      -- ----     --- ----
      1  Sprocket 101 Bob
      1  Sprocket 102 Paul
      2  Flange   101 Bob
      2  Flange   102 Paul

That's probably not what you want since 1000 parts and 100 customers would result in 100,000 rows with lots of duplicated information.

Alternatively, you can use a union to just output the data, though not side-by-side (you'll need to make sure column types are compatible between the two selects, either by making the table columns compatible or coercing them in the select):

> select id as pid, desc, null as cid, null as name from parts
  union
  select null as pid, null as desc, id as cid, name from custs;
    pid desc     cid name
    --- ----     --- ----
                 101 Bob 
                 102 Paul
    1   Sprocket
    2   Flange

In some databases, you can use a rowid/rownum column or pseudo-column to match records side-by-side, such as:

id desc     id  name
-- ----     --- ----
1  Sprocket 101 Bob
2  Flange   101 Bob

The code would be something like:

select a.id, a.desc, b.id, b.name
from parts a, custs b
where a.rownum = b.rownum;

It's still like a cartesian product but the where clause limits how the rows are combined to form the results (so not a cartesian product at all, really).

I haven't tested that SQL for this since it's one of the limitations of my DBMS of choice, and rightly so, I don't believe it's ever needed in a properly thought-out schema. Since SQL doesn't guarantee the order in which it produces data, the matching can change every time you do the query unless you have a specific relationship or order by clause.

I think the ideal thing to do would be to add a column to both tables specifying what the relationship is. If there's no real relationship, then you probably have no business in trying to put them side-by-side with SQL.

If you just want them displayed side-by-side in a report or on a web page (two examples), the right tool to do that is whatever generates your report or web page, coupled with two independent SQL queries to get the two unrelated tables. For example, a two-column grid in BIRT (or Crystal or Jasper) each with a separate data table, or a HTML two column table (or CSS) each with a separate data table.

Find different values between two tables in SQL Server

Here is a generic way to handle two tables differences.

We just need to know their primary key column.

It is based on JSON, and will work starting from SQL Server 2016 onwards.

SQL

-- DDL and sample data population, start
DECLARE @TableA table (rowid int Identity(1,1), FirstName varchar(100), LastName varchar(100), Phone varchar(100));
DECLARE @TableB table (rowid int Identity(1,1), FirstName varchar(100), LastName varchar(100), Phone varchar(100));

insert into @TableA(FirstName, LastName, Phone) VALUES
('JORGE','LUIS','41514493'),
('JUAN','ROBERRTO','41324133'),
('ALBERTO','JOSE','41514461'),
('JULIO','ESTUARDO','56201550'),
('ALFREDO','JOSE','32356654'),
('LUIS','FERNANDO','98596210');

insert into @TableB(FirstName, LastName, Phone) VALUES
('JORGE','LUIS','41514493'),
('JUAN','ROBERTO','41324132'),
('ALBERTO','JOSE','41514461'),
('JULIO','ESTUARDO','56201551'),
('ALFRIDO','JOSE','32356653'),
('LUIS','FERNANDOO','98596210');
-- DDL and sample data population, end

SELECT rowid
      ,[key] AS [column]
      ,Org_Value = MAX( CASE WHEN Src=1 THEN Value END)
      ,New_Value = MAX( CASE WHEN Src=2 THEN Value END)
FROM (
        SELECT Src=1
              ,rowid 
              ,B.*
         FROM @TableA A
         CROSS APPLY ( SELECT [Key]
                             ,Value
                       FROM OpenJson( (SELECT A.* For JSON Path,Without_Array_Wrapper,INCLUDE_NULL_VALUES)) 
                     ) AS B
        UNION ALL
        SELECT Src=2
              ,rowid 
              ,B.*
         FROM @TableB A
         CROSS APPLY ( SELECT [Key]
                             ,Value
                       FROM OpenJson( (SELECT A.* For JSON Path,Without_Array_Wrapper,INCLUDE_NULL_VALUES)) 
                     ) AS B
      ) AS A
GROUP BY rowid,[key]
HAVING MAX(CASE WHEN Src=1 THEN Value END)
     <> MAX(CASE WHEN Src=2 THEN Value END)
ORDER BY rowid,[key];

Output

+-------+-----------+-----------+-----------+
| rowid |  column   | Org_Value | New_Value |
+-------+-----------+-----------+-----------+
|     2 | LastName  | ROBERRTO  | ROBERTO   |
|     2 | Phone     | 41324133  | 41324132  |
|     4 | Phone     | 56201550  | 56201551  |
|     5 | FirstName | ALFREDO   | ALFRIDO   |
|     5 | Phone     | 32356654  | 32356653  |
|     6 | LastName  | FERNANDO  | FERNANDOO |
+-------+-----------+-----------+-----------+

---

Related Topics