Join and union, I what one to tamota against two ID 5

Question

1.00/5 (2 votes)

See more:

I have two tables, oranges and apples. ID 5 in oranges has two items, but ID 5 in apples has one item. When I run the query below, two same items are show in apples table with two different items oranges table. I want the result below.

oranges

ID  Variety     Price
4   Valencia    4
5   Navel       5
5   banana      5
1   Fuji        5
2   Gala        6

apples

ID  Variety    Price
1   Fuji         5  
2   Gala         6
3   carrot       6
5   tamota       5

Query

SQL

select * from apples as a
    left join oranges as o on a.ID = o.ID
union
select * from apples as a
    right join oranges as o on a.ID = o.ID

Result

ID  Variety Price  ID Variety      Price
5   tamota    5     5   Navel       5
5   tamota    5     5   banana      5
1   Fuji      5     1   Fuji        5
2   Gala      6     2   Gala        6
3   carrot    6     null   null    null 
null null   null    4   Valencia    4

Required result

ID   Count   Variety Price  ID   Count   Variety      Price
5      1    tamota    5     5      1       Navel       5
null   0     null    null   5      2      banana       5      
1      1    Fuji      5     1      1      Fuji         5
2      1    Gala      6     2      1      Gala         6
3      1   carrot    6     null    0       null      null 
null   0   null     null    4      1     Valencia      4

What I have tried:

It will give count of duplicate ID number, let is ID 5 is repeated 2 times to it show ID 5 count 1 and ID 5 count 2,

regards

juzar para

Posted 21-Aug-18 21:10pm

juzar Para

Updated 28-Aug-18 0:06am

v3

Add a Solution

Comments

MadMyche 22-Aug-18 9:29am

You should use a Primary Key; as your ID column obviously does not have a unique constraint on it

Herman<T>.Instance 22-Aug-18 11:13am

exactly

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

MadMyche · Answer 1 · 2018-08-22T06:02:00

The whole premise of this question just seems... flawed.
You will need to provide some more context on what the actual needs are (written business) as the logic for the "required" result seems to be incorrect.

That said; this looks like this should be an everyday parent-child table relationship; with tables for Fruits (parent) and Varieties (children).

SQL

DECLARE @Fruits TABLE (
  FruitID    INT PRIMARY KEY  NOT NULL,
  FruitName  NVARCHAR(16)     NULL
)

DECLARE @Varieties TABLE (
  VarietyID     INT PRIMARY KEY	NOT NULL,
  FruitID       INT             NULL,
  VarietyName   NVARCHAR(16)  	NULL,	
  VarietyPrice  INT             NULL,
  WorthlessID   INT             NULL
)

INSERT @Fruits (FruitID, FruitName) 
VALUES  (1, 'Apple')
,       (2, 'Orange')

INSERT @Varieties(VarietyID, FruitID, VarietyName, VarietyPrice, WorthlessID)
VALUES  (1, 1, 'Fuji', 5, 1)
,       (2, 1, 'Gala', 6, 2)
,       (3, 1, 'Carrot', 6, 3)
,       (4, 1, 'Tamota', 5, 5)
,       (5, 2, 'Valencia', 4, 4)
,       (6, 2, 'Navel', 5, 5)
,       (7, 2, 'Banana', 5, 5)
,       (8, 2, 'Fuji', 5, 1)
,       (9, 2, 'Gala', 6, 2)

SELECT  v.VarietyID, f.FruitName, v.VarietyName, v.VarietyPrice
FROM    @Fruits     f
JOIN    @Varieties  v ON f.FruitID = v.FruitID

Which returns

VarietyID   FruitName        VarietyName      VarietyPrice WorthlessID
----------- ---------------- ---------------- ------------ -----------
1           Apple            Fuji             5            1
2           Apple            Gala             6            2
3           Apple            Carrot           6            3
4           Apple            Tamota           5            5
5           Orange           Valencia         4            4
6           Orange           Navel            5            5
7           Orange           Banana           5            5
8           Orange           Fuji             5            1
9           Orange           Gala             6            2