Subquery returned more than 1 value from look up table

Question

I am having this error on my query. I have a look up table that stores the value of gender, marital status and customer type. But I want to display all in 1 grid view in asp. It is work if there is only 1 record on the database, but when I save another record the error appears.Can someone suggest a fix?

Here is my query:

SELECT
  customer.customer_id
  ,customer.first_name
 ,customer.last_name
 ,customer.birth_date
 ,customer.phone
 ,customer.email
 ,customer.block
 ,customer.lot
 ,customer.status
 ,customer.is_deleted
 ,(SELECT 
 lookup_table.value
FROM dbo.lookup_description
INNER JOIN dbo.lookup_table
  ON lookup_description.desc_id = lookup_table.group_id
INNER JOIN dbo.customer ON customer.gender = lookup_table.lookup_id) AS Gender
 ,(SELECT 
 lookup_table.value
FROM dbo.lookup_description
INNER JOIN dbo.lookup_table
  ON lookup_description.desc_id = lookup_table.group_id
INNER JOIN dbo.customer ON customer.marital_status = lookup_table.lookup_id) AS MaritalStatus
 ,(SELECT 
 lookup_table.value
FROM dbo.lookup_description
INNER JOIN dbo.lookup_table
  ON lookup_description.desc_id = lookup_table.group_id
INNER JOIN dbo.customer ON customer.village = lookup_table.lookup_id) AS Village
 ,(SELECT 
 lookup_table.value
FROM dbo.lookup_description
INNER JOIN dbo.lookup_table
  ON lookup_description.desc_id = lookup_table.group_id
INNER JOIN dbo.customer ON customer.customer_type = lookup_table.lookup_id) AS CustomerType
FROM dbo.customer

Thanks in advance!

Answer 1

It makes sense that you have this error. With one record all is fine, but your subquery have more values with more records and the ngine doesnt know what to choose. Because you are not filtering at all, you want all customer with their characteritics (gender, village...) You should do something like this i beleive:

SELECT
  customer.customer_id
  ,customer.first_name
 ,customer.last_name
 ,customer.birth_date
 ,customer.phone
 ,customer.email
 ,customer.block
 ,customer.lot
 ,customer.status
 ,customer.is_deleted
 ,Gender
 ,marital_status
 ,village
 ,customer_type

FROM dbo.customer a
inner join
 (SELECT 
 lookup_table.value as Gender, lookup_id
FROM dbo.lookup_description
INNER JOIN dbo.lookup_table
  ON lookup_description.desc_id = lookup_table.group_id
 ) AS Gender on  a.gender = Gender.lookup_id

inner join
 (SELECT 
 lookup_table.value as marital_status, lookup_id
FROM dbo.lookup_description
INNER JOIN dbo.lookup_table
  ON lookup_description.desc_id = lookup_table.group_id
 ) AS marital_status on  a.marital_status = marital_status.lookup_id

inner join
 (SELECT 
 lookup_table.value as village, lookup_id
FROM dbo.lookup_description
INNER JOIN dbo.lookup_table
  ON lookup_description.desc_id = lookup_table.group_id
 ) AS village on  a.village = village.lookup_id

inner join
 (SELECT 
 lookup_table.value as customer_type, lookup_id
FROM dbo.lookup_description
INNER JOIN dbo.lookup_table
  ON lookup_description.desc_id = lookup_table.group_id
 ) AS customer_type on  a.customer_type = customer_type.lookup_id