Beefy Boxes and Bandwidth Generously Provided by pair Networks
The stupid question is the question not asked
 
PerlMonks  

Re: DBI MySQL Join Question

by graff (Chancellor)
on Oct 04, 2009 at 22:19 UTC ( [id://799130]=note: print w/replies, xml ) Need Help??


in reply to DBI MySQL Join Question

You said:

...this yields Cartesian results, creating duplicate values for Image records when there's no match in the Images table for values in the joined ones.

I'm not sure I understand what you mean there, but it sounds like you don't understand what "Cartesian" means in this context. You are querying for all columns from a set of joined tables, and when table A has two or more rows related to a single row of table B, the values from that row of B will have to be repeated for each matching row in A. That's the Cartesian product you get from doing that sort of join.

If you only want one row of output for each row in the Images table, you either don't want to join with tables that contain multiple matches to a given Images row, or else you want to select some sort of grouped or aggregate value from those other tables, so that there is only one value from each table to be returned for each row of Images -- e.g.:

SELECT ImageID,ImageName, Regio.RegioID,RegioName, Insula.InsulaID,InsulaName, Sources.SourceID,SourceName, (etc...), COUNT(ThemeID) FROM Images LEFT JOIN Regio ON Images.RegioID = Regio.RegioID LEFT JOIN Insula ON Images.InsulaID = Insula.InsulaID LEFT JOIN ImgThemes ON Images.ImageID = ImgThemes.ImageID LEFT JOIN Sources ON Images.SourceID = Sources.SourceID GROUP BY ImgThemes.ImageID ORDER BY ImageName LIMIT $start,$numrows"
Note the addition of the "GROUP BY" clause, and the use of the "COUNT()" function for returning a single value for all rows in the ImgThemes table that match a given Image row.

(update: fixed typo in field list of select statement)

Replies are listed 'Best First'.
Re^2: DBI MySQL Join Question
by Jazz (Curate) on Oct 04, 2009 at 23:54 UTC
    When I add the GROUP BY and COUNT() clauses as you noted, I get only 2 out of 10 Image records. When I change the GROUP BY to Images.ImageID instead of imgThemes.ImageID, I get all Image returned (unduplicated), but only one result from imgThemes. I need all of the records from imgThemes which match the ImageID.

    If you have time, please see the data structure I'm looking for in my reply to zwon above.

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: note [id://799130]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others wandering the Monastery: (4)
As of 2024-04-19 23:14 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found