CODEWITHSUNDEEP
mysqlsql
David
David

Reputation: 728

mysql where condition priority

I have the following query and it gets the first result "Edit: of each contact" as I want but I do not need any other results per contact after. So example if they have 3 phone # and one is main, it gives me the main then the other 2. I have tried grouping but it ends up getting the first one pulled no matter if main or not. Any thoughts to how I could do this without doing a php check in the loop for if contact already exists?

SELECT pc.firstname, pc.lastname, pp.areacode, pp.prefix, pp.last4
, if(pc.mainphone = pp.phoneid,1,0) as phone_priority
FROM contacts pc
JOIN phone pp ON ( (pp.contid = pc.contid && pp.phoneid = pc.mainphone) || (pp.contid = pc.contid) )
ORDER BY pc.lastname ASC, pc.firstname ASC, phone_priority DESC

Table setup:

contacts (id, firstname, lastname, mainphone)
phone (id, areacode, prefix, last4)

Using MySQL 4.

Upvotes: 0

Views: 2098

Answers (4)

Devart
Devart

Reputation: 121922

Try this variant - 

SELECT t.contid
     , t.firstname
     , t.lastname
     , if(t.phoneid IS NULL, p2.phoneid, t.phoneid) phone
     , if(t.phoneid IS NULL, p2.areacode, t.areacode) areacode
     , if(t.phoneid IS NULL, p2.prefix, t.prefix) prefix
     , if(t.phoneid IS NULL, p2.last4, t.last4) last4

FROM
  (
  SELECT c.contid
       , c.firstname
       , c.lastname
       , c.mainphone
       , p.phoneid
       , p.areacode
       , p.prefix
       , p.last4

  FROM
    contacts c
  LEFT JOIN phone p
    ON c.contid = p.contid AND c.mainphone = p.phoneid) t
LEFT JOIN phone p2
  ON t.contid = p2.contid AND t.mainphone <> p2.phoneid
GROUP BY
  t.contid

Upvotes: 0

Chandu
Chandu

Reputation: 82903

Use a left join:

SELECT pc.firstname, 
    pc.lastname, 
    pp.areacode, 
    pp.prefix, 
    pp.last4 , 
    CASE 
        WHEN pp.mainphone IS NULL THEN 0
        ELSE 1
 END    AS phone_priority 
FROM contacts pc LEFT JOIN phone pp 
    ON pp.contid = pc.contid 
 AND pp.phoneid = pc.mainphone
 ORDER BY pc.lastname ASC, pc.firstname ASC, phone_priority DESC

Upvotes: 0

Ortiga
Ortiga

Reputation: 8814

Use LIMIT 1 at the end of your query

Upvotes: 1

Oswald
Oswald

Reputation: 31647

Use a LIMIT expression to narrow the result set down to the first result. See SELECT Syntax for details.

Upvotes: 1

Related Questions