Author Topic: Patulong sa MySQL  (Read 812 times)

0 Members and 1 Guest are viewing this topic.

Offline Corps

  • Sr. Member
  • *
  • Posts: 277
  • Karma: +0/-0
    • View Profile
Patulong sa MySQL
« on: October 18, 2016, 08:12:49 AM »
Guys, I'm trying to join multiple tables and in the simplest form it works, but as I add more tables in the join I find my data is not as I expect.

So what I have to start with is two tables – "compliance" and "compliance_evaluation" with a common key of "compliance_case_id"
Code: [Select]
CREATE VIEW aaa_test_vw AS
select
`ca`.`compliance_case_id` AS `compliance_case_id`,
`ca`.`compliance_case_ref` AS `compliance_case_ref`,
`ce`.`compliance_evaluation_id` AS `compliance_evaluation_id`
from
`compliance_case` `ca`
left join `compliance_evaluation` `ce`
on `ca`.`compliance_case_id` = `ce`.`compliance_case_id`

My results include (3 selections):

compliance_case_id : 68 : 68 : 68
compliance_case_ref: CASE1 : CASE 2 : CASE3
compliance_evaluation_id : 8 : 5 : 4

and all is good.

Change the VIEW to:
Code: [Select]
CREATE VIEW aaa_test_vw AS
select
`ca`.`compliance_case_id` AS `compliance_case_id`,
`ca`.`compliance_case_ref` AS `compliance_case_ref`,
`ce`.`compliance_evaluation_id` AS `compliance_evaluation_id`
from
`compliance_case` `ca`
left join `compliance_evaluation` `ce`
on `ca`.`compliance_case_id` = `ce`.`compliance_case_id`

left join `compliance_condition_assessment` `cca`
on `ce`.`compliance_evaluation_id` = `cca`.`compliance_evaluation_id`

My results now are

compliance_case_id : 68 : 68 : 68
compliance_case_ref: CASE1 : CASE 2 : CASE3
compliance_evaluation_id : 8 : 5 : 5

Change the VIEW to:
Code: [Select]
CREATE VIEW aaa_test_vw AS
select
`ca`.`compliance_case_id` AS `compliance_case_id`,
`ca`.`compliance_case_ref` AS `compliance_case_ref`,
`ce`.`compliance_evaluation_id` AS `compliance_evaluation_id`
from
`compliance_case` `ca`
left join `compliance_evaluation` `ce`
on `ca`.`compliance_case_id` = `ce`.`compliance_case_id`

left join `compliance_condition_assessment` `cca`
on `ce`.`compliance_evaluation_id` = `cca`.`compliance_evaluation_id`

left join `condition_custom` `cus`
on `cca`.`condition_custom_id` = `cus`.`condition_custom_id`
My results now are

compliance_case_id : 68 : 68 : 68
compliance_case_ref: CASE1 : CASE 2 : CASE3
compliance_evaluation_id : 5 : 5 : 5

What am I doing wrong ???

Any help much appreciated
« Last Edit: October 18, 2016, 08:15:49 AM by Corps »

Techronnati | where technology never sleeps

Patulong sa MySQL
« on: October 18, 2016, 08:12:49 AM »

Mountain View

Offline MoneyRepublic

  • Full Member
  • *
  • Posts: 166
  • Karma: +0/-0
    • View Profile
Re: Patulong sa MySQL
« Reply #1 on: October 18, 2016, 08:16:11 AM »
I think you might be looking at the wrong three records then. It is entirely possible to see what you have shown above, if there are child and grandchild records. Change your query to include the child key fields and you will see the related records.

Without seeing the full result set it is difficult to know if there is a problem, or not – I suspect there is no problem.

Add a

WHERE compliance_case_id = 68

...and look at all the returned results, what you think you should be seeing should be in there.

Offline nomadic

  • Full Member
  • *
  • Posts: 105
  • Karma: +0/-0
    • View Profile
Re: Patulong sa MySQL
« Reply #2 on: April 04, 2017, 04:26:51 AM »
kamusta ang performance ng joining tables? mabilis pa rin ba?

Offline nomadic

  • Full Member
  • *
  • Posts: 105
  • Karma: +0/-0
    • View Profile
Re: Patulong sa MySQL
« Reply #3 on: April 30, 2017, 12:36:36 AM »
kelangan mag index para bumilis. .. talking about performance tuning.

 

Related Topics

  Subject / Started by Replies Last post
0 Replies
172 Views
Last post October 16, 2008, 04:19:35 PM
by h2obubbler
0 Replies
179 Views
Last post October 16, 2008, 04:27:23 PM
by h2obubbler
0 Replies
100 Views
Last post November 25, 2009, 02:53:29 AM
by MrSpecialist
1 Replies
138 Views
Last post January 16, 2010, 04:59:46 PM
by MrSpecialist
2 Replies
1899 Views
Last post July 06, 2017, 01:12:32 AM
by arpee

Posting Disclaimer: Any individual may post a message in this forum and may do so anonymously. Therefore, the sole author is exclusively and entirely responsible for all opinions in that message. They do not represent the official opinions of Techronnati, its administrators or moderators or the Techronnati Management. Techronnati is merely acting as an impartial conduit for constitutionally protected free speech and is not responsible and will not be held liable for the content of such messages. All images and service logos are trademarks of their respective owners.