Hello again.
Need your help on the following, please. I have two tables of fruits as below. Some of them can be red, some - may not.
If at least one of the fruits is red, I want to return all the fruits within this fruit type from BOTH of the tables.
In the example below I want to retrieve ALL the berries as well as the apples, but NOT the pears.
WITH cde_a AS
(SELECT 'Berries' AS fruit_type,
'strawberries' AS fruit,
1 AS red
UNION ALL
SELECT 'Berries' AS fruit_type,
'raspberries' AS fruit,
1 AS red
UNION ALL
SELECT 'Berries' AS fruit_type,
'Billberies' AS fruit,
0 AS red
UNION ALL
SELECT 'Berries' AS fruit_type,
'bkacberries' AS fruit,
0 AS red
UNION ALL
SELECT 'Apples' AS fruit_type,
'Golden Dellicious' AS fruit,
0 AS red
UNION ALL
SELECT 'Apples' AS fruit_type,
'Ananasrenette' AS fruit,
0 AS red
UNION ALL
SELECT 'Apples' AS fruit_type,
'Yellow Transparent' AS fruit,
0 AS red
),
cde_b AS
(
SELECT 'Pears' AS fruit_type,
'Beurré Superfin' AS fruit,
0 AS red
UNION ALL
SELECT 'Pears' AS fruit_type,
'Beurré Hardy' AS fruit,
0 AS red
UNION ALL
SELECT 'Apples' AS fruit_type,
'Arkansas Black' AS fruit,
1 AS red)
I was trying to use CROSS APPLY, but to no avail. Can one advise, please?
SELECT cde_a.* FROM cde_a
CROSS APPLY (SELECT * FROM cde_b
WHERE cde_b.red = 1
AND cde_b.fruit_type = cde_a.fruit_type ) b
WITH cde_a AS
(SELECT 'Berries' AS fruit_type,
'strawberries' AS fruit,
1 AS red
UNION ALL
SELECT 'Berries' AS fruit_type,
'raspberries' AS fruit,
1 AS red
UNION ALL
SELECT 'Berries' AS fruit_type,
'Billberies' AS fruit,
0 AS red
UNION ALL
SELECT 'Berries' AS fruit_type,
'bkacberries' AS fruit,
0 AS red
UNION ALL
SELECT 'Apples' AS fruit_type,
'Golden Dellicious' AS fruit,
0 AS red
UNION ALL
SELECT 'Apples' AS fruit_type,
'Ananasrenette' AS fruit,
0 AS red
UNION ALL
SELECT 'Apples' AS fruit_type,
'Yellow Transparent' AS fruit,
0 AS red
),
cde_b AS
(
SELECT 'Pears' AS fruit_type,
'Beurré Superfin' AS fruit,
0 AS red
UNION ALL
SELECT 'Pears' AS fruit_type,
'Beurré Hardy' AS fruit,
0 AS red
UNION ALL
SELECT 'Apples' AS fruit_type,
'Arkansas Black' AS fruit,
1 AS red
),
cde_red_check AS (
SELECT DISTINCT fruit_type
FROM cde_a
WHERE red = 1
UNION
SELECT DISTINCT fruit_type
FROM cde_b
WHERE red = 1
)
SELECT a.*
FROM cde_a a
INNER JOIN cde_red_check rc ON rc.fruit_type = a.fruit_type
UNION ALL
SELECT b.*
FROM cde_b b
INNER JOIN cde_red_check rc ON rc.fruit_type = b.fruit_type
ORDER BY a.fruit_type, a.fruit
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 15, 2021 at 6:51 pm
-- Just paste the following text below the first part of the example... Enjoy!
-- RamonS, setembre 21... Vacunadets, ja podem fer xerinola!!
, AllFruits AS (
SELECT * FROM cde_a
UNION ALL
SELECT * FROM cde_b
)
, TypeOfRed AS (
SELECT DISTINCT fruit_type FROM AllFruits
WHERE
red = 1
)
SELECT * FROM AllFruits
WHERE fruit_type IN (SELECT * FROM TypeOfRed)
September 16, 2021 at 8:34 am
Thank you for posting your solutions, I just wonder how those multiple SELECT statements will affect the server, when the real tables will be very big >1M each.
September 16, 2021 at 9:08 am
Thank you for posting your solutions, I just wonder how those multiple SELECT statements will affect the server, when the real tables will be very big >1M each.
Not sure what sort of answer you are expecting, but 'adversely' is the best I can come up with.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
September 16, 2021 at 10:19 am
I am not arguing nor disapproving anyone and fully appreciate the time people took to reply.
My concern was if the proposed solution, with several SELECT statements from the same table, will not cause performance issue, but on the other hand, I presume it will be cached on the first SELECT and the others will never go to the disk, so no issue here.
September 16, 2021 at 10:26 am
I am not arguing nor disapproving anyone and fully appreciate the time people took to reply.
My concern was if the proposed solution, with several SELECT statements from the same table, will not cause performance issue, but on the other hand, I presume it will be cached on the first SELECT and the others will never go to the disk, so no issue here.
Your presumption may or may not be correct, but if you have a very large table and intend to work with a subset of rows from that table using multiple queries, it may help performance if you first of all create an appropriately indexed temp table and push the rows of interest into that, before proceeding with the multiple queries against that temp table.
Whether this is a better-performing solution than multiple queries against your original table is something which comprehensive testing will indicate.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply