Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite
1

i have two tables in a database. The table clients looks like this:

----------------------------
|id | name | age | gender  |
|---------------------------
|1  | CL1  | 22  |  M      |
|2  | CL2  | 23  |  M      |
|3  | CL3  | 24  |  M      |
|4  | CL4  | 25  |  F      |
|5  | CL5  | 26  |  NA     |
----------------------------

Now i have another table which relates to this client table , please note that the "id" in above Table is not AUTO_INCREMENT and is UNIQUE.

The second table is "images" which contain portfolio images of the clients and looks like this :

 ------------------------------
|id | client_id | url         |
|------------------------------
|1  | 1         | img1_1.jpg  | 
|2  | 1         | img1_2.jpg  | 
|3  | 1         | img1_3.jpg  | 
|4  | 2         | img2_1.jpg  | 
|5  | 2         | img2_2.jpg  |
-------------------------------

What i am basically achieving is that i want to pull all results from the client table which include name age gender etc and the first and one result from the images table which means, that if i Query it will have to show me the imag1_1.jpg from images table if i query for CL1 in clients table. For this i am doing something like this : 

SELECT DISTINCT c.* , i.* FROM clients c LEFT JOIN images i ON i.client_id = c.id

This query returns me the results but then the results are more duplicates. I ain't getting or i am either confused for WHAT THE DISTINCT stands for then if it still returns the duplicates, or may be i am missing something.

Any help regarding would be appreciated.

Best,

Ahsan

share|improve this question
It is only meaningful to talk about the "first" record in the context of an ordering. Tables are not stored in any particular order. Which record do you consider to be "first"? That with the minimal id? – eggyal yesterday
@eggyal , i want to pull out all the records in a page i want all from clients table but only one record from images table as per to the client. – Ahsan yesterday
Yes, but which record from the images table ? – eggyal yesterday
The work is going to be done something like this : I am trying to display a record of all the users on one page, which means each client with one of their photo out of every photo they uploaded in the images table. The image shall be unique, because there are multiple images related to one user so i want to select one image per user. – Ahsan yesterday

3 Answers

up vote 1 down vote accepted

Assuming that by "first" you mean the record with the minimal images.id, you are after the groupwise minimum:

SELECT * FROM images NATURAL JOIN (
  SELECT   client_id, MIN(id) id
  FROM     images
  GROUP BY client_id
) t JOIN clients ON clients.id = images.client_id
share|improve this answer
Well, this worked wonder! – Ahsan yesterday
up vote 1 down vote

SELECT DISTINCT operates on a ROW basis. It checks all values in a row against all other rows. If even one value is different, then the row is not a duplicate and the whole thing will be output. If you want to force a single FIELD to be distinct, then you should GROUP BY that field instead.

Since you're doing a left join, you'll get all records from the clients table, and ANY matching records from the images table.

share|improve this answer
thank you for the explanation! I got my concept more clear against the DISTINCT! – Ahsan yesterday
up vote 2 down vote

Here's one way to do it, using a correlated subquery:

SELECT c.*
     , ( SELECT i.url 
           FROM images i 
          WHERE i.client_id = c.id
          ORDER BY i.id
          LIMIT 1
       ) AS url 
  FROM clients c

You don't really need to pull client_id from the images table, you already know it's value. If you need to return the id value from the images table, you'd need to add another correlated subquery in the select list

     , ( SELECT i.id
           FROM images i 
          WHERE i.client_id = c.id
          ORDER BY i.id
          LIMIT 1
       ) AS images_id

This approach can get expensive on large sets, but it performs reasonably for a limited number of rows returned from clients.

A more general query is of the form:

SELECT c.*
     , i.*
  FROM clients c
  LEFT
  JOIN ( SELECT m.client_id, MIN(m.id) as images_id
           FROM images m
          GROUP BY m.client_id
       ) n
  LEFT
  JOIN images i
    ON i.id = n.images_id

The inline view aliased as n will get a single id value from the images table for each client_id, and then we can use that id value to join back to the images table, to retrieve the entire row.

Performance of this form can be better, but with large sets, materializing the inline view aliased as n can take some time. If you have a predicate on the client.id table on the outer query, then for better performance, that predicate can be repeated on m.client_id inside the inline view as well, to limit the number of rows.

share|improve this answer
This worked too, quite informative, thank you.! – Ahsan yesterday

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.