score:1
Accepted answer
Ok here we go, I built out the table structure to validate the SQL. I did not load any data but it does execute.
SELECT * FROM
(
Select
catalogid, numitems, allitems - numitems ignoreditems
from
(
select
i.catalogid,
case
when ocardtype in ('PayPal','Sofort') then sum(i.numitems)
when ocardtype in ('mastercard','visa') and odate is not null then sum(i.numitems)
else 0 end numitems,
sum(numitems) allitems
from
orders o
inner join
oitems i
on
i.orderid=o.orderid
inner join
products T1
on
T1.catalogid = i.catalogid
group by
i.catalogid, ocardtype, odate
) A
) B
INNER JOIN
(
SELECT
catalogId,
ProcessedSucssessfully =
STUFF((SELECT ', ' + CAST( b.orderid as varchar(10))
FROM oitems b JOIN orders o ON b.orderid = o.orderid
WHERE b.catalogId = a.catalogId
AND (o.ocardtype in ('PayPal','Sofort') OR o.ocardtype in ('mastercard','visa') and o.odate is not null)
FOR XML PATH('')), 1, 2, ''),
NotProcessed =
STUFF((SELECT ', ' + CAST( c.orderid as varchar(10))
FROM oitems c JOIN orders o ON c.orderId = o.orderid
WHERE c.catalogid = a.catalogid
AND (o.ocardtype in ('mastercard') OR o.ocardtype is null) and o.odate is null
FOR XML PATH('')), 1, 2, '')
FROM
oitems a
GROUP BY
a.CatalogId
)C
ON
B.CatalogId = C.CatalogId
Read More
- SQL joining 2 queries that share a column
- Queries that include a column with large NVARCHAR values from from SQL Azure are slow
- In SQL Server, how to SELECT rows that share a common column value?
- SQL Server compare results of two queries that should be identical
- SQL query to determine that values in a column are unique
- Need a datetime column in SQL Server that automatically updates when the record is modified
score:1
SQL server view would look something like this
SELECT * FROM
(
Select
catalogid, numitems, allitems - numitems ignoreditems
from
(
select * from
(
select
i.catalogid,
sum(case when (ocardtype in ('PayPal','Sofort')
OR
ocardtype in ('mastercard','visa') and odate is not null) then numitems
else 0 end) numitems,
sum(numitems) allitems
from
orders
) o
inner join
items i
on
i.orderid=o.orderid
inner join
products T1
on
T1.catalogid = i.catalogid
group by
i.catalogid
) A
) X
) B
INNER JOIN
SELECT * FROM
(
SELECT
catalogId,
ProcessedSucssessfully =
STUFF((SELECT ', ' + CAST( b.orderid as varchar(10))
FROM oitems b JOIN orders o ON b.orderid = o.orderid
WHERE b.catalogId = a.catalogId
AND (o.ocardtype in ('PayPal','Sofort') OR o.ocardtype in ('mastercard','visa') and o.odate is not null)
FOR XML PATH('')), 1, 2, ''),
NotProcessed =
STUFF((SELECT ', ' + CAST( c.orderid as varchar(10))
FROM oitems c JOIN orders o ON c.orderId = o.orderid
WHERE c.catalogid = a.catalogid
AND (o.ocardtype in ('mastercard') OR o.ocardtype is null) and o.odate is null
FOR XML PATH('')), 1, 2, '')
FROM
oitems a
GROUP BY
a.CatalogId
) B
ON A.CatalogId = B.CatalogId
score:2
You could create a view on the sql box and then just Linq to that view
select * from table_a inner join table_b on table_a.field_a = table_b.field_b
EDIT:
or
Select * from (select * from table_a inner join table_b on table_a.field_a = table_b.field_b) AB
inner join
select * from (select * from table_c inner join table_d on table_c.field_c = table_d.field_d) CD
ON
AB.id_column = CD.id_column
More questions
- How do I add a column to a table in SQL Server that doesn't allow nulls?
- SQL Server 2008 Unique Column that is Case Sensitive
- SQL Server: How to use UNION with two queries that BOTH have a WHERE clause?
- Choosing a binary collation that can differentiate between 'ss' and 'ß' for nvarchar column in Sql Server
- In SQL how do you update each row of the table by finding all rows that are equal for a column, then set another column equal to eachother
- SQL Server : removing duplicate column while joining tables
- Joining against a CSV Column in SQL Server
- SQL query to find all tables in a database that have a column with a specific name
- SQL query - Join that returns the first two records of joining table
- SQL Server : there are no primary or candidate keys in the referenced table that match the referencing column list in the foreign key 'FK'
- Find SQL Server Tables that have two specified column names
- SQL - Get the index of column that has maximum value
- Is there a JS library that supports writing linq to sql queries with nodejs?
- Is there a free Web UI that will bind to an sql server database and allow basic column filtering and data export?
- Can queries that read table variables generate parallel exection plans in SQL Server 2008?
- How to find SQL Server queries that took a lot of time?
- Does SQL Server even look at a table when joining on a variable that returns false?
- How to stop stored procs from whining about a missing column that I am about to delete in SQL Server 2008?
- About create single column index on SQL Server for various of queries
- SQL Server 2008 - How do I add a constraint that prevents the same value from appearing in both Column A and Column B?
- How to add a new column that flag duplicate of a column based on value of another column in SQL Server?
- TSQL: Select set of rows that share a single column value
- Is it possible in Microsoft SQL Server to create a column that is a datepart?
- SQL - SELECT a column that compares multiple rows of the same query
More questions with similar tag
- SQL Server query to find all current database names
- How to delete the contents of a database in SQL Server Management Studio?
- WAITFOR command
- SQL: How To Use DateDiff + ORDER BY with INNER JOIN
- SQL- Extracting Text between characters
- Paging in GridView using SqlDataSource
- merging records for sql table based on column data
- How do I incorporate "where [binary] is not null" in a T-SQL select statement?
- send SMS using sql server 2012
- Why do I get this unexpected SQL performance gain?
- How to create trigger inside an IF condition?
- How do I get a user input and apply it in a sql statement in bash?
- WordPress with MSSQL
- Convert flat SQL rows into nested JSON array using FOR JSON
- SQL - Comparing Dates in 2 Tables, Retrieving Most Recent
- ORDER BY with a CASE statement for column with alias
- SQL Server 2000 + ASP.NET: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'
- sql server - Combine tables into one table
- Add days in date in yyyymmdd format SQL server
- SQL Server Express Entity Framework code-first
- How do I rewrite my SQL query to remove redundant values from a GROUP BY column?
- calculate car parking hours in sql server
- What are NDF Files?
- Having roundoff values for columns
- Is it possible to determine when a stored procedure was last modified in SQL Server 2000?
- Operand type clash: int is incompatible with DaysAndHours
- Most efficient way of getting record counts from a related table?
- Trying to access array offset on value of type null but database is not
- Polybase unable to place a WHERE condition on XLSX destination
- Update only the top 1 row in sql server
Source:
stackoverflow.com