SQL query, retrieve multiple values from column with different IDs

Question

I've searched for this answer, there are a lot of "similar" issues but not exactly the same thing. I want to pull the value from a column, but it matches different ids.

I want both values in the same row after the query runs.

Example table:

+--field_id--+--ticket_id--+--value--+
+     16     +    423      +   PO123 +
+     16     +    344      +   PO101 +
+     7      +    423      +   17    +
+     7      +    344      +   24    +
+------------------------------------+

The main variable here is the ticket_id, however I want the value of field_id = 16 and field_id = 7 to show up on my query on the same row, so as a result I want:

+--ticket--+--PO#--+--Est Hours--+
+   423    + PO123 +    17       +
+   344    + PO101 +    24       +
+--------------------------------+

Currently I have a select statement that includes "value", but it only pulls the information from field_id = 16, I can't get the value from field_id = 7 into the same row.

Please assist. I apologize if this is a duplicate and I just didn't spot it or understand the other answers I've seen where people had problems "similar" but they didn't seem exactly the same. *Note: I cannot change the table layout I'm retrieving data from in any way to make this work better.

Denis O. · Accepted Answer · 2019-09-29 01:44:50Z

1

You simply need to join the table to itself by ticket_id AND field_id conditions at the same time

SELECT
    P.ticket_id AS ticket, P.value AS PONum, H.value AS EstHours
FROM
              Table P
    LEFT JOIN Table H ON P.ticket_id = H.ticket_id AND H.field_id = 7
WHERE
    P.field_id = 16

edited Sep 29, 2019 at 1:44

answered Sep 29, 2019 at 1:39

Denis O.

1,85020 silver badges40 bronze badges

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

1 Comment

dcary Over a year ago

Thank you, this worked. I guess I forgot to mention, I have 3 other joins so I just added this in and it worked. I just needed this small piece really.

Gordon Linoff · Accepted Answer · 2019-09-29 01:32:12Z

0

Use conditional aggregation:

select ticket_id,
       max(case when field_id = 16 then value end) as po,
       max(case when field_id = 7 then value end) as est_hours
from t
group by ticket_id;

answered Sep 29, 2019 at 1:32

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

2 Comments

dcary Over a year ago

Thank you for your response. I tried this however it didn't work, though that might have been because I didn't disclose the WHOLE project. I have multiple other joins and another join worked just fine. When I tried this one, I only retrieved one row and it didn't have the correct est_hours.

Gordon Linoff Over a year ago

@dcary . . . This answers the question that you asked. It really isn't possible to read your mind to answer a different question. You are doing something wrong if this returns only one row when you have multiple ticket_ids.

Collectives™ on Stack Overflow

SQL query, retrieve multiple values from column with different IDs

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related