PostgreSQL Question posted in
The official documentation can be found here.

In sql Not able get the column data with lookup table Postgresql

I have two tables, Customer table and a lookup table which in customer table has 3 columns and lookup table has 2 columns (which is like vlookup in Excel). Every detailed information is in the lookup table. I want to get detailed information match with customer table.

Customer table:

Custo ID name address
1 1 6
2 4 6
3 4 7
4 3 8
5 2 7

Lookup table:

lk_id lk_value
1 A
2 B
3 C
4 D
5 E
6 x
7 y
8 z

How can I get the output like this:

name address
A x
C z

I tried this query

Select 
    name, address 
from 
    customer, lookup 
where 
    lk_id = name 
    and lk_id = address

But I am getting only first column in where condition, how can I get address column?

Tags:

2

Answers


  1. 0

    You can use two joins, one on the name and one on the address

    SELECT n.lk_value AS name, a.lk_value AS address
FROM   customer c
JOIN   lookup n ON c.name = n.lk_id
JOIN   lookup a ON c.address = a.lk_id
    Login or Signup to reply.
  2. 0

    The issue with your query is that you are not properly handling the two separate joins between the Customer table and the Lookup table. To match both the name and address columns with the Lookup table, you need to join the Lookup table twice: once for the name column and once for the address column.

    Here is the corrected query:

    SELECT
    lookup_name.lk_value AS name,
    lookup_address.lk_value AS address
FROM
    customer
JOIN
    lookup AS lookup_name
    ON customer.name = lookup_name.lk_id
JOIN
    lookup AS lookup_address
    ON customer.address = lookup_address.lk_id;

    Explanation:

    1. Alias usage:

      • lookup_name is an alias for the Lookup table when joining it for the name column.
      • lookup_address is an alias for the Lookup table when joining it for the address column.

    2. Joins:

      • The first JOIN matches the name column from Customer with lk_id in Lookup.
      • The second JOIN matches the address column from Customer with lk_id in Lookup.

    3. Column Selection: The query selects the lk_value from each join to get the desired details for both name and address.

    Output:

    Based on your example data, this query will return:

    name address
    A x
    D x
    D y
    C z
    B y
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search