CODEWITHSUNDEEP
mysql
Ashwani
Ashwani

Reputation: 732

get data from another table if column is null

I have two table:-

tableA:- 

CREATE TABLE tableA (
  firstId INT(6),
  secondId INT(6),
  product VARCHAR(30) default NULL,
  primary key (firstId, secondId)
)

tableB:-

CREATE TABLE tableB (
  firstId INT(6),
  product VARCHAR(30) default NULL,
  primary key (firstId)
)

INSERT INTO `tableA` (
`firstId` ,
`secondId` ,
`product`
) VALUES ('1',  '10', 'hello'), ('1',  '11', NULL);

INSERT INTO `tableB` (`firstId`, `product`) VALUES ('1', 'original');

Now i will have firstId and secondId, and i have to fetch product details but condition is if combination ( firstId and secondId) lies in tableA then get product details from it and if it is NULL then get product details from TableB in respect to firstId.

For Eg:-

If i have firstID = 1 and secondId = 10 then hello shold get as output

If i have firstID = 1 and secondId = 11 then original shold get as output

If i have firstID = 1 and secondId = 12 then original shold get as output

I need this in one query if possible. Any help is appreciated.

Upvotes: 2

Views: 137

Answers (1)

Circum
Circum

Reputation: 56

You can write a control flow function inside a mysql statement:

$first='1';$second='10';


SELECT IF( exists(select * FROM tableA WHERE  
tableA.firstId =  '$first' and tableA.secondId =  '$second'
and tableA.product!= 'NULL'),tableA.product, tableB.product )
FROM tableA, tableB ;

Upvotes: 1

Related Questions