Query to list columns where some values are not available

I have two tables in a database that look like this:

CREATE TABLE PAR (
    A           varchar(10) not null,
    T               varchar(25) not null,
    U               varchar(25) not null,
    V               varchar(30) not null,
    primary key (A),  
);

INSERT INTO PAR (A, T, U, V)
VALUES
('A5065','Choco','Belt','Texas'),
('B6060','Koro','Tan','Montana'),
('C2036','Beta','Blast','Washington'),
('D2000','Tao','Bet','Kentucky');



CREATE TABLE AT (
    A       varchar(10) not null,
    B               char(1),
    C       integer,
    D       varchar(20),
    primary key (A),
    foreign key (A) references PAR(A)
);

INSERT INTO AT (A, B, C, D)
VALUES
('A5065','Z','1660','Apple'),
('B6060','D','1598','Banana');

I am trying to get a table like this. There will be null values since not every column in Table PAR will have the same key in table AT

|A5065| 1660
|B6060| 1598
|C2036| 'blank'
|D2000| 'blank'

I tried to use the following query but I did not get my output. I also tried to use IFNULL but I still cant seem to figure this out

SELECT P.A, A.A
FROM PAR AS P, AT AS A
LEFT OUTER JOIN PARTICIPANT USING (A)
WHERE A.C IS NULL;

Answer

SELECT P.A, A.C
FROM PAR AS P
LEFT OUTER JOIN AT AS A
ON P.A =A.A

What you want here is all values from table P irrespective there are values in table A like A.C and A.D will be blank in final output

So output from above query will be

|A5065| 1660
|B6060| 1598
|C2036| 'blank'
|D2000| 'blank'

Advertisement

Answer