Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

join 2 tables and limit records from one 1

Status
Not open for further replies.
nego78

nego78

Programmer
Jun 13, 2004
129
PL
helo
i've got 2 tables

Gallery
Gallery_Id,Gallery_Name

Images
Images_Id,Gallery_Id,Images_Filename,Images_Width,Images_Height

I'd like to select all galeries from Gallery and join Images to each gallery but i'd like to limit 3 images per gallery

this code list all images from all galleries, i suppose that i must try with subqueries, am i wrong ?
Code: 
SELECT a.Gallery_Id,a.Gallery_Name,b.Images_Id,b.Images_Filename
FROM Gallery AS a INNER JOIN Images AS b ON b.Gallery_Id=a.Gallery_Id

Via-Net - web directory
 
Sort by date Sort by votes
Code: 
select a.Gallery_Id
     , a.Gallery_Name
     , b1.Images_Id
     , b1.Images_Filename
  from Gallery as a
inner
  join Images as b1
    on a.Gallery_Id = b1.Gallery_Id
inner
  join Images as b2
    on a.Gallery_Id = b2.Gallery_Id
   and b1.Images_Width <= b2.Images_Width
group 
    by a.Gallery_Id
     , a.Gallery_Name
     , b1.Images_Id
     , b1.Images_Filename
having count(*) <= 3    
order 
    by a.Gallery_Name
     , b1.Images_Filename

r937.com | rudy.ca
 
Upvote 0 Downvote
Unfortunately it didn't work as i expected.
What i'm tryng achieve.
I'd like display on one page names of galleries from Gallery and next to each gallery name a 3 or more images from Images.
I'd like to do it in max 2 queries.
Any ideas?
I was experimenting with inner subqueries but i can't imagine has to pass gallery_id to inner query.


Via-Net - web directory
 
Upvote 0 Downvote
It returned exactly three images from one gallery
i paste real code from my db

Here i put galleries
Code: 
CREATE TABLE `md_ero_Galerie` (
  `Galerie_Id` int(10) unsigned NOT NULL auto_increment,
  `Kategorie_Id` int(10) unsigned NOT NULL default '0',
  `Galerie_Katalog` varchar(255) collate utf8_polish_ci NOT NULL default '',
  `Galerie_Nazwa` varchar(255) collate utf8_polish_ci NOT NULL default '',
  `Galerie_Opis` varchar(255) collate utf8_polish_ci default NULL,
  `Galerie_Status` tinyint(3) unsigned NOT NULL default '1',
  `Galerie_DataDodania` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`Galerie_Id`),
  KEY `IX_Kategorie` TYPE BTREE (`Kategorie_Id`,`Galerie_Status`),
  KEY `IX_DataDodania` (`Galerie_DataDodania`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci;

here i put images

Code: 
DROP TABLE IF EXISTS `moje_erotyczne`.`md_ero_Zdjecia`;
CREATE TABLE `md_ero_Zdjecia` (
  `Zdjecia_Id` int(10) unsigned NOT NULL auto_increment,
  `Galerie_Id` int(10) unsigned NOT NULL default '0',
  `Zdjecia_Zdjecie` varchar(255) collate utf8_polish_ci NOT NULL default '',
  `Zdjecia_ZdjecieX` smallint(5) unsigned NOT NULL default '0',
  `Zdjecia_ZdjecieY` smallint(5) unsigned NOT NULL default '0',
  `Zdjecia_ZdjecieSr` varchar(255) collate utf8_polish_ci NOT NULL default '',
  `Zdjecia_ZdjecieSrX` smallint(5) unsigned NOT NULL default '0',
  `Zdjecia_ZdjecieSrY` smallint(5) unsigned NOT NULL default '0',
  `Zdjecia_ZdjecieMa` varchar(255) collate utf8_polish_ci NOT NULL default '',
  `Zdjecia_ZdjecieMaX` smallint(5) unsigned NOT NULL default '0',
  `Zdjecia_ZdjecieMaY` smallint(5) unsigned NOT NULL default '0',
  `Zdjecia_Status` tinyint(3) unsigned NOT NULL default '1',
  PRIMARY KEY  (`Zdjecia_Id`),
  KEY `IX_Galerie` (`Galerie_Id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci;

What i'm doing:
I select galleries id with limit, let's say from 1 to 10 and now i'd like to select for each gallery (from 1 to 10) select images - 3 image per gallery
i don't want to do 10 queries.

r937: could you also check did i put good indexes for this purpose?

Via-Net - web directory
 
Upvote 0 Downvote

Code: 
SELECT
m.Galerie_Id
,m.Galerie_Nazwa
,mZ.Zdjecia_Id
,mZ.Zdjecia_Zdjecie
,mZ.Zdjecia_ZdjecieX
,mZ.Zdjecia_ZdjecieY
 FROM md_ero_Galerie m
INNER JOIN md_ero_Zdjecia mZ ON m.Galerie_Id=mZ.Galerie_Id
INNER JOIN md_ero_Zdjecia mZ1 ON m.Galerie_Id=mZ1.Galerie_Id and mZ.Zdjecia_ZdjecieX <= mZ1.Zdjecia_ZdjecieX
group
    by m.Galerie_Id
     , m.Galerie_Nazwa
     ,mZ.Zdjecia_Id
     ,mZ.Zdjecia_Zdjecie
having count(*) <= 3
order
    by m.Galerie_Nazwa
     , mZ.Zdjecia_Id

Via-Net - web directory
 
Upvote 0 Downvote
that looks fine to me

how did you accomplish this: "I select galleries id with limit"

i don't see anything which limits the galleries to 10

p.s. make your GROUP BY columns agree with your SELECT

r937.com | rudy.ca
 
Upvote 0 Downvote
how did you accomplish this: "I select galleries id with limit"
Click to expand...

i do
Code: 
select count(*) from md_ero_Galerie
than i calculate limit depending from which gallery i'd like to start showing
so for first page it will be
Code: 
SELECT
m.Galerie_Id
,m.Galerie_Nazwa
,mZ.Zdjecia_Id
,mZ.Zdjecia_Zdjecie
,mZ.Zdjecia_ZdjecieX
,mZ.Zdjecia_ZdjecieY
 FROM md_ero_Galerie m
INNER JOIN md_ero_Zdjecia mZ ON m.Galerie_Id=mZ.Galerie_Id
INNER JOIN md_ero_Zdjecia mZ1 ON m.Galerie_Id=mZ1.Galerie_Id and mZ.Zdjecia_ZdjecieX <= mZ1.Zdjecia_ZdjecieX
group
    by m.Galerie_Id
,m.Galerie_Nazwa
,mZ.Zdjecia_Id
,mZ.Zdjecia_Zdjecie
,mZ.Zdjecia_ZdjecieX
,mZ.Zdjecia_ZdjecieY
having count(*) <= 3
order
    by m.Galerie_Nazwa
     , mZ.Zdjecia_Id
limit 0,10
and i receive
Code: 
6, 'gal1', 22, 'loza_d_798550233924401944936.jpg', 915, 600
6, 'gal1', 28, 'loza_d_114108548311825062402.jpg', 915, 600
6, 'gal1', 30, 'loza_d_263282197798258407512.jpg', 915, 600
but i need
Code: 
6, 'gal1', 22, 'loza_d_798550233924401944936.jpg', 915, 600
6, 'gal1', 28, 'loza_d_114108548311825062402.jpg', 915, 600
6, 'gal1', 30, 'loza_d_263282197798258407512.jpg', 915, 600
7, 'gal2', 42, 'loza_d_343433243924401944936.jpg', 915, 600
7, 'gal2', 48, 'loza_d_114132342311825062402.jpg', 915, 600
7, 'gal2', 60, 'loza_d_264345543798258407512.jpg', 915, 600
8, 'gal1', 62, 'loza_d_343442343924401944936.jpg', 915, 600
8, 'gal3', 78, 'loza_d_414132342311445387402.jpg', 915, 600
8, 'gal3', 80, 'loza_d_873723978347683637122.jpg', 915, 600
so really i need retrieve 30 records

Maybe this is impossible for mysql?
I thought abous something like

Code: 
SELECT * FROM md_ero_Galerie AS a INNER JOIN (SELECT * FROM md_ero_Zdjecia AS c WHERE c.Galerie_Id = a.Galerie_Id LIMIT 3) AS b ON b.Galeria_Id=a.Galeria_Id
but i know it's impossible to pass value from outer to inner query

Via-Net - web directory
 
Upvote 0 Downvote
no, you don't need to retrieve 30 records

LIMIT is applied after the ORDER BY, which is performed after the GROUP BY

why don't you run a separate query to retrieve the gallery ids that you want, then take those id numbers and put them into a WHERE clause
Code: 
where m.Galerie_Id in ( 22, 28, 30, ... )

r937.com | rudy.ca
 
Upvote 0 Downvote
i'm stupid
i modified code

Code: 
SELECT
mZ.Galerie_Id
,mZ.Zdjecia_Id
,mZ.Zdjecia_Zdjecie
,mZ.Zdjecia_ZdjecieX
,mZ.Zdjecia_ZdjecieY
 FROM md_ero_Zdjecia mZ
INNER JOIN md_ero_Zdjecia mZ1 ON mZ1.Galerie_Id=mZ.Galerie_Id and  mZ.Zdjecia_ZdjecieX <= mZ1.Zdjecia_ZdjecieX
WHERE mZ.Galerie_Id IN (1,2,3,4,5)
group
    by
     mZ.Galerie_Id
,mZ.Zdjecia_Id
,mZ.Zdjecia_Zdjecie
,mZ.Zdjecia_ZdjecieX
,mZ.Zdjecia_ZdjecieY
having count(*) <= 3

which returns

4, 143, 'a4_d_298816351044428047849.jpg', 915, 600
4, 149, 'a4_d_760894345643238088170.jpg', 915, 600
4, 151, 'a4_d_915087792617137146851.jpg', 915, 600
5, 169, 'a5_d_442527302422625992231.jpg', 503, 768
5, 191, 'a5_d_704407290799599745505.jpg', 576, 768
5, 195, 'a5_d_543513961512468345249.jpg', 576, 768

but where are images from Gallery_Id 1,2 adn 3 ?



Via-Net - web directory
 
Upvote 0 Downvote
Solved ! :)

r937: Your idea was right but it didn't work because most images has the same width and height i realise taht i should change condition on second table is joined.
Thank you for help.

Code: 
SELECT
mZ.Galerie_Id
,mZ.Zdjecia_Id
,mZ.Zdjecia_Zdjecie
,mZ.Zdjecia_ZdjecieX
,mZ.Zdjecia_ZdjecieY
 FROM md_ero_Zdjecia mZ
INNER JOIN md_ero_Zdjecia mZ1 ON mZ1.Galerie_Id=mZ.Galerie_Id and  mZ.Zdjecia_Id <= mZ1.Zdjecia_Id
WHERE mZ.Galerie_Id IN (1,2,3,4,5)
group
    by
     mZ.Galerie_Id
,mZ.Zdjecia_Id
,mZ.Zdjecia_Zdjecie
,mZ.Zdjecia_ZdjecieX
,mZ.Zdjecia_ZdjecieY
having count(*) <= 3

Via-Net - web directory
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

EdLentz
  • Locked
  • Question
Need to compare two tables and list the differences
Replies
3
Views
440
EdLentz
EdLentz
boylesg
  • Locked
  • Question
Can't sort my table by a particular column, records are returned in autinc order.
Replies
4
Views
490
spamjim
spamjim
penguinspeaks
  • Locked
  • Question
Stored Procedures and triggers
Replies
9
Views
439
penguinspeaks
penguinspeaks
BobThornton
  • Locked
  • Question
Drop/Truncate tables based on a stored procedures value 1
Replies
6
Views
596
BobThornton
BobThornton
AbidingDude
  • Locked
  • Question
One Constraint for Multiple Columns 1
Replies
2
Views
322
AbidingDude
AbidingDude

Part and Inventory Search

Sponsor

Back
Top