CODEWITHSUNDEEP
mysqlsqljoinleft-join
BostonAreaHuman
BostonAreaHuman

Reputation: 1461

I'm trying to only get one row from a LEFT JOIN

Temp ordering table

temp_id     series_id   news_ticket_breakdown_id    quantity    expiry_time
58db09bba25b4   2                  2                     2       1490750973
58db09bba25b4   2                  4                     1      1490750973

NEWS ITEMS TABLE

news_id   series_id      news_title
   26          2         GENERIC TICKETING
   27          2         GENERIC TICKETING

When I do a left join on series_id I get back 4 rows and I only want 2. I'm not sure how to limit the amount of joined rows to one row.

CURRENT QUERY

SELECT 
    * 
FROM 
    ticket_ordering_temp    
LEFT JOIN 
    news_items 
on 
    ( news_items.series_id = SELECT 
                                DISTINCT(event_id)
                             FROM 
                                news_items 
                             where 
                                series_id=ticket_ordering_temp.series_id
    )
where 
    temp_id='58db09bba25b4''

DESIRED RESULT I want just 1 row from table 2 and not 2 rows joined as stated above

temp_id     series_id   news_ticket_breakdown_id    quantity    expiry_time news_title
58db09bba25b4   2                  2                     2       1490750973  GENERIC TICKETING
58db09bba25b4   2                  4                     1      1490750973 GENERIC TICKETING

Upvotes: 0

Views: 83

Answers (2)

Vijunav Vastivch
Vijunav Vastivch

Reputation: 4191

try this Querying with tempTable:

select distinct * from
(select  * from 
(select '58db09bba25b4' as temp_id,   2 as series_id,2 as news_ticket_breakdown_id,2 as quantity,1490750973 as  expiry_time  union all
select '58db09bba25b4' ,  2   ,              4  ,                  1,     1490750973) as tbl_order) as tbl_order

left join


(select series_id,news_title from 
(select  26 as  news_id,         2 as series_id,         'GENERIC TICKETING' as news_title union all
select   27  ,        2 ,        'GENERIC TICKETING') as tbl_items) tbl_items
on tbl_order.series_id = tbl_items.series_id where temp_id ='58db09bba25b4'

Querying With actual table:

select DISTINCT * from
(select temp_id,series_id,news_ticket_breakdown_id,quantity,expiry_time from tbl_order) as tb_order
left join
(select series_id,news_title from tbl_items) as tb_items
on tb_order.series_id = tb_items.series_id where temp_id ='58db09bba25b4'

result:

58db09bba25b4   2   2   2   1490750973  2   GENERIC TICKETING
58db09bba25b4   2   4   1   1490750973  2   GENERIC TICKETING

Just giving an another solution.

Upvotes: 0

Blank
Blank

Reputation: 12378

For your sample data and your desired result, you can try following sql to do:

SELECT 
    ticket_ordering_temp.*, t.news_title
FROM 
    ticket_ordering_temp    
LEFT JOIN( 
    SELECT DISTINCT series_id, news_title
    FROM news_items
) t ON ticket_ordering_temp.series_id = t.series_id
WHERE temp_id = '58db09bba25b4'

however, I think you should tell us the logic behind these sample data.

Demo in SqlFiddle

Upvotes: 2

Related Questions