question

francois.labbe avatar image
francois.labbe asked

In my query I need the result from one table but with two id

In the table 'organization' I have the name of the organizations (organization.name).

In the table 'client_order' I have an ID for the client (client_order.client_id) and for the workshop working on the client order (client_order.opeq_id).

How can I get both organization names in my query?

This is my query:

SELECT 
organization.name as Client, client_order.opeq_id as Atelier_OPEQ,
client_order.ordernumber as Commande_OPEQ,
client_order.alternateorderreferencenumber as Commande_client,
client_order.rma as RMA,
client_order.orderstatus as Etat_de_la_commande,
client_order.datecreated as Date_de_la_demande,
client_order.shipmentdate as Date_de_livraison,
product.description as Produit,
client_order.project_id as Projet FROM
client_order
left join organization on organization.id = client_order.client_id left join client_order_product on client_order_product.clientorder_id = client_order.id left join product on client_order_product.product_id = product.id
where
client_order.datecreated > '2018-10-01' AND organization.cfs_group_id =1

With this query I get the name of the client and the ID number for client_order.opeq_id, I wiish to replace the ID number of client_order.opeq_id with the name of the workshop in 'organization.name'.

query-resultsjoin
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Jon Crawford avatar image
Jon Crawford answered

LEFT JOIN another organization table, alias it something (workshopOrg or something) and join it on the client_order.opeq_id. Then just pull it from there in your SELECT

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

francois.labbe avatar image
francois.labbe answered

@Jon Crawford

I tried this:

SELECT

organization.name as Client,
workshop.name as Atelier_OPEQ,
client_order.ordernumber as Commande_OPEQ,
client_order.alternateorderreferencenumber as Commande_client, client_order.rma as RMA, client_order.orderstatus as Etat_de_la_commande,
client_order.datecreated as Date_de_la_demande,
client_order.shipmentdate as Date_de_livraison,
product.description as Produit,
client_order.project_id as Projet

FROM

client_order, organization AS workshop
left join organization on organization.id = client_order.client_id
left join workshop on organization.id = client_order.opeq_id
left join client_order_product on client_order_product.clientorder_id = client_order.id left join product on client_order_product.product_id = product.id

where

client_order.datecreated > '2018-10-01' AND organization.cfs_group_id =1

I receive this error:

SQL error:

ERROR:  invalid reference to FROM-clause entry for table "client_order"
LINE 13: left join organization on organization.id = client_order.cli...
                                                     ^
HINT:  There is an entry for table "client_order", but it cannot be referenced from this part of the query.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.