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!

And what exactly is your question ?

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..

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.

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

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.