Discuss Insepecting the reason for wrong query results in the MySQL Help forum on Dev Shed. Insepecting the reason for wrong query results MySQL Help forum discussing administration, SQL syntax, and other MySQL-related topics. MySQL is an open-source relational database management system (RDBMS).
Receive the tools necessary to be the rock star of your field. Our 12-month program teaches you the evolving world of multi-channel marketing as well as the complex issues and opportunities found in the industry.
ASP Free and Iron Speed Designer are giving away $5,500+ in FREE licenses. Iron Speed's RAD CASE toolset can save up to 80% of your coding time. One free license per week, one perpetual license per month! Download and Activate to enter!
Web development can be a daunting task, even for specialists. There is a lot of information to absorb and a lot of technologies to learn in order to manage a superior website. When trying to learn the ropes, developers need a reliable source to introduce new ideas that can be easily implemented. When working on large projects, even web veterans may run into a technology or an aspect of a technology that they are unfamiliar with.
Posts: 656
Time spent in forums: 1 Week 1 Day 13 h
Reputation Power: 65
Insepecting the reason for wrong query results
Ok,
Lets say there is a query that executes fine, but returns wrong results. By wrong I mean un-wanted, unexpected. For example duplicate rows or not all expected rows etc etc.
Does this mean the WHERE part has not been written properly?
Does this mean joins have not been done properly?
Does it mean the mistake could be just anywhere in the query?
As an example, this simple query is supposed to show a list of tasks. If the task has not yet been viewed by the current logged member, it shows view_id as 'NULL'.
It doesn't do that! It simply does not show a job which has not been viewed by this member
Code:
SELECT
cms_job_list.id AS JID,
cms_job_list.status,
cms_group.title,
cms_job_list.date_added,
cms_job_view.id AS job_view_id,
cms_group.title AS GT,
cms_job_list.title AS JT
FROM cms_group_members,
cms_job_group,
cms_job_list,
cms_group ,
cms_members
LEFT OUTER JOIN cms_job_view ON cms_members.id = cms_job_view.member_id
WHERE
STATUS != 'deleted'
AND cms_group.id = cms_group_members.group_id
AND cms_group_members.group_id = cms_job_group.group_id
AND cms_job_list.id = cms_job_group.job_id
AND cms_group_members.member_id ='2'
AND cms_job_view.job_id = cms_job_list.id
AND cms_job_list.id = cms_job_group.job_id
AND (cms_job_view.member_id = cms_group_members.member_id OR 1=1)
GROUP BY cms_job_list.id
ORDER BY cms_job_list.id DESC
What is the best approach to trouble shoot and find the faulty part in a query? What is the de-bug steps? For example, look in WHERE section first, then,,,,,..
Thanks
Posts: 25,046
Time spent in forums: 3 Months 2 Days 22 h 44 sec
Reputation Power: 3829
Quote:
Originally Posted by zxcvbnm
What is the best approach to trouble shoot and find the faulty part in a query?
build it up in steps, which in this case means doing only one join in the query, then checking results, then adding another join and checking results, then another one, and so on
while doing this, don't use GROUP BY, don't apply GROUP BY until you know the joins are working
when you get to the LEFT OUTER JOIN you will discover the problem -- your WHERE clause includes conditions for columns in the right table
in a left outer join, for unmatched rows, the columns from the right table are set to NULL
you knew this, right?
okay, what do you think will happen here --
Code:
SELECT somefin
FROM table1
LEFT OUTER
JOIN table2
ON table2.foo = table1.bar
WHERE table2.zip = table1.zap
let's imagine you have a row in table1 that has no matching row in table 2
that means there is no row which has table2.foo = table1.bar
so all the columns in the result joined row that would've come from table2 are set to NULL
now the WHERE clause wants to filter out any result joined rows where table2.zip isn't equal to table1.zap
but table2.zip is NULL, so that equality is always false
result: the unmatched row gets dropped
this is exactly what you're doing to yourself here --
Code:
FROM cms_members
LEFT OUTER
JOIN cms_job_view
ON cms_job_view.member_id = cms_members.id
WHERE cms_job_view.job_id = cms_job_list.id
Posts: 656
Time spent in forums: 1 Week 1 Day 13 h
Reputation Power: 65
Quote:
Originally Posted by r937
build it up in steps, which in this case means doing only one join in the query, then checking results, then adding another join and checking results, then another one, and so on
while doing this, don't use GROUP BY, don't apply GROUP BY until you know the joins are working
when you get to the LEFT OUTER JOIN you will discover the problem -- your WHERE clause includes conditions for columns in the right table
in a left outer join, for unmatched rows, the columns from the right table are set to NULL
you knew this, right?
okay, what do you think will happen here --
Code:
SELECT somefin
FROM table1
LEFT OUTER
JOIN table2
ON table2.foo = table1.bar
WHERE table2.zip = table1.zap
let's imagine you have a row in table1 that has no matching row in table 2
that means there is no row which has table2.foo = table1.bar
so all the columns in the result joined row that would've come from table2 are set to NULL
now the WHERE clause wants to filter out any result joined rows where table2.zip isn't equal to table1.zap
but table2.zip is NULL, so that equality is always false
result: the unmatched row gets dropped
this is exactly what you're doing to yourself here --
Code:
FROM cms_members
LEFT OUTER
JOIN cms_job_view
ON cms_job_view.member_id = cms_members.id
WHERE cms_job_view.job_id = cms_job_list.id
Hello and thank you;
I read your post. But I need to study it in details to write better comments and make sure I've got it.
For now, based on what you mentioned, I changed it to:
Code:
cms_members
LEFT OUTER JOIN cms_job_view ON cms_job_view.member_id = cms_members.id
WHERE
cms_job_view.member_id = cms_members.id
I now get the row I want (job number 46) but the job_view_id is 1, it should be NULL as there is no value in cms_job_view table for member 2 and job 46!
I may find more solutions in your post tomorrow, but if this makes any sense to you, please do not hesitate to post.
Posts: 25,046
Time spent in forums: 3 Months 2 Days 22 h 44 sec
Reputation Power: 3829
Quote:
Originally Posted by zxcvbnm
Code:
cms_members
LEFT OUTER JOIN cms_job_view ON cms_job_view.member_id = cms_members.id
WHERE
cms_job_view.member_id = cms_members.id
it's important that you read and understand my previous post
when there is a cms_members row that has no matching cms_job_view row, then cms_job_view.member_id will be NULL, so it couldn't possibly be equal to anything, which is what you're asking in the WHERE clause now
Posts: 656
Time spent in forums: 1 Week 1 Day 13 h
Reputation Power: 65
Quote:
Originally Posted by r937
When there is a cms_members row that has no matching cms_job_view row, then cms_job_view.member_id will be NULL, so it couldn't possibly be equal to anything, which is what you're asking in the WHERE clause now
aaaarrrrrghh;;
Mate,
3 hours later.
I was reading your book, so I changed alot in the query:
I hear you and understand why it is not showing job 46, simply cause there is no record in the cms_job_view for this member. So I thought I change my WHERE part to:
WHERE (cms_members.id = '2' OR cms_members.id = NULL)
Which returns job 46 but with the wrong view_ID,, I wanted NULL!
Can you give me more direction on this please?
Thanks
Code:
SELECT cms_job_list.id AS JID, cms_job_list.title AS JT, cms_job_view.id AS VIEW_ID, cms_members.id AS mid, cms_job_group.group_id AS GROUP_ID, cms_group.title AS group_TITLE
FROM cms_job_list
LEFT OUTER JOIN cms_job_view ON cms_job_list.id = cms_job_view.job_id
INNER JOIN cms_members ON cms_job_view.member_id = cms_members.id
INNER JOIN cms_job_group ON cms_job_list.id = cms_job_group.job_id
INNER JOIN cms_group ON cms_group.id = cms_job_group.group_id
WHERE (cms_members.id = '2' OR cms_members.id = NULL)
ORDER BY JID DESC
Posts: 25,046
Time spent in forums: 3 Months 2 Days 22 h 44 sec
Reputation Power: 3829
you have a left outer join from cms_job_list to cms_job_view
but then an inner join from cms_job_view to cms_members
this would ordinarily turn the left outer join into an inner join, but you conveniently allow cms_members.id to be NULL, which it would be if the left outer join didn't find a match...
i'm not sure i completely understand your table relationships, so please let me know if the follwoing slight rewrite also produces the correct result --
Code:
SELECT cms_job_list.id AS jid
, cms_job_list.title AS jt
, cms_job_view.id AS view_id
, cms_members.id AS mid
, cms_job_group.group_id AS group_id
, cms_group.title AS group_title
FROM cms_job_list
INNER
JOIN cms_job_group
ON cms_job_group.job_id = cms_job_list.id
INNER
JOIN cms_group
ON cms_group.id = cms_job_group.group_id
LEFT OUTER
JOIN cms_job_view
ON cms_job_view.job_id = cms_job_list.id
AND cms_job_view.member_id = 2
LEFT OUTER
JOIN cms_members
ON cms_members.id = cms_job_view.member_id
ORDER
BY jid DESC
Posts: 656
Time spent in forums: 1 Week 1 Day 13 h
Reputation Power: 65
Quote:
Originally Posted by r937
i'm not sure i completely understand your table relationships, so please let me know if the follwoing slight rewrite also produces the correct result -
Thank you very much Rudy;
Hello. I believe you understood my table relationships.
There was 1 thing missing from the query you provided (which was my fault, I should have mentioned it)!
Each member can be a member of one or more groups. Each job is assigned to only 1 group. The query you posted above shows all the jobs (regardless of the group of the current logged member) to any logged in member.
So I changed your query to this (which I am still testing).
Code:
SELECT cms_job_list.id AS jid
, cms_job_list.title AS jt
, cms_job_view.id AS view_id
, cms_members.id AS mid
, cms_job_group.group_id AS group_id
, cms_group.title AS group_title
FROM cms_job_list
INNER
JOIN cms_job_group
ON cms_job_group.job_id = cms_job_list.id
INNER
JOIN cms_group
ON cms_group.id = cms_job_group.group_id
LEFT OUTER
JOIN cms_job_view
ON cms_job_view.job_id = cms_job_list.id
AND cms_job_view.member_id = '138'
LEFT OUTER
JOIN cms_members
ON cms_members.id = cms_job_view.member_id
INNER JOIN cms_group_members
ON cms_group_members.group_id = cms_job_group.group_id
AND cms_group_members.member_id = '138'
ORDER
BY jid DESC
I learned quite a few things from your post which I need to confirm with you:
About the red parts in this query below: When we use INNER JOINS (or basically joins), can it be any of the tables mentioned previously in the FROM part? In other words, any of the tables mentioned in above can be reffered to in a join. Is it correct?
I realize it is forbidden to use a tables name more than once in the FROM section. What I need to make clear is the part after ON.
If it is not clear please let me know so I explain my question better.
Quote:
SELECT *
FROM A
INNER
JOIN B
ON B.1 = A.2
INNER
JOIN C
ON C.3 = (A or B).(a column from A or B)
LEFT OUTER
JOIN D
ON D.1 = (A,B, or C).(a column from A, B or C)
AND D.7 = B.5
....
WHERE C.1 = '4'
Many thanks
Last edited by zxcvbnm : November 27th, 2011 at 06:04 PM.
Posts: 25,046
Time spent in forums: 3 Months 2 Days 22 h 44 sec
Reputation Power: 3829
Quote:
Originally Posted by zxcvbnm
About the red parts in this query below: When we use INNER JOINS (or basically joins), can it be any of the tables mentioned previously in the FROM part?
yup, perfect
one small improvement...
A, B, and C are all inner joined
but in terms of an approach to retrieving the joined rows, one of these tables is special
in the WHERE clause, you have a restriction on C
so if you imagine getting only the restricted rows of C first, then joining A and B, this will be the most efficient approach, since starting with A or B will likely return many rows that will be discarded if the C row doesn't match the filter
therefore, i always write the most restricted table as the first table in the FROM clause
Posts: 656
Time spent in forums: 1 Week 1 Day 13 h
Reputation Power: 65
Quote:
Originally Posted by r937
Therefore, i always write the most restricted table as the first table in the FROM clause. Make sense?
Hello;
This part is to be practiced
I hear you. Thank you. I am going through my files now and start making joins.
A question though:
My main challenge is calibrating. For example: How can you tell if a query is possible to be built at all or not. Because sometimes the conditions may conflict!
Is it possible to build a query without looping and using the results of this query in another query?
For example this is an extra simplified example that I am avoiding joins on purpose (to show the point):
Code:
SELECT * FROM hotels.
while($results)
{
SELECT * FROM hotels_rooms WHERE hotel_id = $results.hotel_id
while($results_room)
{
echo $room_results and other things;
}
}
The loop can simply be avoided by a small JOIN. But this is easy to say. When the join is between number tables, it gets harder. Would you please give me some tips on that beside experience.
Thank you Rudy;
Last edited by zxcvbnm : November 27th, 2011 at 07:18 PM.
Posts: 656
Time spent in forums: 1 Week 1 Day 13 h
Reputation Power: 65
Quote:
Originally Posted by r937
not really
just join one table at a time, building up your FROM clause with however many joins are required
as to how you should know if a query can be built, that's easy, the tables have to be related somehow
it's all about primary and foreign keys, right?
Well, history shows you are one of those fellows who is always right. But I cannot confirm I feel 100% confident when it comes to join queries YET as I have not done a lot of different queries with this approach yet. On paper the theory sounds great and a solid logical approach. I am documenting your tips and loving it.
What i can confirm is that things already make a lot more sense to me than before and no doubt you are great help and an amazing teacher.
One general wondering. What should I do (and avoid doing) to make sure my threads wont die?
Cheers
Last edited by zxcvbnm : November 27th, 2011 at 08:44 PM.
You see I need to add a column that shows the list of all the members belonging to this group (Wm 1, WM 2,...) so I can assign it to the new fixer.
I can simple write another query in the loop and get a drop down list of the members. Is that the way to go? Or should I change the main query?
If you knew the answer to this in a flash, I need to know how it came to your head, what your approach was?
Please guide me what is the right thing to do ( and what is the right way to look at such problem) and I start working on it, please don't give me the solution.
Thanks
Last edited by zxcvbnm : November 28th, 2011 at 03:43 AM.
Posts: 25,046
Time spent in forums: 3 Months 2 Days 22 h 44 sec
Reputation Power: 3829
Quote:
Originally Posted by zxcvbnm
The question remains un-ansewred and the helper quits
-----------------------------------------------------------------
what are you talking about? i haven't quit anything
you asked "What should I do (and avoid doing) to make sure my threads wont die?"
i was merely asking for clarification
what threads? how do your threads die? you mean here on this forum?
as for the rest of your last post, i have no idea what you're talking about
that webmoosh diagram means nothing to me, sorry, if it's related to your earlier query, i don't see the connection, nor do i understand what you mean by "add a column that shows the list of all the members belonging to this group"
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
