Get The Inverse of a Join

Get the inverse of a join?

If I understand it correctly, you will have to do a cartersian result of users & groups and reduce the result derived from GroupUsers.

That will give you records of users which do not have any groups attached to it.

I apologize if I didn't understand the question correctly.

EDIT: Cartesian result will give you users * groups. You will have to subtract GroupUsers from it. I am sorry, I do not have SQL ready for it & can't try it out at this point.

SELECT, get the invert result from the WHERE on the JOIN

You can make it by using not exists.

SELECT sh__shop.id AS shop_id, sh__shop.name
FROM sh__shop 
WHERE 
    NOT EXISTS (SELECT * FROM sh__shop_invoice i
            WHERE i.shop_id = sh__shop.id
                  AND i.month_commission LIKE "2021-08-01");

MySQL Query Inner Join Inverse?

What I'm seeking to do is show [...] all the people who [...] appear in the TRANSACTIONS LIST table but do NOT appear in the USERS table.

You could use not exists:

select t.*
from transactions_list t
where not exists (
    select 1 from users u where t.customer_email = u.email
)

Another way to phrase this is to use an anti-left join (this is more in the spirit of your question, that relates to joins):

select t.*
from transactions_list t
left join users u on t.customer_email = u.email
where u.email is null

This means: try to join each transaction with a user, and filter on those that did not match.

Opposite of inner join

What will be the opposite of inner join?

An OUTER join, which can be of three options:

• LEFT
• RIGHT
• FULL

This is a good visual representation of JOINs

I want to know the rows in Person with bad AddrId which don't have a row in the Address table.

Using LEFT JOIN / IS NULL

SELECT p.*
FROM PERSON p
LEFT JOIN ADDRESS a ON a.addrid = p.addrid
WHERE a.addrid IS NULL;

Using NOT EXISTS

SELECT p.*
FROM PERSON p
WHERE NOT EXISTS (
    SELECT NULL
    FROM ADDRESS a
    WHERE a.addrid = p.addrid
);

Using NOT IN

SELECT p.*
FROM PERSON p
WHERE p.addrid NOT IN (
    SELECT a.addrid
    FROM ADDRESS a
);

Inverse of Inner join (Intersect) with multiple foreign keys

Okay, I think I understand better now.

This should return any entry in purchase that have no matching entry in sales.

SELECT  
  `purchase`.`purchase_id`, `purchase`.`product_id`
FROM `purchase`
  LEFT JOIN `sale` ON `sale`.`purchase_id` = `purchase`.`purchase_id` AND `sale`.`product_id` = `purchase`.`product_id`
WHERE 
  `sale`.`sale_id` IS NULL
ORDER BY 
  `purchase`.`purchase_id`, `purchase`.`product_id`

Doctrine Query reverse join?

You can create a query in ContentRepository like:

$qb = $this
    ->createQueryBuilder('content')
    ->join(
         'App\Entity\Star',
         'star',
         \Doctrine\ORM\Query\Expr\LeftJoin::WITH,
         'content.idcontent = star.content_Idcontent '
         )
    ->orderBy('star.content_Idcontent', 'DESC')
;

$qb->getResult();

Assuming that your Entity Star is located in App\Entity\Star

Opposite Of An Inner Join Query

Select * from table1
left join table2 on table1.id = table2.id
where table2.id is null

Return the Inverse of Tuples from an INNER JOIN Query

you could do another thing by getting all of the courses that is not exists in the courses list for this user like this

select course_name from courses where course_id not in (select user_course_id from users_courses where user_email = 'admin@....')

update

forget to add the column name course_id

---

Related Topics