logo
down
shadow

SQL insert into statement leads to weird (cartesian product) result


SQL insert into statement leads to weird (cartesian product) result

By : Gaurav Singal
Date : October 14 2020, 02:21 PM
This might help you INSERT inserts new rows into the table. Columns not specified in the INSERT are given NULL values (or specified defaults).
UPDATE updates columns in existing rows. That appears to be the logic that you want and you have the correct statement for it.
code :


Share : facebook icon twitter icon
Postgresql: Insert the cartesian product of two or more sets

Postgresql: Insert the cartesian product of two or more sets


By : IenC
Date : March 29 2020, 07:55 AM
wish helps you I am not sure if this really answers your question, but in PostgreSQL there is CROSS JOIN defined as:
code :
SELECT N AS number, L AS letter FROM
    (VALUES (1), (2), (3)) a(N)
CROSS JOIN
    (VALUES ('A'), ('B'), ('C')) b(L);
 number | letter
--------+--------
      1 | A
      1 | B
      1 | C
      2 | A
      2 | B
      2 | C
      3 | A
      3 | B
      3 | C
(9 rows)
SELECT n AS number, chr(ascii('A') + L - 1) AS letter
FROM
    generate_series(1, 5) N
CROSS JOIN
    generate_series(1, 5) L
ORDER BY N, L;
 number | letter
--------+--------
      1 | A
      1 | B
      1 | C
      1 | D
      1 | E
      2 | A
      2 | B
      2 | C
      2 | D
      2 | E
      3 | A
      3 | B
      3 | C
      3 | D
      3 | E
      4 | A
      4 | B
      4 | C
      4 | D
      4 | E
      5 | A
      5 | B
      5 | C
      5 | D
      5 | E
(25 rows)
Cartesian product as a result of inner join

Cartesian product as a result of inner join


By : shinichikuto
Date : March 29 2020, 07:55 AM
I hope this helps . Try this one ,since you want to select the movies and roles of actor for a specific actor you can skip the join on actor table, also in your query you are joining movies two times
code :
SELECT mo.date, mo.title, r.roleDescription
FROM movies mo, 
INNER JOIN role r
ON r.movieCode=mo.movieCode
WHERE r.actorid=2
Why using a UDF in a SQL query leads to cartesian product?

Why using a UDF in a SQL query leads to cartesian product?


By : Saketh
Date : March 29 2020, 07:55 AM
I wish did fix the issue.
Why using UDFs leads to a Cartesian product instead of a full outer join?
SQL JOIN Multiplies Result Set - Cartesian Product

SQL JOIN Multiplies Result Set - Cartesian Product


By : Himanshu Gupta
Date : March 29 2020, 07:55 AM
it fixes the issue You could number rows in tables i and oi at first using function row_number() and then use it as part of joining condition:
code :
with i as  (select row_number() over (partition by order_id order by id) rn, i.*  
              from invoice i),
     oi as (select row_number() over (partition by order_id order by id) rn, oi.* 
              from order_items oi)
select i.name, i.comments, o.order_number, oi.strain_id, oi.species_id
  from i left join orders o on i.order_id = o.id
  left join oi on oi.order_id = o.id and oi.rn = i.rn
create table invoice (id number(4), order_id number(4), 
  name varchar2(15), comments varchar2(20));
insert into invoice values (1, 500, 'Males', 'Mice');
insert into invoice values (2, 500, 'Females' ,'Mice');
insert into invoice values (3, 500, 'Courier Fee', 'Within City');
--
create table orders (id number(4), order_number varchar2(15));
insert into orders values (500, 'AN5246516264');
--
create table order_items(id number(5), order_id number(4), 
  strain_id number(5), species_id number(5), comments varchar2(20));
insert into order_items values(1100, 500, 858,  9876, 'Mice');
insert into order_items values(1101, 500, 858,  9876, 'Mice');
insert into order_items values(1102, 500, NULL, NULL, 'Within City');
NAME            COMMENTS             ORDER_NUMBER    STRAIN_ID SPECIES_ID
--------------- -------------------- --------------- --------- ----------
Males           Mice                 AN5246516264          858       9876
Females         Mice                 AN5246516264          858       9876
Courier Fee     Within City          AN5246516264              
Collapse the result of the cartesian product

Collapse the result of the cartesian product


By : Will Wang
Date : March 29 2020, 07:55 AM
around this issue Its only a partial solution but assuming you know for certain that the result is a valid cartesian product generated by itertools.product and it is over lists of distinct values
code :
>>> [list(collections.OrderedDict.fromkeys(y)) for y in zip(*cartesian_product)]
[['a', 'b', 'c'], [1, 2, 3]]
>>> source = [['a', 'b', 'c'], [1, 2, 3], [3, 5, 7]]
>>> cartesian_product = itertools.product(*source)
>>> [list(collections.OrderedDict.fromkeys(y)) for y in zip(*cartesian_product)]
[['a', 'b', 'c'], [1, 2, 3], [3, 5, 7]]
Related Posts Related Posts :
  • SQL Query - Group consecutive items based on condition
  • Users who work in same department
  • Syntax error near column value Vb
  • Oracle Trigger BEFORE INSERT has No data found
  • What kind of join to use on SQL tables
  • Is there a way to add a constant value dynamically to all records returned in Hive?
  • SQL optimization (inner join or selects)
  • EF 6.x, LINQ-to-SQL and raw SQL clauses
  • Simple SQL Variable Assignment Only Returns One Letter: Why?
  • Converting a custom timestamp to date
  • SQL Server : inserting Player vs Player names in to new table from tblEntrants
  • invalid identifier in sql
  • PL/SQL - I keep getting this error when concatenating: PLS-00306: wrong number or types of arguments in call to '||'
  • Count records only from left side of a LEFT JOIN
  • get everything before a string including itself oracle
  • Format Data from Word Doc to SQL using RegEX
  • Conditional formatting on MAX value row
  • MS-Access : selecting data from two tables and only returning you need
  • SQL Server: optimal indexing strategies for many-to-many join
  • DBgrid column very wide
  • PostgreSQL Group values by category, count and calculate percentage
  • MS Access SQL - Most Recent Record for Each Consultant ID
  • Update table: Summary of previous rows without using cursor or while loop
  • PostgreSQL: built-in function to remove substring starting with certain pattern
  • ORA-00909: invalid number of arguments
  • How to summarize all possible combinations of variables?
  • Select Column within a Column SQL
  • PostgreSQL Inserting 2 relationships at once
  • T sql - How to store results from a dynamic query using EXEC or EXECUTE sp_executesql
  • How do I parse my json into CSV using regex?
  • Reverse foreign key cascading (or how to collect database garbage)
  • SQL Pivot Questions
  • Insert records into a table with a condition in SQL Server 2016
  • display null value using rank functions in oracle sql
  • SQL - Get count of group by column but also select top item of group
  • How to add an array of datarows into an exisitng table inside my database
  • There is no unique constraint matching given keys for referenced table "employee" 1
  • SQL: Unable to SELECT joined column
  • How to find out how much space a SQL Server table uses?
  • Window function to remove specific records from SQL Server dataset
  • How to add a column for each day in sql?
  • Create group column based on the specific rows
  • Not sure if this consistitues a transitive dependency
  • How to compare the values in a column to a long list in SQL Server
  • Preserving data format Decimal(6,5) from vba to sql
  • Oracle Query to rollup QTY by Year- only last 3 years
  • SQL - Calculate 2 columns and view result to another to column
  • Divide or Multiply according to a condition (Improving query)
  • PostgreSQL unnest() with consecutive integers grouped by number
  • SQL to limit output to certain months and years
  • VARCHAR TIME TO GET THE DIFFERENCE
  • SQL conditional constraint on multiple columns being unique
  • Optimize a SQL select query in a loop
  • BTEQ Teradata Import Multiple files into one table
  • Update SQL datetime column with oldest values of another table column?
  • Is INSERT ... SELECT an atomic transaction?
  • SQL query completed successfully but not results
  • SQL sub select returning multiple values
  • Verify condition on two columns
  • SQL conditional field, first match JOIN
  • shadow
    Privacy Policy - Terms - Contact Us © voile276.org