CODEWITHSUNDEEP
mysqlsql
dcary
dcary

Reputation: 69

SQL query, retrieve multiple values from column with different IDs

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.

Upvotes: 1

Views: 87

Answers (2)

Denis O.
Denis O.

Reputation: 1850

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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269773

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;

Upvotes: 0

Related Questions