How do you perform a CROSS JOIN with LINQ to SQL?
How do you perform a CROSS JOIN with LINQ to SQL? ...
cross-join
Massive CROSS JOIN in SQL Server 2005
I'm porting a process which creates a MASSIVE CROSS JOIN of two tables. The resulting table contains 15m records (looks like the process makes a 30m cross join with a 2600 row table and a 12000 row table and then does some grouping which must split it in half). The rows are relatively narrow - just 6 columns. It's been running for 5 h...
Cross join (pivot) with n-n table containing values
I have 3 tables : TABLE MyColumn ( ColumnId INT NOT NULL, Label VARCHAR(80) NOT NULL, PRIMARY KEY (ColumnId) ) TABLE MyPeriod ( PeriodId CHAR(6) NOT NULL, -- format yyyyMM Label VARCHAR(80) NOT NULL, PRIMARY KEY (PeriodId) ) TABLE MyValue ( ColumnId INT NOT NULL, PeriodId CHAR(6) NOT NULL, Amount DECIMAL(8, 4) NOT NU...
Mixing implicit and explicit JOINs
I am having a problem with Hibernate generating invalid SQL. Specifically, mixing and matching implicit and explicit joins. This seems to be an open bug. However, I'm not sure why this is invalid SQL. I have come up with a small toy example that generates the same syntax exception. Schema CREATE TABLE Employee ( employeeID INT, name...
Are these cross joins causing me problems?
I have a poor man's replication setup that I can't do anything about. Some identifying data (basically primary key) from a call_table is copied into another table via a simple trigger, and then the "replication server" runs a stored procedure to copy the data from the queue table to a #temp table (to prevent locking in SQL 6.5 is the cas...
Is the performance of SQL Server cross joins with conditionals in the where statement the same as inner joins with conditionals in the join's on statement?
I am trying to determine the performance of a piece of generated SQL that runs in SQL Server 2005. It uses CROSS JOINS, but the conditionals tying the cross joined tables are in the where statement. I previously thought that all cross joins that have where statements would first pull the full cartesian product and then apply the filter...
Self-Joins, Cross-Joins and Grouping
I've got a table of temperature samples over time from several sources and I want to find the minimum, maximum, and average temperatures across all sources at set time intervals. At first glance this is easily done like so: SELECT MIN(temp), MAX(temp), AVG(temp) FROM samples GROUP BY time; However, things become much more complicated ...
Faster CROSS JOIN alternative - PostgreSQL
I am trying to CROSS JOIN two tables, customers and items, so I can then create a sales by customer by item report. I have 2000 customer and 2000 items. SELECT customer_name FROM customers; --Takes 100ms SELECT item_number FROM items; --Takes 50ms SELECT customer_name, item_number FROM customers CROSS JOIN items; Takes 200000ms I k...
Doctrine: Unable to execute either CROSS JOIN or SELECT FROM Table1, Table2?
Using Doctrine I'm trying to execute either a 1. CROSS JOIN statement or 2. a SELECT FROM Table1, Table2 statement. Both seem to fail. The CROSS JOIN does execute, however the results are just wrong comparing to execution in Navicat. The multiple table SELECT doesn't even execute because Doctrine automatically tries to LEFT JOIN the ...
If Inner Join can be thought of as Cross Join but filtering out the records satisfying the condition, then Left Outer Join is the above, plus 1 record for the "no match" on the Left?
If an Inner Join can be thought of as a cross join and then getting the records that satisfy the condition, then a LEFT OUTER JOIN can be thought of as that, plus ONE record on the left table that doesn't satisfy the condition. In other words, it is not a cross join that "goes easy" on the left records (even when the condition is not sa...
What does this MySQL statement do?
INSERT IGNORE INTO `PREFIX_tab_lang` (`id_tab`, `id_lang`, `name`) (SELECT `id_tab`, id_lang, (SELECT tl.`name` FROM `PREFIX_tab_lang` tl WHERE tl.`id_lang` = (SELECT c.`value` FROM `PREFIX_configuration` c WHERE c.`name` = 'PS_LANG_DEFAULT' LIMIT 1) AND tl.`id_tab`=`PREFIX_tab`.`id_tab`) F...
Crystal Reports - Include null values in all report groups
I have some data that shows the number of visits a person has done to all companies in our database. What I would like to do is show a report of all the visits and all the ones they haven't visited, grouped by month. For example, assuming the entire database of companies is CompanyA, CompanyB, CompanyC and CompanyD and the visit data i...
SQL Server: What is the difference between CROSS JOIN and FULL OUTER JOIN?
What is the difference between CROSS JOIN and FULL OUTER JOIN in SQL Server? Are they the same, or not? Please explain. When would one use either of these? ...
Cross Join followed by Left Join
Is it possible to do a CROSS JOIN between 2 tables, followed by a LEFT JOIN on to a 3rd table, followed by possibly more left joins? I am using SQL Server 2000/2005. I am running the following query, which is pretty straightForward IMO, but I am getting an error. select P.PeriodID, P.PeriodQuarter, P.PeriodYear, ...