CODEWITHSUNDEEP
mysqlsqljoin
Louie Miranda
Louie Miranda

Reputation: 1159

MySQL: Query two fields with different lookup id using one lookup table

How can I improve my existing query to display the correct lookup value if the second lookup.id was used already. Would this be better if I use derived tables? sub-queries? Can someone teach me please?

PROBLEM:

RECORDS TYPE    TYPE_DESC   PROCESS_ID  STATUS  QUEUE_DESC
1       1       Queued      55          4       Queued
1       2       Cancelled   84          7       Cancelled

MY GOAL:

RECORDS TYPE    TYPE_DESC   PROCESS_ID  STATUS  QUEUE_DESC
1       1       Initial     55          4       Queued
1       2       Follow Up   84          7       Cancelled

Existing query:

SELECT 
    COUNT(q.id) as records,
    q.type,
    l.description AS type_desc,
    q.process_id,
    q.status,
    l.description AS queue_desc
FROM
    queues q,
    lookups l
WHERE
    l.id = q.status
GROUP BY q.status;

To better understand my problem, please see sqlfiddle entry: http://sqlfiddle.com/#!2/6b7d10/6

Thanks

Upvotes: 1

Views: 561

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

I think this is the query you want:

SELECT COUNT(q.id) as records,
       q.type,
       lt.description AS type_desc,
       q.process_id,
       q.status,
       ls.description AS queue_desc
FROM queues q join
     lookups ls
     on ls.id = q.status and ls.key = 'status' join
     lookups lt
     on lt.id = q.type and lt.key = 'type'
GROUP BY q.status;

Note that this ensures that the key type matches the values for the joins.

Upvotes: 1

shree.pat18
shree.pat18

Reputation: 21757

Try this:

select count(q.id) as records, 
q.type, a.description, q.process_id, q.status, 
b.description as qdesc
from 
queues q
inner join lookups a on q.type = a.id
inner join lookups b on q.status = b.id
group by q.status

You need to join with lookups twice - once to get the type and again to get the status. Note the use of explicit join syntax with ON clause.

Upvotes: 1

AL-
AL-

Reputation: 11

You have to join "lookups" table twice.

SELECT COUNT(q.id)    AS records
      ,q.type
      ,l1.description AS type_desc
      ,q.process_id
      ,q.status
      ,l2.description AS queue_desc
FROM queues  q
    ,lookups l1
    ,lookups l2
WHERE l1.id = q.type
  and l2.id = q.status
GROUP BY q.status;

that's all.

Upvotes: 1

Related Questions