CODEWITHSUNDEEP
mysqlsqlsql-server
Jayson
Jayson

Reputation: 83

SQL Query multiple tables with records not existing in 1

So I always have trouble with this type of query. I have 2 tables and I need to combine them as 3 but I need the query to see if a record does not exist in one connection.

The design is this: Table 1 connects to Table 2 looking for existing records. Table 1 then needs to connect to table 2 again on a different record id but looking for not existing.

My current code looks like this:

select p.ProfileId, p.OrgInd, p.OrgName, p1.OrgInd, p1.ProfileId
from profile as p inner join
ProfileRelations as pa on p.ProfileId = pa.ProfileID inner join
profile as p1 on p1.ProfileId = pa.RelProfileID

I need profile(p) to connect to PRofileRelations(pa) as it states but I then need to connect ProfileRelations(pa) back on Profile(p1) where p1.profileid does not exist in ProfileRelations(pa) looking specifically at the column RelProfileID.

This is an association table to look at related records. The Profile table holds all of my customers and I can relate them as connections. What I am looking for is show me any profile that has no related records. The code given shows me those who do have related records, I just now want to see those who have none.

Thanks.

Jayson

Upvotes: 0

Views: 1227

Answers (3)

Thorsten Kettner
Thorsten Kettner

Reputation: 94904

First of all: You want to select profiles. So do this; select from the profiles table. You want to apply criteria (missing relations); so put this in the WHERE clause. Criteria belongs in the WHERE clause. There is no need to join.

All profiles that have no related profiles:

select *
from profile
where ProfileId not in (select ProfileID from ProfileRelations)
  and ProfileId not in (select RelProfileID from ProfileRelations);

or

select *
from profile p
where not exists
(
  select *
  from ProfileRelations pr
  where p.ProfileId in (pr.ProfileId, pr.RelProfileID)
);

All profile pairs that are not related:

select *
from profile p1
join profile p2 on p2.ProfileID <> p1.ProfileID
where not exists
(
  select *
  from ProfileRelations pr
  where (pr.ProfileId = p1.ProfileId and pr.RelProfileID = p2.ProfileId)
     or (pr.ProfileId = p2.ProfileId and pr.RelProfileID = p1.ProfileId)
);

Upvotes: 1

xQbert
xQbert

Reputation: 35323

Visual understanding of Joins: https://blog.codinghorror.com/a-visual-explanation-of-sql-joins/

Use a left join:

All profiles without a related relation. In this case we join the tables but we know the values for p1 and pa will be null becuase the left join will include all profiles and only keep those with a related record in the relations table.

SELECT p.ProfileId, p.OrgInd, p.OrgName, p1.OrgInd, p1.ProfileId
FROM profile as p 
LEFT join ProfileRelations as pa 
   on p.ProfileId = pa.ProfileID 
LEFT join profile as p1 
  on p1.ProfileId = pa.RelProfileID
WHERE PA.ProfileID is null

In the above P1 values will always be null because of the where clause.

OR uses not exists and correlated subquery

All profiles w/o a related profile relation. since we know there is no relation there is no data we need from the other tables; thus we can exclude them from the select simplifying the query and gaining performance.

SELECT p.ProfileId, p.OrgInd, p.OrgName
FROM profile as p 
WHERE NOT EXISTS (SELECT * 
                  FROM ProfileRelations as pa 
                  WHERE p.ProfileId = pa.ProfileID)

In this we don't select the P1 table at all since there is no relation record.

Upvotes: 1

dev8080
dev8080

Reputation: 4020

Per your problem statement, please try this:

select p.ProfileId, p.OrgInd, p.OrgName, p1.OrgInd, p1.ProfileId
from profile as p 
        inner join
     ProfileRelations as pa 
        on p.ProfileId = pa.ProfileID 
where pa.RelProfileID not in (select ProfileId from profile);

Upvotes: 0

Related Questions