CODEWITHSUNDEEP
mysqlselect
Daniel Berthiaume
Daniel Berthiaume

Reputation: 100

Complex search SQL Query

I need your help with a sql query.

I need the select value in a table where the mathing element has the smalest order, Currently I have the following Query :

SELECT p.confirmation,
  p.lname,
  p.fname,
  p.profiletype,
  a.address1,
  a.city,
  c.contactinfo
FROM profile p
LEFT JOIN contact c
  ON p.confirmation = c.profileid
  AND c.ord = 1
LEFT JOIN address a
  ON p.confirmation = a.profileid
  AND a.ord =1

This work well when the smalest "a.ord" or c.ord" is 1. But the smallest value will not always be one, it may be any other digit, so I've tried the following with no succes : 

SELECT p.confirmation,
  p.lname,
  p.fname,
  p.profiletype,
  a.address1,
  a.city,
  c.contactinfo
FROM profile p
LEFT JOIN contact c
  ON p.confirmation = c.profileid
  min(c.ord)
LEFT JOIN address a
  ON p.confirmation = a.profileid
  min(a.ord)

I've replace "AND a.ord =1" by "min(a.ord)" with no success..

How could I do so wihtout making two seperate query.

Is there a way I can add something in the sort of 

SELECT c.ord FROM contact c WHERE p.confirmation = c.profileid ORDER BY c.ord LIMIT 1

inside the current SQL?

thank you!

Upvotes: 0

Views: 85

Answers (5)

Daniel Berthiaume
Daniel Berthiaume

Reputation: 100

Update:

I've finalyl solve my problem with nested SELECT : 

SELECT p.confirmation,
        p.lname,
        p.fname,
        p.profiletype,
        a.address1,
        a.city,
        c.contactinfo
    FROM profile p
    LEFT JOIN contact c
        ON p.confirmation = c.profileid
        AND c.ord=(SELECT ord FROM contact WHERE profileid= p.confirmation ORDER BY ord LIMIT 1)
    LEFT JOIN address a
        ON p.confirmation = a.profileid
        AND a.ord=(SELECT ord FROM address WHERE profileid= p.confirmation ORDER BY ord LIMIT 1)

Thank you to #Gordon Linoff for the insight on using the multiple select in one query!

Upvotes: 0

mon4goos
mon4goos

Reputation: 1589

Something like this may be what you're looking for. You can change ORDER BY c.ord ASC, a.ord ASC to ORDER BY a.ord ASC, c.ord ASC depending on the precedence you want.

SELECT p.confirmation,
    p.lname,
    p.fname,
    p.profiletype,
    a.address1,
    a.city,
    c.contactinfo
FROM profile p
LEFT JOIN contact c
    ON p.confirmation = c.profileid
LEFT JOIN address a
    ON p.confirmation = a.profileid
ORDER BY c.ord ASC, a.ord ASC
LIMIT 1

Upvotes: 0

Carlos A. Ibarra
Carlos A. Ibarra

Reputation: 6122

Just use LIMIT 1 in MySQL to get the first row after ordering:

SELECT p.confirmation,
  p.lname,
  p.fname,
  p.profiletype,
  a.address1,
  a.city,
  c.contactinfo
FROM profile p
LEFT JOIN contact c
  ON p.confirmation = c.profileid
LEFT JOIN address a
  ON p.confirmation = a.profileid
ORDER BY c.ord, a.ord
LIMIT 1

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269493

You can do it by calculating the min ord for each of the tables and then using that in the join:

SELECT p.confirmation, p.lname, p.fname, p.profiletype,
       a.address1, a.city, c.contactinfo
FROM profile p LEFT JOIN
     (select c.profileid, MIN(c.ord) as minord
      from contact c
      group by c.profileid
     ) minco
     on minco.profileid = c.profileid left join
     contact c
     ON p.confirmation = c.profileid and minco.minord = c.ord LEFT JOIN
     (select a.profileid, min(a.ord) as minord
      from address a
      group by a.profileid
     ) minao
     on minao.profileid = c.profileid left join
     address a
     ON p.confirmation = a.profileid and minao.minord = a.ord

Upvotes: 1

Dominic
Dominic

Reputation: 21

  AND a.ord =1 // if a.cord is always 1
  AND a.ord <=1 // if a.cord is always <1 (eg. .9 or .75) 
  // or just leave it.. i dont know your script, but is it needed?
  // same at c.cord!
  // but rather use where! see below

And as an example:

SELECT 
    p.confirmation, 
    p.lname, 
    p.fname, 
    p.profiletype, 
    a.address1, 
    a.city, 
    c.contactinfo 
FROM  
    profile AS p 
LEFT JOIN 
    contact AS c 
ON 
    p.confirmation=c.profileid 
LEFT JOIN 
    address AS a 
ON 
    p.confirmation=a.profileid 
WHERE 
    c.ord=1 AND a.ord=1 
ORDER BY 
    c.ord ASC, a.ord ASC

Hope i got your problem right

Upvotes: 0

Related Questions