Sql select

[kadriirdak]

Hi There,
I am using a sql query to show a row to users.
The Problem is in the database there are single as well as duplicate records.
My 1st code is – Works good if user has no duplicate records;

SELECT 
contacts.forenames, contacts.surname, addresses.postcode, contacts.contact_number
FROM
contacts
LEFT JOIN addresses
ON contacts.address_number=addresses.address_number
WHERE
contacts.surname='$sn' AND contacts.forenames='$fn' AND addresses.postcode='$ad'

My 2nd code is – Works good if user has duplicate records;

SELECT 
contacts.forenames, contacts.surname, communications.number, contacts.contact_number
FROM
contacts 
LEFT JOIN communications
ON contacts.contact_number=communications.contact_number

WHERE
  contacts.surname='$sn' AND contacts.forenames='$fn' AND communications.number='$em' 
AND contacts.contact_number  = (SELECT MAX(contacts.contact_number)  FROM contacts)

But I need to use these in one – something like that => if user is single record use this

WHERE
  contacts.surname='$sn' AND contacts.forenames='$fn' AND communications.number='$em'

Or use this (to print highest number)

WHERE
  contacts.surname='$sn' AND contacts.forenames='$fn' AND communications.number='$em' 
AND contacts.contact_number  = (SELECT MAX(contacts.contact_number)  FROM contacts)

Any Ideas?

Thank You!

[aquilax]

And what exactly is your question ?

[kadriirdak]

I need to use this WHERE clause like
If user record is more than one use this one ->

WHERE
  contacts.surname='$sn' AND contacts.forenames='$fn' AND communications.number='$em'  AND contacts.contact_number  = (SELECT MAX(contacts.contact_number)  FROM contacts)

Or if user is a single record in database use this one ->

WHERE
  contacts.surname='$sn' AND contacts.forenames='$fn' AND communications.number='$em'

Because when I use the above query it works but does not give result for single record - donno why..

[aquilax]

Try this query:

SELECT 
contacts.forenames, contacts.surname, communications.number, MAX(contacts.contact_number) AS contact_number 
FROM
contacts 
LEFT JOIN communications ON contacts.contact_number=communications.contact_number
WHERE
contacts.surname='$sn' AND contacts.forenames='$fn' AND communications.number='$em' 
GROUP BY contacts.forenames, contacts.surname, communications.number

It should work in both cases.

[rch1231]

It might be easier to use this sort them with the highest contact number and then just get one record.

SELECT 
contacts.forenames, contacts.surname, communications.number, contacts.contact_number
FROM
contacts 
LEFT JOIN communications
ON contacts.contact_number=communications.contact_number
WHERE
  contacts.surname='$sn' AND contacts.forenames='$fn' AND communications.number='$em' 
ORDER BY contacts.contact_number DESC
LIMIT 1

[kadriirdak]

Strange I did try but does not print with order by , limit or group by.

Is it because I use this sql query through web services?

Anyway Now I am using Select TOP 1 method not good as what needed but works..

Thanks