1

I believe this is a pretty simple thing, and I swear I've done it before but I can't remember how.

So let's say I have a one-to-many relationship. I want to JOIN the two tables, but not allow duplicates for the left table.

SQLFIDDLE

So based on the above SQLFiddle, my results would be:

categories.title  |  items.NAME  |  items.category_id
-----------------------------------------------------
red               | apple        | 1
red               | car          | 1
red               | paper        | 1
yellow            | lego         | 2
yellow            | banana       | 2
blue              | pen          | 3

I want it to be: 

categories.title  |  items.NAME  |  items.category_id
-----------------------------------------------------
red               | apple        | 1
NULL              | car          | 1
NULL              | paper        | 1
yellow            | lego         | 2
NULL              | banana       | 2
blue              | pen          | 3

My reasoning is that this way, I can easily loop over the results without having to do any further processing with PHP.

scootstah
  • 193
  • 2
  • 11
  • 2
    And why can't you loop when the title has duplicates? – ypercubeᵀᴹ Jan 11 '13 at 20:51
  • 1
    I would echo the comment of @ypercube Having the value repeated would have not being on your ability to loop over the result set. In fact, if you are trying to place this into a 2D array, it may actually hamper your efforts. You may also want to consider a sort in your query. – Mike Brant Jan 11 '13 at 20:56
  • 2
    I agree; this question is asking to solve a problem which doesn't exist. – Gavin Towey Jan 11 '13 at 21:12
  • 1
    A solution like the one provided by bluefeet can certainly return the specified result set. But a query like that is going to be less efficient (on the MySQL side) due to the inline view, and you are still going to need to process those rows on the client side either way. It will be more efficient to do the processing on the client side (i.e. compare the value of `title` from the current row to the value from the previous row.) – spencer7593 Jan 11 '13 at 21:20
  • Exactly as @spencer7593 said. You only need to add `order by category_id, ...` in the query so rows with same category are one after the other. – ypercubeᵀᴹ Jan 11 '13 at 21:24
  • Hmm, for some reason I could have sworn it was just a simple little thing which would let me cheat a little. Guess not! – scootstah Jan 12 '13 at 01:20

2 Answers2

4

You can replace the values with something like this:

select 
  case when rownum = 1 then title else null end title,
  name,
  category_id
from
(
  SELECT c.title, 
    i.name, 
    i.category_id,
    @row:=(case when @prev=title and @precat=category_id 
           then @row else 0 end) + 1 as rownum,
    @prev:=title ptitle,
    @precat:=category_id pcat
  FROM items AS i
  INNER JOIN categories AS c 
    ON c.id = i.category_id
   order by i.category_id, c.title
) src
order by category_id, rownum

See SQL Fiddle with Demo

The result is:

|  TITLE |   NAME | CATEGORY_ID |
---------------------------------
|    red |  apple |           1 |
| (null) |    car |           1 |
| (null) |  paper |           1 |
| yellow |   lego |           2 |
| (null) | banana |           2 |
|   blue |    pen |           3 |
Taryn
  • 242,637
  • 56
  • 362
  • 405
  • There's still a corner case. Consider what happens when the `@prev` user variable is previously initialized in the session, and it happens to match the first `c.title` returned by the view query. Best practice would dictate that we initialize that variable, which can be done conveniently by adding a `CROSS JOIN (SELECT @prev := NULL) v ` to the inline view query. – spencer7593 Jan 11 '13 at 21:30
0

It might be a long time ago when this was post. But I'll post my answer to the future readers. There is another process that is light and quick to understand.

You can make good use of variables. No subqueries are necessary.

SET @previous:="";

SELECT
  IF(C.title=@previous, "", @previous:=C.title) AS Titles,
  I.name, I.category_id
FROM items I
INNER JOIN categories AS C ON C.id = I.category_id
ORDER BY I.id, I.name

@previous is the variable that is being used.

SQL FIDDLE DEMO

kiLLua
  • 443
  • 1
  • 6
  • 17