Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

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

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

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

>Solution :

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'
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading