How to make a MYSQL query to include rows once of a column based on another?

Question

Seems to be a simple question for someone who knows well SQL, but not for me.

Anyhow, here is a sample table:

Primary (and only) key is on id. So basically the scenario is as fallows. User may add images. Newly added images are inserted with comune_number value 0 avoiding duplicates (on file name = image via PHP). Images inserted can be assigned to a category from another table. The same image can be assigned to many categories, for each is inserted a new row with the category id (comune_number). Relation between the two tables is on comune_number.

I would like to show the images, with a checkbox checked for which is assigned already.
My question is simple. How to include all images once, but where the same image is already assigned, include that comune_number instead of 0. I don't care about the order. The result I would like to achieve is something like this:
sample result

I'm aware of GROUP BY, so if I try

mysql_query("SELECT * FROM banner WHERE `comune_number` = 0");

or

mysql_query("SELECT * FROM banner GROUP BY `image`");

I end up with the same (not wanted) result.

Most likely I have to combine two queries in one, but I can't figure out which and how.

Note1: I have tried many combinations in phpMyAdmin, based on my (little) knowledge and on what I found with Google (including Stackoverflow), but none of them resulted as shown.

Note2: MySQL version 5.1

Either MYSQL only or combined with PHP solutions are welcome.

EDIT

I need to keep the comune_number. So when I show the images, I need to know the id.
My queries must rely on comune_number, so when I need comune_number = 10, the result should be as on second image above.

EDIT2

It seems I wasn't made myself clear. So what I want, when user is watching category of id 10, show him all the images once, and mark the ones which were assigned to that category.
Another example is here. So if user is watching category (comune_number) of id=9, show every picture once, and mark the two assigned to it.

enter image description here

I don't know what's happened, I can't see the comments. To the last one I read. Yes, it includes one comune_number 0, because that image is not assigned yet to that category of id=10 (category id = comune_number), but have to show it. — Andrew Brown, Jul 22 '13 at 12:24
I would suggest you the following link: http://stackoverflow.com/questions/1425240/select-rows-with-maximum-column-value-group-by-another-column — Piyush Arora, Jul 22 '13 at 12:27
Thank you, but the one you are pointing to, is not what I need. — Andrew Brown, Jul 22 '13 at 12:38
I don't think the question is clear enough: what do you mean by `user is watching category` like in database language. Does that mean when s/he request images in that category? Also, what do you mean by `mark the two assigned to it`? If we have to build an sql query to `mark` something, you'll have to define how that is done. — nt.bas, Jul 22 '13 at 12:47
Sorry for not being clear enough. User is watching category = in browser is shown the page of that category. Every image shown once, with a checkbox next to them. Mark the two assigned = I need the checkbox to be checked next to the images which were assigned to the category they are looking at, in the browser. eg. www.mysite.com?category=9. Look at the pictures. How to achieve those results? — Andrew Brown, Jul 22 '13 at 12:53

Prix · Accepted Answer · 2013-07-22T14:04:50.867

2

Based on your SQLFiddle and all the comments here is the updated query:

   SELECT r.* 
     FROM (SELECT b.* 
             FROM banner b
            WHERE b.comune_number = 9
UNION ALL
           SELECT b2.*
             FROM banner b2
            WHERE b2.comune_number = 0
         GROUP BY b2.image) r
 GROUP BY r.image;

Live DEMO.

edited Jul 22 '13 at 14:04

answered Jul 22 '13 at 12:30

Prix

19,417
15
73
132

This query also, like the one of Joe Taras's, gives every image once. True. But the for the one not assigned should be comune_number=0, instead it's comune_number=9. The comune_number is important, because 0 = not assigned, 9 means that the image was assigned to that category(comune_number). Thank you anyway. – Andrew Brown Jul 22 '13 at 13:09
You are very kind, thought this one applied to my table gives me only comune_number 9, which in the example are 2.. It's not showing the other 2 (comune_number=0). But I found that answer of Praveen works. Thank you anyway. – Andrew Brown Jul 22 '13 at 13:16
Hmm.. Your edited answer, applied to my table, indeed, same as Praveen, gives me the desired result. Applied to your table, his answer is not giving the right result.. Strange. Now I will make some more tests, as I have to figure out the downside of each, in different scenarios, though it should work.. Thank you again. – Andrew Brown Jul 22 '13 at 13:23
@AndrewBrown so just want to make sure, you want to return to the category (comune_number) you select more all other images that have the category 0 (comune_number) is that correct ? – Prix Jul 22 '13 at 13:31
Interesting that your table, fiddle, and query are perfect. Applied to mine, now, not giving what I need. Anyway. I want to show every image once, and where applicable, change the 0 to the right number (eg. 9), much like in your fiddle. Again, applied to mine, not giving the good result. Let me try to make a fiddle myself with my data (not sensitive data..) – Andrew Brown Jul 22 '13 at 13:35
Here is my data.. And as this query, it includes the two images assigned twice, once with 0, and once with 9, whereas I need only once those two, with 9, and the rest with 0. Of course, other images and comune_number's will be added. http://sqlfiddle.com/#!8/8cab9/1 – Andrew Brown Jul 22 '13 at 13:46
Your last query returns only those of coumne_number 0.. Now you may understand why I couldn't figure it out all by myself.. :) Thanks. – Andrew Brown Jul 22 '13 at 13:54
YUP! :) It works as expected.. Believe me or not, Praveen's answer is keep working as well, both in fiddle and real table.. just like your's.. :) Problem solved. It would be nice if I could accept both answers.. A BIG THANK YOU for your spent time and interest. – Andrew Brown Jul 22 '13 at 14:08
@AndrewBrown no problem I like this sort of challenges it's good for improving. – Prix Jul 22 '13 at 14:19

Praveen Prasannan · Answer 2 · 2013-07-22T13:01:18.217

1

select * from 
(SELECT * FROM banner order by 
FIELD(comune_number,10,0))
abc  group by `image`;

fiddle

Updated:

Query with filter condition:

select * from 
(SELECT * FROM banner
where comune_number in (10,0) order by 
FIELD(comune_number,10,0))
abc  group by `image`;

edited Jul 22 '13 at 13:01

answered Jul 22 '13 at 12:30

Praveen Prasannan

7,093
10
50
70

In mysql every derived result set needs, its own alias. – Praveen Prasannan Jul 22 '13 at 12:51
I didn't noticed the banners word. My table actually called banner (without the s). Anyway, this query gives every image once, but the comune_number is not correct. Two of them 10, and two of them is 9. The same image, as I said, can be assigned to different comune_number, in which case 0, 9 and 10 are not the only ones. It is just for the sake of the example. I need the results shown in the pictures. Thank you. – Andrew Brown Jul 22 '13 at 12:59
that you can filter using a where.. Updated the answer. – Praveen Prasannan Jul 22 '13 at 12:59
FIELD(comune_number,10,0) is to do a custom sorting to get the record with 10 when group by applied. – Praveen Prasannan Jul 22 '13 at 13:02
Huh?! :) This one works as expected! Thank you.. I am keep testing, but it seems it's ok. Thank you very much. I couldn't figure it out by myself. :) – Andrew Brown Jul 22 '13 at 13:13
I accepted Prix's answer, though yours working as well. Thank you very much. Appreciate it. Upvoted. – Andrew Brown Jul 22 '13 at 14:19

Joe Taras · Answer 3 · 2013-07-22T13:04:42.123

0

an important tip. When you use GROUP BY all the field you put in your field list, must be in GRUOP BY clause or must be into an aggragate function. MySql has a strange behaviour, don't sign the error but if you try in another DBMS your query:

SELECT * FROM banner GROUP BY image

You have an error.

MySql applies for fields not present in group by an implicit limit 1.

Solution about your issue:

SELECT * FROM banner b1
WHERE b1.comune_number <= 'YOUR_CATEGORY'
    AND NOT EXISTS
    (SELECT 'X' FROM banner b2
    where b2.image = b1.image
    and b2.comune_number > b1.comune_number
    AND b2.comune_number <= 'YOUR_CATEGORY')

EDIT I've changed query, now I put a condition about input category. Where you find YOUR_CATEGORY put the value of category you see (i.e. 9).

edited Jul 22 '13 at 13:04

answered Jul 22 '13 at 12:23

Joe Taras

15,166
7
42
55

EXISTS function return a boolean (true or false). Syntax of SELECT wants at least a field. I use as field a simple string ('X'). I hope this not cause the down vote. I try, in this way, is more fast instead to use a field of the table interested. – Joe Taras Jul 22 '13 at 12:32
I didn't downvoted either. Thank you for pointing out the difference. Your answer is almost there. It shows every image once, right, but for the one not assigned, instead of comune_number 0, it shows 9. Tried to change the last part (> b1.comune_number) to = 10, but then it excluded all comune_number 10. – Andrew Brown Jul 22 '13 at 12:46
Ok, now I try to change so it's compliant for your issue ;) – Joe Taras Jul 22 '13 at 12:50
In this form it gives me an error in the statement. If I put "AND" after the first 'YOUR_CATEGORY', the result is exactly as before. Thank you for taking your time. – Andrew Brown Jul 22 '13 at 13:03
Hi, I fixed my query, I've forgotten AND clause after YOUR_CATEGORY – Joe Taras Jul 22 '13 at 13:05
Tried the updated one. Same result. In the result, the one is not assigned, instead of comune_number 0, is 9. – Andrew Brown Jul 22 '13 at 13:19
Sorry, I don't understand, you want show 9 if only 0 is value (when selected category is 9)? – Joe Taras Jul 22 '13 at 13:24
Please take a look at what I said to Prix. – Andrew Brown Jul 22 '13 at 13:37

score -1 · Answer 4 · answered Jul 22 '13 at 13:14

I am not sure whether this is the exactly you need, but I think it can be helpful.Try this:

Select if(comune_number = 10, comune_number,0), id, image from table

You may change comune_number as per your convenience. Further nesting in IF condition is also possible.

How to make a MYSQL query to include rows once of a column based on another?

4 Answers4