SQL Duplicates

Hi all,

I'm facing the next problem:

I've got a table like this:

Name Size Total

Martens 200 2
Martens 300 3

What I want is run an SQL script which will give me the following answer:

Martens 200 2
Martens 200 2
Martens 300 3
Martens 300 3
Martens 300 3

It should show the data the number of times of total.

Who can help?

Kind Regards Sander

Sander Martens wrote:
> Hi all,
>
> I'm facing the next problem:
>
> I've got a table like this:
>
> Name Size Total
>
> Martens 200 2
> Martens 300 3
>
> What I want is run an SQL script which will give me the following answer:
>
> Martens 200 2
> Martens 200 2
> Martens 300 3
> Martens 300 3
> Martens 300 3
>
> It should show the data the number of times of total.
>
> Who can help?
>
> Kind Regards Sander
>
>

I don't think you're going to be able to do this just in MySQL. You'll
need to do it in another programming language.



--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

"Sander Martens" <koningsander@msn.com> wrote in message
news:43d4d923$0$12835$ba620dc5@text.nova.planet.nl ...
> It should show the data the number of times of total.

FWIW, I agree with Jerry, I'd handle this in application code, not in SQL.

Regards,
Bill K.

On Mon, 23 Jan 2006 14:24:50 +0100, "Sander Martens" <koningsander@msn.com>
wrote:

>I've got a table like this:
>
>Name Size Total
>
>Martens 200 2
>Martens 300 3
>
>What I want is run an SQL script which will give me the following answer:
>
>Martens 200 2
>Martens 200 2
>Martens 300 3
>Martens 300 3
>Martens 300 3
>
>It should show the data the number of times of total.

mysql> create table sander (name varchar(10), size int, total int);
Query OK, 0 rows affected (0.15 sec)

mysql> create table rowsource (n int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into sander values ('Martens', 200, 2);
Query OK, 1 row affected (0.01 sec)

mysql> insert into sander values ('Martens', 300, 3);
Query OK, 1 row affected (0.00 sec)

mysql> insert into rowsource values (1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> select name, size, total
-> from sander
-> join rowsource
-> on (n <= total)
-> order by name, size;
+---------+------+-------+
| name | size | total |
+---------+------+-------+
| Martens | 200 | 2 |
| Martens | 200 | 2 |
| Martens | 300 | 3 |
| Martens | 300 | 3 |
| Martens | 300 | 3 |
+---------+------+-------+
5 rows in set (0.05 sec)

--
Andy Hassall :: andy@andyh.co.uk :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool

Thanks for the code.

In the meantime I've managed to do it with some programming code.

Kind Regards Sander


"Andy Hassall" <andy@andyh.co.uk> schreef in bericht
news:cgqat1ht403cbfogj3ud74skfdvohaqm6u@4ax.com...
> On Mon, 23 Jan 2006 14:24:50 +0100, "Sander Martens"
> <koningsander@msn.com>
> wrote:
>
>>I've got a table like this:
>>
>>Name Size Total
>>
>>Martens 200 2
>>Martens 300 3
>>
>>What I want is run an SQL script which will give me the following answer:
>>
>>Martens 200 2
>>Martens 200 2
>>Martens 300 3
>>Martens 300 3
>>Martens 300 3
>>
>>It should show the data the number of times of total.
>
> mysql> create table sander (name varchar(10), size int, total int);
> Query OK, 0 rows affected (0.15 sec)
>
> mysql> create table rowsource (n int);
> Query OK, 0 rows affected (0.01 sec)
>
> mysql> insert into sander values ('Martens', 200, 2);
> Query OK, 1 row affected (0.01 sec)
>
> mysql> insert into sander values ('Martens', 300, 3);
> Query OK, 1 row affected (0.00 sec)
>
> mysql> insert into rowsource values (1),(2),(3);
> Query OK, 3 rows affected (0.00 sec)
> Records: 3 Duplicates: 0 Warnings: 0
>
> mysql> select name, size, total
> -> from sander
> -> join rowsource
> -> on (n <= total)
> -> order by name, size;
> +---------+------+-------+
> | name | size | total |
> +---------+------+-------+
> | Martens | 200 | 2 |
> | Martens | 200 | 2 |
> | Martens | 300 | 3 |
> | Martens | 300 | 3 |
> | Martens | 300 | 3 |
> +---------+------+-------+
> 5 rows in set (0.05 sec)
>
> --
> Andy Hassall :: andy@andyh.co.uk :: http://www.andyh.co.uk
> http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool