You are not logged in.
- Topics: Active | Unanswered
#1 2011-10-21 20:44:21
- madeye
- Member
- From: Denmark
- Registered: 2006-07-19
- Posts: 331
- Website
[SOLVED] mysql limit on joined tables
This might be a little odd, I mean for me to post this here. But I think that the variety of users with archlinux may be just the right crowd to help me with this.
First a little description on what I'm trying to do.
I have two tables. The first contains details on cars, the seconds contains images. There can be more than one image to every car.
Table1: annonce
+-------------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------------+------+-----+---------+----------------+
| id | bigint(20) unsigned | NO | PRI | NULL | auto_increment |
| overskrift | varchar(255) | NO | | NULL | |
| beskrivelse | text | NO | | NULL | |
| oprettet | datetime | YES | | NULL | |
| aktiv | tinyint(3) unsigned | YES | | 1 | |
| pris | bigint(20) | NO | | NULL | |
+-------------+---------------------+------+-----+---------+----------------+Table2: billed
+-----------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------------------+------+-----+---------+----------------+
| image_id | bigint(20) unsigned | NO | PRI | NULL | auto_increment |
| filename | varchar(255) | NO | | NULL | |
| mime_type | varchar(255) | NO | | NULL | |
| file_size | int(11) | NO | | NULL | |
| file_data | longblob | NO | | NULL | |
| car_id | bigint(20) unsigned | NO | | NULL | |
| img_order | tinyint(4) | NO | | NULL | |
+-----------+---------------------+------+-----+---------+----------------+Now I want to put all contents from table1 into a two dimensional array, and also put the id of the first image from table2 in the same array.
This is what I have until now. Needless to say it does not exactly do what I want.
$query = sprintf('SELECT a.*, b.image_id
FROM annonce a
LEFT JOIN billed b
ON a.id = b.car_id
GROUP BY a.id
HAVING COUNT(*) <=1');I've tried it without the "group by" and "having count". Didn't work either.
Am I overlooking something here? (I have a feeling I'm doing something stupid along the way here 
)
Last edited by madeye (2011-10-23 11:45:03)
MadEye | Registered Linux user #167944 since 2000-02-28 | Homepage
Offline
#2 2011-10-21 23:37:16
- firecat53
- Member
- From: Lake Stevens, WA, USA
- Registered: 2007-05-14
- Posts: 1,542
- Website
Re: [SOLVED] mysql limit on joined tables
Hmm, I tried an abbreviated version of what you posted and it worked fine as written, without the 'HAVING COUNT...' clause. Can you post a sample dataset and the output you received, along with the output you would expect to receive?
Scott
Offline
#3 2011-10-21 23:45:54
- falconindy
- Developer
- From: New York, USA
- Registered: 2009-10-22
- Posts: 4,111
- Website
Offline
#4 2011-10-22 10:11:34
- madeye
- Member
- From: Denmark
- Registered: 2006-07-19
- Posts: 331
- Website
Re: [SOLVED] mysql limit on joined tables
I will try to make a little clearer. (Sorry I was almost asleep last night when I wrote the first post 
)
Here is the contents of table 1
mysql> select * from annonce;
+----+---------------------+------------------------+---------------------+-------+-------+
| id | overskrift | beskrivelse | oprettet | aktiv | pris |
+----+---------------------+------------------------+---------------------+-------+-------+
| 1 | test | test | 2011-10-21 12:16:58 | 1 | 556 |
| 2 | Skoda felicia 1.6GL | Min skoda er til salg! | 2011-10-21 13:32:23 | 1 | 24300 |
| 3 | Mazda 323f | dejlig bil til salg | 2011-10-21 15:39:41 | 1 | 44333 |
| 4 | Nissan Primera | testet af den bedste | 2011-10-21 15:46:32 | 1 | 99900 |
| 5 | Nissan Primera 2 | testet af den bedste | 2011-10-21 15:47:04 | 1 | 88922 |
| 6 | Nissan Primera 3 | testet af den bedste | 2011-10-21 15:50:03 | 1 | 88922 |
| 7 | chevrolet camaro | lalalelelolo | 2011-10-21 15:51:57 | 1 | 1 |
| 8 | chevrolet camaro | lalalelelolo | 2011-10-21 15:52:01 | 1 | 1 |
+----+---------------------+------------------------+---------------------+-------+-------+
8 rows in set (0.00 sec)And the contents of table 2 (without the file_data, which is the image itself)
mysql> select image_id,filename,mime_type,file_size,car_id,img_order from billed;
+----------+---------------------------------+------------+-----------+--------+-----------+
| image_id | filename | mime_type | file_size | car_id | img_order |
+----------+---------------------------------+------------+-----------+--------+-----------+
| 1 | avatar.gif | image/gif | 5275 | 1 | 1 |
| 2 | 1-snowsamurai2.jpg | image/jpeg | 208896 | 1 | 1 |
| 3 | Indkøbsvogn let-elektronik.png | image/png | 919534 | 1 | 1 |
| 4 | Screenshot_march_2010_dirty.png | image/png | 1044907 | 1 | 1 |
| 5 | P1000310.jpeg | image/jpeg | 5818242 | 1 | 1 |
| 6 | P1000315.JPG | image/jpeg | 5729566 | 2 | 1 |
| 7 | P1000317.JPG | image/jpeg | 4610784 | 2 | 1 |
| 8 | P1000319.JPG | image/jpeg | 4507224 | 3 | 1 |
+----------+---------------------------------+------------+-----------+--------+-----------+
8 rows in set (0.03 sec)What I want to do is grab all data from table 1(annonce) and for each row grab only one image_id from table 2 (billed).
here is an example: (only two rows listed, but would of course be 8, as there are 8 records in table 1)
+----+---------------------+------------------------+---------------------+-------+-------+----------+
| id | overskrift | beskrivelse | oprettet | aktiv | pris | image_id |
+----+---------------------+------------------------+---------------------+-------+-------+----------+
| 1 | test | test | 2011-10-21 12:16:58 | 1 | 556 | 1 |
| 2 | Skoda felicia 1.6GL | Min skoda er til salg! | 2011-10-21 13:32:23 | 1 | 24300 | 6 |
+----+---------------------+------------------------+---------------------+-------+-------+----------+If I run the query I listed in my post, I get:
mysql> SELECT a.*, b.image_id FROM annonce a LEFT JOIN billed b ON a.id = b.car_id GROUP BY a.id HAVING COUNT(*) <=1;
+----+------------------+----------------------+---------------------+-------+-------+----------+
| id | overskrift | beskrivelse | oprettet | aktiv | pris | image_id |
+----+------------------+----------------------+---------------------+-------+-------+----------+
| 3 | Mazda 323f | dejlig bil til salg | 2011-10-21 15:39:41 | 1 | 44333 | 8 |
| 4 | Nissan Primera | testet af den bedste | 2011-10-21 15:46:32 | 1 | 99900 | NULL |
| 5 | Nissan Primera 2 | testet af den bedste | 2011-10-21 15:47:04 | 1 | 88922 | NULL |
| 6 | Nissan Primera 3 | testet af den bedste | 2011-10-21 15:50:03 | 1 | 88922 | NULL |
| 7 | chevrolet camaro | lalalelelolo | 2011-10-21 15:51:57 | 1 | 1 | NULL |
| 8 | chevrolet camaro | lalalelelolo | 2011-10-21 15:52:01 | 1 | 1 | NULL |
+----+------------------+----------------------+---------------------+-------+-------+----------+
6 rows in set (0.18 sec)Another one I tried gives the following.
mysql> SELECT a.*, b.image_id FROM annonce a LEFT JOIN billed b ON a.id = b.car_id GROUP BY b.car_id HAVING COUNT(b.image_id) >=1;
+----+---------------------+------------------------+---------------------+-------+-------+----------+
| id | overskrift | beskrivelse | oprettet | aktiv | pris | image_id |
+----+---------------------+------------------------+---------------------+-------+-------+----------+
| 1 | test | test | 2011-10-21 12:16:58 | 1 | 556 | 1 |
| 2 | Skoda felicia 1.6GL | Min skoda er til salg! | 2011-10-21 13:32:23 | 1 | 24300 | 6 |
| 3 | Mazda 323f | dejlig bil til salg | 2011-10-21 15:39:41 | 1 | 44333 | 8 |
+----+---------------------+------------------------+---------------------+-------+-------+----------+
3 rows in set (0.17 sec)And if I use only the join I get: (this was actually what I tried first before starting to add GROUP BY and HAVING COUNT)
mysql> SELECT a.*, b.image_id FROM annonce a LEFT JOIN billed b ON a.id = b.car_id;
+----+---------------------+------------------------+---------------------+-------+-------+----------+
| id | overskrift | beskrivelse | oprettet | aktiv | pris | image_id |
+----+---------------------+------------------------+---------------------+-------+-------+----------+
| 1 | test | test | 2011-10-21 12:16:58 | 1 | 556 | 1 |
| 1 | test | test | 2011-10-21 12:16:58 | 1 | 556 | 2 |
| 1 | test | test | 2011-10-21 12:16:58 | 1 | 556 | 3 |
| 1 | test | test | 2011-10-21 12:16:58 | 1 | 556 | 4 |
| 1 | test | test | 2011-10-21 12:16:58 | 1 | 556 | 5 |
| 2 | Skoda felicia 1.6GL | Min skoda er til salg! | 2011-10-21 13:32:23 | 1 | 24300 | 6 |
| 2 | Skoda felicia 1.6GL | Min skoda er til salg! | 2011-10-21 13:32:23 | 1 | 24300 | 7 |
| 3 | Mazda 323f | dejlig bil til salg | 2011-10-21 15:39:41 | 1 | 44333 | 8 |
| 4 | Nissan Primera | testet af den bedste | 2011-10-21 15:46:32 | 1 | 99900 | NULL |
| 5 | Nissan Primera 2 | testet af den bedste | 2011-10-21 15:47:04 | 1 | 88922 | NULL |
| 6 | Nissan Primera 3 | testet af den bedste | 2011-10-21 15:50:03 | 1 | 88922 | NULL |
| 7 | chevrolet camaro | lalalelelolo | 2011-10-21 15:51:57 | 1 | 1 | NULL |
| 8 | chevrolet camaro | lalalelelolo | 2011-10-21 15:52:01 | 1 | 1 | NULL |
+----+---------------------+------------------------+---------------------+-------+-------+----------+
13 rows in set (0.17 sec)As you can see this one gives me all possible rows. 
Hopefully this makes it a little clearer what I attempt to do. 
MadEye | Registered Linux user #167944 since 2000-02-28 | Homepage
Offline
#5 2011-10-22 14:11:44
- falconindy
- Developer
- From: New York, USA
- Registered: 2009-10-22
- Posts: 4,111
- Website
Re: [SOLVED] mysql limit on joined tables
Yes, because that's what a left join does. I still maintain that you don't want a left join at all. An inner join only returns rows where there's a 1:1 match. There will never be NULL padding in column you perform an inner join on (save for poor table design).
SELECT a.* FROM annonce a INNER JOIN billed b ON a.id = b.car_id;Offline
#6 2011-10-22 17:42:09
- firecat53
- Member
- From: Lake Stevens, WA, USA
- Registered: 2007-05-14
- Posts: 1,542
- Website
Re: [SOLVED] mysql limit on joined tables
Isn't this exactly what you want?
SELECT a.*, b.image_id FROM annonce a LEFT JOIN billed b ON a.id = b.car_id GROUP BY a.id;
+------+---------------------+------------------------+---------------------+-------+-------+----------+
| id | overskrift | beskrivelse | oprettet | aktiv | pris | image_id |
+------+---------------------+------------------------+---------------------+-------+-------+----------+
| 1 | test | test | 2011-10-21 12:16:58 | 1 | 556 | 1 |
| 2 | Skoda felicia 1.6GL | Min skoda er til salg! | 2011-10-21 13:32:23 | 1 | 24300 | 6 |
| 3 | Mazda 323f | dejlig bil til salg | 2011-10-21 15:39:41 | 1 | 44333 | 8 |
| 4 | Nissan Primera | testet af den bedste | 2011-10-21 15:46:32 | 1 | 99900 | NULL |
| 5 | Nissan Primera 2 | testet af den bedste | 2011-10-21 15:47:04 | 1 | 88922 | NULL |
| 6 | Nissan Primera 3 | testet af den bedste | 2011-10-21 15:50:03 | 1 | 88922 | NULL |
| 7 | chevrolet camaro | lalalelelolo | 2011-10-21 15:51:57 | 1 | 1 | NULL |
| 8 | chevrolet camaro | lalalelelolo | 2011-10-21 15:52:01 | 1 | 1 | NULL |
+------+---------------------+------------------------+---------------------+-------+-------+----------+
8 rows in set (0.00 sec)This will give you one row for each entry in annonce, regardless of whether there are any matching entries. If, like falconindy says you only want rows with a valid image, then just use join (inner join)
SELECT a.*, b.image_id FROM annonce a JOIN billed b ON a.id = b.car_id GROUP BY a.id;
+------+---------------------+------------------------+---------------------+-------+-------+----------+
| id | overskrift | beskrivelse | oprettet | aktiv | pris | image_id |
+------+---------------------+------------------------+---------------------+-------+-------+----------+
| 1 | test | test | 2011-10-21 12:16:58 | 1 | 556 | 1 |
| 2 | Skoda felicia 1.6GL | Min skoda er til salg! | 2011-10-21 13:32:23 | 1 | 24300 | 6 |
| 3 | Mazda 323f | dejlig bil til salg | 2011-10-21 15:39:41 | 1 | 44333 | 8 |
+------+---------------------+------------------------+---------------------+-------+-------+----------+
3 rows in set (0.00 sec)Scott
edit: It was your HAVING clause that was messing you up. A count >=1 was giving you all cars with 1 or more images available, but not cars with no available images (exactly like using a regular INNER JOIN (aka JOIN). <=1 was giving you cars with either 1 or no images available. You were close In general, I like to try and stay away from count(*) and rather put in a specific column name so there are no issues with counting something you don't want to count.
Last edited by firecat53 (2011-10-22 18:11:00)
Offline
#7 2011-10-23 11:44:44
- madeye
- Member
- From: Denmark
- Registered: 2006-07-19
- Posts: 331
- Website
Re: [SOLVED] mysql limit on joined tables
Thanks. That was exactly what I was looking for. (the first query giving me all 8 rows).
I've been using MySQL on various webpage projects I've been fooling around with, (Nothing special. Just for trying new things out). However I've never tried to use the query to select from more than one table at a time.
I had been reading about joins, but obviously didn't quite understand enough about them to choose the right one to use. Or more correctly how many extra options to apply to the query.
Actually I thought I had tried the solution variant, but it must only have been in my mind
Thanks to everyone helping me along to understand yet another thing. 
MadEye | Registered Linux user #167944 since 2000-02-28 | Homepage
Offline