RE: Is this possible?

[m.pheasant]

You would need an aggregate concat() function I think its in 5.0
m

-Original Message-
From: Chris Boget [mailto:[EMAIL PROTECTED] 
Sent: Thursday, April 01, 2004 7:08 AM
To: MySQL
Subject: Is this possible?

I have 2 tables (looks best in fixed width font):

test_items
+---+--+
| name  | rec_num  |
+---+--+
| Book  | 1|
| Game  | 2|
+---+--+

test_attributes
+---++
| item_rec_num  | attribute  |
+---++
| 1 | Thick  |
| 1 | Tall   |
| 1 | Green  |
| 2 | Narrow |
| 2 | Yellow |
+---++

How can I query the above tables so that if I select name and
attribute, the result set comes out looking like this:

+---+---+
| name  | attribute |
+---+---+
| Book  | Thick/Tall/Green  |
| Game  | Narrow/Yellow |
+---+---+

Because every join query I've been able to think of always
returns the result set as follows:

+--+---+
| name | attribute |
+--+---+
| Book | Thick |
| Book | Tall  |
| Book | Green |
| Game | Narrow|
| Game | Yellow|
+--+---+

So my question, is it even possible to do what I'd like to do?

thnx,
Chris


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: group by & order by rand() problem

[m.pheasant]

Order by is working after the group stage. 
You would need an aggregate function which chooses a random row. 
Some other SQL implementations would not let you select a column that is not
also grouped (eg title) or in an aggregate function as in your select ...
group by ... example.

m


-Original Message-
From: Dathan Vance Pattishall [mailto:[EMAIL PROTECTED] 
Sent: Thursday, April 01, 2004 7:16 AM
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: RE: group by & order by rand() problem

Try seeding your rand.

> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, March 31, 2004 12:57 PM
> To: [EMAIL PROTECTED]
> Subject: group by & order by rand() problem
> 
> I have this table:
> 
> mysql> select * from banners;
> ++-+---+
> | id | user_id | title |
> ++-+---+
> |  1 |   1 | first banner  |
> |  2 |   1 | second banner |
> |  3 |   2 | third banner  |
> |  4 |   2 | forth banner  |
> |  5 |   2 | fifth banner  |
> ++-+---+
> 
> I would like to show a random banner for each user,
> something like this:
> 
> first call
> ++-+---+
> | id | user_id | title |
> ++-+---+
> |  1 |   1 | first banner  |
> |  3 |   2 | third banner  |
> ++-+---+
> 
> second call
> ++-+---+
> | id | user_id | title |
> ++-+---+
> |  2 |   1 | second banner |
> |  4 |   2 | forth banner  |
> ++-+---+
> 
> etc...
> 
> I have tried with following query but the banner
> doesn't change while multiple calls:
> 
> SELECT * FROM banners GROUP BY user_id ORDER BY RAND();
> 
> Can anyone help me?
> 
> Thanks in advance,
> 
> Alex
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]