How to Prioritize OR condition in mysql

Question

I have table structure something like this

+---+----------+-----------+--------------+
| id| customer | Address   | Address_type |
+---+----------+-----------+--------------+
|1  | 1        | Address 1 | 2            |
|2  | 2        | Address 2 | 2            |
|3  | 1        | Address 3 | 1            |
+---+----------+-----------+--------------+

There are two Address_types in Database. I have to select Address based on following conditions

1. If Address for costumer of Address_type = 1 is present then display that address.
2. If Address_type = 1 is not present and Address_type = 2 is present then display Address_type = 2 Address for that customer.
3. If both are present for that customer then display only Address where Address_type = 1

I have tried this by OR condition but it displays record whichever is first in Database so there is way in mysql query to achieve this with only one query? i.e. something like giving priority in OR condition to fetch only Address_type = 1 record when both Address_types(1 and 2) are present in Database?

Answer 1

You can use

SELECT 
  yt1.*
FROM 
  your_table yt1 
  LEFT JOIN your_table yt2 ON ( yt2.customer = yt1.customer AND yt2.address_type < yt1.address_type )
WHERE 
  yt2.id IS NULL

Outputs:

| ID | CUSTOMER |   ADDRESS | ADDRESS_TYPE |
-----|----------|-----------|--------------|
|  1 |        1 | Address 1 |            2 |
|  2 |        3 | Address 2 |            2 |

SQLFIDDLE

Answer 2

Another option: get the minimum Address_Type for each customer, then join to that:

SELECT
  id,
  customer,
  Address,
  Address_Type
FROM custs
INNER JOIN (
  SELECT customer, MIN(Address_Type) AS MinType
  FROM custs
  GROUP BY customer
) AddType ON custs.Address_Type = AddType.MinType