Retrieving unjoined/non-overlapping rows-CodePudding

I'm using the following query to join Table_A and Table_B:

select *
from Table_A a
join Table_B b on a.Country = b.Land and a.City = b.Town and A.Language = B.Spoken;

Is it possible to retrieve the rows in Table_A that was not joined? I need to know which rows that are overlapping, and which are not. Maybe there's a better solution than "join on" in this case?

UPDATE WITH REAL DATA

I'm using the following query to join the tables schedulingErrorsProd and serviceProductions:

select *
from schedulingErrorsProd a
join serviceProductions b on a.receiverid = b.logicalAdress_logicalAddress and a.tjanstekontrakt = b.serviceContract_namespace
where b.connectionPoint_id = 4;

Is it possible to retrieve the rows in schedulingErrorsProd that was not joined? I need to know which rows that are overlapping, and which are not. Maybe there's a better solution than "join on" in this case?

A picture of all tables/columns and their joining:

CodePudding user response：

If all you want is to show all joined records, and all records that have NOT been joined. An so called "Full outer join" that is not supported in MySQL officially. Fiddle: https://www.db-fiddle.com/f/vvcfn2nr7XNiPg9kNggXsd/1

CREATE TABLE table_a (
  continent varchar(50),
  country varchar(50),
  city varchar(50),
  language varchar(50)
);
INSERT INTO table_a (continent,country,city,language) VALUES ('Europe', 'Sweden', 'Stockholm', 'Swedish');
INSERT INTO table_a (continent,country,city,language) VALUES ('Asia', 'Russia', 'Moscow', 'Russian');
INSERT INTO table_a (continent,country,city,language) VALUES ('Europe', 'Norway', 'Oslo', 'Norwegian');

CREATE TABLE table_b (
  land varchar(50),
  town varchar(50),
  spoken varchar(50),
  currency varchar(50)
);

INSERT INTO table_b (land,town,spoken,currency) VALUES ('Sweden', 'Stockholm', 'Swedish', 'Svensk Krona');
INSERT INTO table_b (land,town,spoken,currency) VALUES ('Finland', 'Helsinki', 'Finnish', 'Dunno');
INSERT INTO table_b (land,town,spoken,currency) VALUES ('Norway', 'Oslo', 'Norwegian', 'Norsk Krona');

SELECT a.continent, a.country, a.city, a.language, b.currency
FROM table_a as a
left join table_b as b 
on a.country = b.land and a.city = b.town and a.language = b.Spoken
union
SELECT 
a2.continent, 
case when a2.country is null then b2.land else a2.country end, 
case when a2.city is null then b2.town else a2.city end, 
case when a2.language is null then b2.spoken else a2.language end, 
b2.currency
FROM table_a as a2
right join table_b as b2 
on a2.country = b2.land and a2.city = b2.town and a2.language = b2.spoken

EDIT * Question updated * This is the new code for the new question:

SELECT b.connectionPoint_id, a.timestamp, a.waypoint, a.senderid, a.receiverid, a.tjanstekontrakt, a.errorCode, a.time_producer, a.loglevel, a.loglevel, a.endpoint_url
FROM schedulingErrorsProd as a
left join serviceProductions as b 
on a.receiverid = b.logicalAdress_logicalAddress and a.tjanstekontrakt = b.serviceContract_namespace
and b.connectionPoint_id = 4;

CodePudding user response：

If you want all the rows of serviceProductions then you should use a LEFT join and the condition b.connectionPoint_id = 4 in the ON clause:

SELECT *
FROM schedulingErrorsProd a LEFT JOIN serviceProductions b 
ON a.receiverid = b.logicalAdress_logicalAddress 
AND a.tjanstekontrakt = b.serviceContract_namespace
AND b.connectionPoint_id = 4;

The unmatched rows will have null in all the columns of the table logicalAdress_logicalAddress.

If you want only the unmatched rows of serviceProductions then you should add to the above query a WHERE clause that filters out the matched rows, like:

SELECT a.*
FROM schedulingErrorsProd a LEFT JOIN serviceProductions b 
ON a.receiverid = b.logicalAdress_logicalAddress 
AND a.tjanstekontrakt = b.serviceContract_namespace
AND b.connectionPoint_id = 4;
WHERE b.connectionPoint_id IS NULL;