• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 631
  • Last Modified:

MySQL / SQL syntax - using DISTINCT - not working as expected.

Hi,

I'm sure I have acheived this before but seem to be having problems recreating.

I want to display a list of "suppliers". A supplier can have more then one email address and email addresses are stored in a different table (email__addresses).

I want to display a list of suppliers on a page and I want that list of suppliers to display an just one email address for each supplier (does not matter which, just display one).

Of course when I do the query using a LEFT JOIN to link the tables it gives me my results, but it a supplier has more then one email address it is listed more then once.

Now I thought I had come across this problem before and simply used the
SELECT DISTINCT(suppliers.supplier_id) to make sure that at most each supplier isonly displayed once.

but when I use the code as shown in the code window it still displays a supplier multiple times if that supplier has more then one email address.

What am I missing?
Thanks


SELECT DISTINCT(suppliers.supplier_id), suppliers.*, email_addresses.email_address  FROM suppliers 
LEFT JOIN email_addresses ON (supplier_id = emails_supplierid )

Open in new window

0
afflik1923
Asked:
afflik1923
  • 2
  • 2
2 Solutions
 
dportasCommented:
DISTINCT returns unique ROWS. DISTINCT will not pick one row from the joined table, it just eliminates duplicate rows from the end result. The brackets in your query aren't needed and do nothing.

To help us answer your question with a new query please tell us what is/are the key(s) of the email_addresses table?
0
 
racekCommented:
SELECT s.supplier_id, s.xx, s.yyy,
            MIN(ea.email_address) email_address
 FROM suppliers s
LEFT JOIN email_addresses ea
  ON  supplier_id = emails_supplierid
GROUP BY s.supplier_id;
0
 
racekCommented:
SELECT s.*, x.email_address
 FROM suppliers s
LEFT JOIN
   (SELECT emails_supplierid, MIN(email_address) email_address
     FROM  email_addresses GROUP BY 1 ) as  x
  ON  s.supplier_id = x.emails_supplierid ;
0
 
afflik1923Author Commented:
Ahh. I think I was getting myself in a muddle. Seeing the code there I think it was GROUP BY I also used. I will try and report back.
Thanks
0
 
afflik1923Author Commented:
Yes of course. It was the group by Supplier_id that done the trick. I was being a bit silly there.

Thanks
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now