Using Left Join and Inner Join in the Same Query

left join and group of inner join

Use paretheses to force joins order, kind of

SELECT * 
FROM (
   TABLE1 
   JOIN TABLE2 ON ...)
LEFT JOIN (
    TABLE3 
    JOIN TABLE3_1 ON ...
    JOIN TABLE3_2 ON ...
    JOIN TABLE3_3 ON ...) ON ...

Inner join and left join gives same results with where filter and hence which join to use?

This happens when you apply the filter (where clause condition) on the table you're left joining on. In this case the 'Order' table.

It is because your WHERE clause explicitly filters rows from the Order table where the color is pink. It will then join on only the matching Order rows on the Customer table.

You'll see that when you remove the where clause, the left join will function as you expect. :)

How to use Inner Join and Left Outer Join together?

Your problem comes from trying to use the out-dated comma operator for your inner joins.

If you switch to the "modern" (standardised in 1992) JOIN syntax for Query 2, it will look something like this:

SELECT itemNo
FROM Store
INNER JOIN Department 
   ON Store.ShelfNo = Department.ShelfNo
WHERE Department.itemNo is not null 
   AND Store.StoreId in('12345')

You can then add in Item as a second INNER JOIN, relating it to the Department:

SELECT itemNo
FROM Store
INNER JOIN Department 
   ON Store.ShelfNo = Department.ShelfNo
INNER JOIN Item
   ON Department.itemNo = Item.item
WHERE Department.itemNo is not null 
   AND Store.StoreId in('12345')

And then add in your LEFT OUTER JOIN and extra WHERE condition from Query 1:

SELECT itemNo, Description.itemName, ItemScale, ItemExpiry
FROM Store
INNER JOIN Department 
   ON Store.ShelfNo = Department.ShelfNo
INNER JOIN Item
   ON Department.itemNo = Item.item
LEFT OUTER JOIN Description 
   ON Item.uniqueLabel = Description.uniqueLabel
WHERE Department.itemNo is not null 
   AND Store.StoreId in('12345')
   AND Item.uniqueLabel like '11%'

That query may not be exactly what you want, and some of the conditions are possibly redundant once the queries are merged, but hopefully this shows how the explicit join syntax can mix INNER and OUTER joins.

Inner Join and Left Join in same sql query

Since you are doing a left join, cc.CLIENT_ID is null for all the values which only exist in CLIENT_CONTACTS and your where clause Where cc.CLIENT_ID = 20111
converts your LEFT JOIN into INNER JOIN. Adding this filter in ON clause solves the issue.

select * 
from [UandMePROD].[dbo].EVENT_INVITATIONS EI
inner join [UandMePROD].[dbo].USERINFO UI on EI.[USER_ID] = UI.[USER_ID] 
 left join CLIENT_CONTACTS CC             on UI.MOBILENO = CC.MOBILE_NUMBER                 
                                         and cc.CLIENT_ID = 20111
where  EI.EVENT_ID=11033

Left join and Inner join in same mysql query not working

There are four tags and you want four result records, one per tag. So select from the tags table. You get the count with a sub-select.

select
  tag_id, 
  tag,
  (
    select count(*)
    from mp_desk_tags_match dtm
    where dtm.tag_id = dt.tag_id
    and u_cust_id in
    (
      select u_cust_id
      from mp_desk_agent_added_customers
      where agent_id = 22
    )
  ) as tag_count
from mp_desk_tags dt;

Here is the same with joins:

select
  dt.tag_id, 
  dt.tag,
  count(*)
from mp_desk_tags dt
left join mp_desk_tags_match dtm on dtm.tag_id = dt.tag_id
left join mp_desk_agent_added_customers daac on daac.u_cust_id = dtm.u_cust_id 
                                                and daac.agent_id = 22
group by dt.tag_id;

---

Related Topics