Topic: left Join problem

[proctk]

HI

The below code is supposed to bring together multiple tables but for some reason its retuns repeat results for the query

based on my test data it should return DylanJennifer

but its returning DylanJenniferTylerJennifer

any ideas why

thank you

Code: [Select]

$query_get_Birthdays = ("SELECT * FROM users LEFT JOIN parent ON parent.owner_id = users.user_id LEFT JOIN children ON children.owner_id = users.user_id LEFT JOIN sibling ON sibling.owner_id = users.user_id WHERE month(childdob) = '3' OR month(spousedob) = '3' AND users.user_id = '$user_id' OR month(siblingdob) = '3' AND sibling.owner_id ='$user_id' OR month(parentdob) = '3' AND parent.owner_id = '$user_id'");

$get_birthdays = mysql_query($query_get_Birthdays) or die ("Invalid query". mysql_error());

while($birthdays = mysql_fetch_assoc($get_birthdays)){
echo $birthdays['childfirstname'];
echo $birthdays['spousefirstname'];
echo $birthdays['parentfirstname'];
echo $birthdays['siblingfirstname'];
}


[btherl]

Can you reformat your output a little?  I can't tell if you are getting more rows or if you are getting more columns than you expected.

[spfoonnewb]

Meh too many join's for me.. Haven't used them in a while.. I did format it to take a look, it's probably one of those or's

Code: [Select]

<?php
$q = mysql_query("SELECT * FROM `users` LEFT JOIN `parent` ON `parent.owner_id` = 'users.user_id' LEFT JOIN `children` ON `children.owner_id` = 'users.user_id' LEFT JOIN `sibling` ON `sibling.owner_id` = 'users.user_id' WHERE `month(childdob)` = '3' OR `month(spousedob)` = '3' AND `users.user_id` = '".$user_id."' OR `month(siblingdob)` = '3' AND `sibling.owner_id` ='".$user_id."' OR `month(parentdob)` = '3' AND `parent.owner_id` = '".$user_id."'") or die ("Invalid query". mysql_error());
while($b = mysql_fetch_assoc($q)){
echo $b['childfirstname'].$b['spousefirstname'].$b['parentfirstname'].$b['siblingfirstname'];
}
?>

[Barand]

When using OR condtions, use () to define the logic

eg  A AND B OR C

 is it

A AND (B OR C)

or is it

(A AND B) OR C

Also, the WHERE conditions below are superfluous as you are matching on the id
   AND sibling.owner_id ='$user_id'
   AND parent.owner_id = '$user_id'

Could be other problems but, as there is no indication of which tables all those xxxdob fields come from, I cannot be sure

[proctk]

Thank you for the replies, I'm having a hard time working with where to place the () around the ORS

I'll explain the xxdob and what tables they are from


childdob is from the table childen
spousedob is from the table users
siblingdob is from the table sibling
parentdob is from the table parent.

my goal is to get all results where the month is = 4(April)

[proctk]

the code looks like this with many edits since my last post

Code: [Select]

$query_get_Birthdays = ("SELECT * FROM users LEFT JOIN (parent, sibling, children) ON (parent.owner_id = users.user_id AND children.owner_id = users.user_id AND sibling.owner_id = users.user_id) WHERE month(children.childdob) = '3' OR month(users.spousedob) = '3' OR month(sibling.siblingdob) = '3' OR month(parent.parentdob) = '3' ");

its returning the same results over and over

[Barand]

Is this any better?

Code: [Select]

$query_get_Birthdays = "SELECT * FROM users
            LEFT JOIN parent ON parent.owner_id = users.user_id AND month(parent.parentdob) = '3'
            LEFT JOIN sibling ON sibling.owner_id = users.user_id AND month(sibling.siblingdob) = '3'
            LEFT JOIN children ON children.owner_id = users.user_id AND month(children.childdob) = '3' 
            WHERE  (month(users.spousedob) = '3')
                OR (parent.owner_id IS NOT NULL)
                OR (sibling.owner_id IS NOT NULL)
                OR (children.owner_id IS NOT NULL)";
Change '3' to '4' if you want April

[proctk]

thank you for the help it works excellent,  The April / March thing was a small typo in my post.

So I understand going forward and so I can write better mysql Statements from a formating stand point a line break show appear for each new mysql function is OR AND SELECT etc.

also is there a tutorial or a witting on when and where to place () around statements

[Barand]

The line breaks are optional. I prefer to do it that way to make it easier to read. As for the (), certainly use them with the And/OR combinations as I mentioned earlier, apart form that it's an aid to readability again.

I would recommend using the join syntax that I used.

Even if you are just using normal JOIN it's better to have

SELECT x,y,z FROM a JOIN b ON a.id = b.id WHERE something
rather than
SELECT x,y,z FROM a,b  WHERE something AND a.id = b.id

as it defines the relationship structure separate from the selection criteria and IMO afain it's easier to understand what's going on.

Also, I'd normally use table aliases (sometimes they are necessary) as an aid to readability - the query is less cluttered. If I hadn't been copy/pasting your code I'd have written it like this

Code: [Select]

SELECT * FROM users u
LEFT JOIN parent p ON p.owner_id = u.user_id AND month(p.parentdob) = '3'
LEFT JOIN sibling s ON s.owner_id = u.user_id AND month(s.siblingdob) = '3'
LEFT JOIN children c ON c.owner_id = u.user_id AND month(c.childdob) = '3' 
WHERE  (month(u.spousedob) = '3')
    OR (p.owner_id IS NOT NULL)
    OR (s.owner_id IS NOT NULL)
    OR (c.owner_id IS NOT NULL)

One last thing - avoid SELECT * unless you really do need to pull every column. Specify the columns you need

SELECT u.name, u.spousedob, .... FROM ...

The less data you fetch the more efficient the query, and again, it let's the reader see what the query is really doing. (If you had a 2Mb blob image in each record you bee pulling those too in every record even though the birthdate query doesn't need them.)

Hope that jelps

[proctk]

Thank you for taking time for the explanation. One last question why did you include this " IS NOT NULL" for each id

[Barand]

When using

user LEFT JOIN parent

if no match is found in the parent table then all result values from the parent table are NULL. So if parent.id value IS NOT NULL a matching record was found.

You are looking for users where
spouse dob in March OR there was a match against any of the other tables with dob in March.

Also note, because data may or may nor be present in the RIGHT table (parent in the example) the condition
month(p.parentdob)=3 was moved to the join condition and not left in the WHERE clause. This was why I wanted to know which tables they were in when I first posted.

[proctk]

I have run into a little snag

Her is a a query that I created with what I have learned from this post


the problem with this query is if no records are found 'mysql_query_row_num' will return a result of 8 based on data in my tables.  The same problem holds true for the query that was created with this form.  The interesting thing is if I echo a value for a row three should be no results nothing is returned but the mysql_query_row_num still retuns a value

any thoughts why this is happening

Code: [Select]

  <?php $query_get_buddyPhoto = ("SELECT * FROM buddylink b
 LEFT JOIN users u ON b.buddy_id = u.user_id
 LEFT JOIN image_files img ON  img.user_id = b.buddy_id AND album = 'Member'
 WHERE b.owner_id = '$id' ORDER BY b.added"); 
$get_buddyPhoto = mysql_query($query_get_buddyPhoto) or die ("Invalid query". mysql_error());

while($buddyPhoto= mysql_fetch_assoc($get_buddyPhoto)){


[proctk]

I must add that if I set a limit in the query it messes things all up

[Barand]

"mysql_query_row_num" not in my manual. Is it one of your own functions?

[darkfreaks]

dont ya mean mysql_num_rows ?