A column returns null value in SQL statement containing multiple SELECTs

Question

I inherited a particular code from a previous developer. I intend to build the application all over again, but I have to add some functionalities before I proceed.

Firstly, It's a 62 column table that has to do with accounting, and I also have to fetch values from different tables with a single call to get the values that I need before insertion.

Lets say I need to make an insertion into table dailysales and i need to get values from table a,b,c and d at the same time. I already have an sql statement for fetching this values, and it works fine except that a particular column keeps returning as NULL. Here's my code:

SELECT `gds_pnr_ref`, `transaction_date`,
       (SELECT `lastname` FROM `a` WHERE `id` = `staff` LIMIT 1) as `lastname`,
       (SELECT `firstname` FROM `a` WHERE `id` = `staff` LIMIT 1) as `firstname`,
       (SELECT `department_name` FROM `b` WHERE `id` = `staff_department` LIMIT 1) as `department`,
       (SELECT `name` FROM `b` WHERE `memo_serial` = '$some_value' LIMIT 1) as `pax_name`,
       (SELECT `customer_name` FROM `c` WHERE `id` = `customer_name` LIMIT 1) as `customer`,
       travel_product,
       (SELECT `vendor_name` FROM `c` WHERE `id` = `vendor` LIMIT 1) as `vendor` 
FROM `d` WHERE `id` = '$some_value' LIMIT 1

The column (SELECT customer_name FROM c WHERE id = customer_name LIMIT 1) as customer always returns as NULL but when i run it independently it gives me the appropriate value.

I'm very much opened to a better solution for going about this.

id = customer_name? I expect a number on id and a string on customer_name. So they can't be equal. — Sebastian Brosch
– Sebastian Brosch, Commented Mar 20, 2019 at 15:18
Have you tried using joins instead of subquery?? You should post your tables structure — nacho
– nacho, Commented Mar 20, 2019 at 15:19
could you show me an example of using joins for up to, lets say 4 tables — Devops-Paddy
– Devops-Paddy, Commented Mar 20, 2019 at 15:26
staff, staff_department, customer_name and vendor are all columns of table d — Devops-Paddy
– Devops-Paddy, Commented Mar 20, 2019 at 15:27

Gordon Linoff · Accepted Answer · 2019-03-20 15:49:10Z

3

You should always qualify column names in a query. Presumably, you intend something like this:

SELECT d.`gds_pnr_ref`, d.`transaction_date`,
       (SELECT a.`lastname` FROM `a` WHERE a.`id` = d.`staff` LIMIT 1) as `lastname`,
       (SELECT a.`firstname` FROM `a` WHERE a.`id` = d.`staff` LIMIT 1) as `firstname`,
       (SELECT b.`department_name` FROM `b` WHERE b.`id` = d.`staff_department` LIMIT 1) as `department`,
       (SELECT b.`name` FROM `b` WHERE b.`memo_serial` = ? LIMIT 1) as `pax_name`,
       (SELECT c.`customer_name` FROM `c` WHERE c.`id` = d.`customer_name` LIMIT 1) as `customer`,
       d.travel_product,
       (SELECT c.`vendor_name` FROM `c` WHERE c.`id` = d.`vendor` LIMIT 1) as `vendor` 
FROM `d`
WHERE d.`id` = ?
LIMIT 1;

I have to guess where the columns come from -- so this might not be 100% correct.

Notice that I also replaced the string variables with the ? placeholder. This is a reminder that you should be using parameters for such values.

answered Mar 20, 2019 at 15:49

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Devops-Paddy Over a year ago

okay, thank you. But is it possible that it can be done using the JOIN statement

Gordon Linoff Over a year ago

@Devops-Paddy . . . This answers the question that you asked. Your query is reasonable, so there is not a need to rewrite it once you have fixed the logic problem in the subquery (by using aliases). If you have another question, ask it as a new question.

Devops-Paddy · Accepted Answer · 2019-03-21 11:15:58Z

Thanks guys, but this is the query i finally went with that returns all my needed values with non as null.

SELECT  `a`.`currency`, 
        `a`.`vendor_name`,
        CONCAT(`c`.`lastname`, ' ', `c`.`firstname`) AS `actioned_by`,
        `e`.`department_name` AS `department`,
        `f`.`customer_name` AS `customer`,
        `g`.`currency_name` AS `fl_currency`,
        `b`.`name`,
        `b`.`nuc`,
        `b`.`tax`,
        `b`.`comm` AS `comm_percen`,
        `b`.`comm_tax` AS `comm_tax_value`,
        `b`.`actual_comm`,
        `b`.`service_charge`,
        `b`.`dip`,
        SUM(`b`.`vendor`) AS payable,
        `b`.`charge` AS receivable
FROM ((((((`d`
INNER JOIN `b` ON `d`.`id` = `b`.`memo_serial`)
INNER JOIN `a` ON `d`.`vendor` = `a`.`id`)
INNER JOIN `c` ON `d`.`staff` = `c`.`id`) 
INNER JOIN `e` ON `d`.`staff_department` = `e`.`id`) 
INNER JOIN `f` ON `d`.`customer_name` = `f`.`id`) 
INNER JOIN `g` ON `a`.`currency` = `g`.`id`)  WHERE `d`.`id` = '$some_value'

Using sub queries had some limitations, like when i needed to pull out multiple entries of the a particular foreign key in a particular table. It keeps returning the first row value only. So i ended up using INNER JOIN to pull from 6 different tables to get my results and it's way neater

Collectives™ on Stack Overflow

A column returns null value in SQL statement containing multiple SELECTs

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related