selecting from 3 tables..

abda53

i have two tables selected, but i need one more.. and im having some issues... heres what i have for my 2.

SELECT ims.u_id, ims.code, ims.PhotoShare, 
ims.photo_album, ims.is_approved, 
ims.albumprefs_on, ims.albumprefs_privacy, 
ims_image.title, ims_image.views, 
ims_image.size, ims_image.i_id, ims_image.ext 
FROM ims 
JOIN ims_image 
ON ims_image.u_id = ims.u_id 
WHERE ims.PhotoShare='Y' 
AND ims.photo_album='yes' 
AND ims.is_approved='yes' 
AND ims.albumprefs_on='yes' 
AND ims.albumprefs_privacy='public' 
ORDER BY rand() 
LIMIT 10

my third one is called ims_folders and i need to pull the data in it called 'in_album'

Roger_Ramjet

Well you just use joins explicitly. I've cut your field list to select all, you can type the field names yourself


SELECT ims.*, ims_images.*, in_folders.*
FROM ims LEFT JOIN ims_images ON ims.u_id=ims_images.u_id
LEFT JOIN ims_folders ON ims_images.u_id=ims_folders.u_id
WHERE ims.PhotoShare='Y' 
AND ims.photo_album='yes' 
AND ims.is_approved='yes' 
AND ims.albumprefs_on='yes' 
AND ims.albumprefs_privacy='public' 
ORDER BY rand() 
LIMIT 10

Roger_Ramjet

PS the left join will return NULL for the right hand table fields when there is no matching record.

abda53

ok thanks.
will doing a * have any major effect on the speediness of the query?

Roger_Ramjet

No. It is swings and roundabouts. All fields are read from the disk in either case. To only transfer some of them requires that the db engine perform more processing, but then it has fewer bytes to transfer so it all balances out. Try benchmarking it with both queires and you will see very little diff.
Unless we are talking thousands of records transfered across a slow link from db server to webserver.

Using the * makes queries easier to read and reduces maint when your requirements change and you need additional fields

'Downside' is with identical names in both tables, you have to use table.field to select which one you want in your script.

abda53

awesome thanks. ill try out the solution above..

i guess us navy guys dont have work today. LOL im glad they told me that last week. :rolleyes:

abda53

Originally posted by Roger Ramjet
PS the left join will return NULL for the right hand table fields when there is no matching record.

is there any way i can do it so this doesnt happen?

i need to pull the exact info to show the correct images

Roger_Ramjet

use an inner join instead. The left join will retrun all on the left and you can check for null, inner join (your original JOIN) will only return when there are records on both sides.

abda53

i keep having issues..

heres what i have

SELECT ims.*, ims_image.*, ims_folders.* 
FROM ims INNER JOIN ims_image ON 
ims.u_id=ims_image.u_id INNER JOIN 
ims_folders ON 
ims_image.u_id=ims_folders.u_id WHERE 
ims.PhotoShare='Y' AND 
ims.photo_album='yes' AND
ims.is_approved='yes' AND 
ims.albumprefs_on='yes' AND 
ims.albumprefs_privacy='public' AND 
ims_folders.in_album='yes' ORDER BY 
rand() LIMIT 10

when i check it i get this..
in_album
yes
yes
yes
yes
etc all yes.

but when i take out ims_folders.in_album='yes'
i get this
in_album
yes
no
no
yes
no
yes
yes
yes
etc. it shows the correct value
it is because of how the merge is?

abda53

anyone? 😕