CODEWITHSUNDEEP
mysqlsqljoin
Niko Laus
Niko Laus

Reputation: 1

How to get all entries with SQL query with join

kon

id | name
1    alex
2    peter
3    john

ticket

id  | amount | kon_id | package
122   13        1        234
123   12        1        234
124   20        2        NULL
125   23        2        235
126   19        1        236

I would like to get a list of all contacts with the sum of the amount, except tickets, where the package entry is NULL. My problem is, that I only get the contacts which have a ticket, because of the WHERE clause. 

SELECT 
    kon.id, 
    kon.name, 
    SUM(ticket.amount) 
FROM kon LEFT JOIN ticket ON kon.id = ticket.kon_id
WHERE ticket.package IS NOT NULL
GROUP BY kon.id

At the moment, the output looks like this

1   alex    44
2   peter   23

but it should look like this

1   alex    44
3   john    NULL
2   peter   23

I use a MySQL Server. Is it possible to solve this?

Upvotes: 0

Views: 316

Answers (6)

Niko Laus
Niko Laus

Reputation: 1

I figured out the fastest way to solve the problem. It takes about 0.2s compared to the other solutions (2s - 2min). The CAST is important, otherwise the summation of double variables is wrong (float string problem).

SELECT 
  kon1, 
  kon2, 
  SUM(CAST(kon3 AS DECIMAL(7,2))) 
FROM (
  SELECT k.id kon1, k.name kon2, t.amount kon3 FROM kon as k
  LEFT JOIN ticket t ON k.id = t.ticket_kon
WHERE t.package IS NOT NULL
UNION ALL
  SELECT k.id kon1, k.name kon2, NULL kon3 FROM kon k WHERE) t1
GROUP BY kon1, kon2

Upvotes: 0

Saif
Saif

Reputation: 2679

the following query return your expected result 

SELECT 
 kon.id, 
 kon.name, 
 SUM(ticket.amount) as 'amount'
FROM kon LEFT JOIN ticket ON kon.id = ticket.kon_id
GROUP BY kon.id, kon.name

attached image shows the result

enter image description here

Upvotes: 0

vitalygolub
vitalygolub

Reputation: 735

Generally, "ticket.package IS NOT NULL" is wrong condition: your query becomes inner join from left join. If ticket.package should be NOT NULL to add from amount, it should be not in condition, but inside SUM agregate function.

working example for MS SQL

SELECT 
    kon.id, 
    min(kon.name), 
    SUM(case when package is NULL then 0 else ticket.amount end) 
FROM @kon kon LEFT JOIN @ticket ticket ON kon.id = ticket.kon_id
GROUP BY kon.id

Answer from Mr. Bhosale is right too, but for big tables will have worse performance (the reason is subquery)

Upvotes: 0

Alfaiz Ahmed
Alfaiz Ahmed

Reputation: 1728

Begin Tran
Create Table #Kon (id INt , name Nvarchar(255))
Insert into #Kon
Select 1,'alex' UNION ALL
Select 2,'peter' UNION ALL
Select 3,'john'


Create Table #Ticket (id  int,amount int,Kon_Id Int,Package Int)

INSERT INTO #Ticket
SELECT 122,13,1,234  UNION ALL
SELECT 123,12,1,234  UNION ALL
SELECT 124,20,2,NULL UNION ALL 
SELECT 125,23,2,235  UNION ALL
SELECT 126,19,1,236


SELECT K.id, Name,SUM(amount) amount
FROM #Kon k  
LEFT JOIN #Ticket T ON K.id=T.Kon_Id
GROUP BY K.id,Name

RollBAck Tran

Upvotes: 0

Jaydip Jadhav
Jaydip Jadhav

Reputation: 12309

Replace Where with AND

SELECT 
   kon.id, 
   kon.name, 
   SUM(ticket.amount) 
FROM kon LEFT JOIN ticket ON kon.id = ticket.kon_id AND ticket.package IS NOT NULL
GROUP BY kon.id

Upvotes: 2

Mr. Bhosale
Mr. Bhosale

Reputation: 3106

Check This.

        SELECT 
        k.id, 
        k.name ,
        coalesce  (SUM(t.amount) ,0)
        FROM kon k LEFT JOIN
        ( select id,amount,kon_id,package from  ticket where package is not null ) t  
        ON k.id = t.kon_id
        GROUP BY k.id, k.name

OutPut :

enter image description here

Upvotes: 0

Related Questions