-
March 11th, 2009, 10:44 PM
#1
Help w/ this query!
im using phpmyadmin
first of all i have this tables,
table TROUBLE_REPORTED w/ fields CODE, TR_ID
table FINDINGS w/ fields CODE, FINDINGS_ID
table SERVICE_PERFORMED w/ fields CODE, SVC_PER_ID
table SO_TROUBLE_REPORTED w/ fields SO_TR_ID, TR_ID, SO_NO
table SO_FINDINGS w/ fields SO_FINDINGS_ID, FINDINGS_ID, SO_NO
table SO_SERVICE_PERFORMED w/ fields SO_SP_ID, SVC_PER_ID, SO_NO
table SERVICE_ORDER w/ many fields including SO_NO as primary key
now,
in a SERVICE ORDER there is a SELECT STATEMENTS where u can add a possible of 4 TROUBLE REPORTED, 4 FINDINGS and 4 SERVICE PERFORMED. Those SELECT STATEMENTS have value of CODE coming form tables TROUBLE_REPORTED, FINDINGS and SERVICE PERFORMED. Now when u insert the SERVICE ORDER those reports will be inserted in SO_TROUBLE_REPORTED, SO_FINDINGS and SO_SERVICE_PERFORMED with its their foreign key TR_ID, FINDINGS_ID, SVC_PER_ID respectively having similar SO_NO.
then what i want now is to retrieve those data coming from SO_TROUBLE_REPORTED, SO_FINDINGS and SO_SERVICE_PERFORMED,
i have this query and its a MESS
PHP Code:
<? $children = mysql_query("SELECT UCASE(service_performed.code) as service_performed, UCASE(trouble_reported.code) as trouble_reported, UCASE(findings.code) as find FROM so_trouble_reported inner join so_service_performed ON so_service_performed.so_no = so_trouble_reported.so_no inner join so_findings ON so_trouble_reported.so_no = so_findings.so_no left join trouble_reported ON trouble_reported.tr_id = so_trouble_reported.tr_id left Join findings ON findings.findings_id = so_findings.findings_id left Join service_performed ON service_performed.svc_per_id = so_service_performed.svc_per_id WHERE so_trouble_reported.so_no = $so_no") or die (mysql_error()); while($row2=mysql_fetch_array($children)) { $tr = $row2["trouble_reported"]; $findings = $row2["find"]; $sp = $row2["service_performed"]; ?>
it displays repeated values
hope this problem is clear w/ u guys.
need help
Last edited by ryanbong; March 13th, 2009 at 12:08 AM.
-
March 12th, 2009, 01:51 AM
#2
Re: Help w/ this query!
If it displays repeated values, have you tried "SELECT DISTINCT <your selected fields>" ?
-
March 12th, 2009, 02:16 AM
#3
Re: Help w/ this query!
yes. i tried it but its still the same.
this is the wrong output
Code:
BROKEN-CRACKED/ BURNED/ OVERHAULED
BROKEN-CRACKED/ BURNED/ REPAINTING
BROKEN-CRACKED/ BROKEN/ OVERHAULED
BROKEN-CRACKED/ BROKEN/ REPAINTING
CUTTED WIRE / BURNED/ OVERHAULED
CUTTED WIRE / BURNED/ REPAINTING
CUTTED WIRE / BROKEN/ OVERHAULED
CUTTED WIRE / BROKEN/ REPAINTING
this must be the output
Code:
BROKEN-CRACKED/ BURNED/ OVERHAULED
CUTTED WIRE/ BROKEN/ REPAINTING
i think somethin wrong with my JOINS
Last edited by ryanbong; March 13th, 2009 at 12:17 AM.
-
March 12th, 2009, 02:35 AM
#4
Re: Help w/ this query!
Well - what you are selecting and what you expect aren't matching, which to me leads that the (main) problem is the logic behind the selection and not as much the syntax to begin with.
For example why shouldn't the line:
"UYT; BROKEN/CRACKED UTYU; BURNED UTYU; REPAINTING"
(your second listed) be selected out?
Is there some requirements which tells you the rules for what values needs to be selected and which doesn't?
Cause then you should properly use those rules in your WHERE and/or ON clause.
-
March 13th, 2009, 12:15 AM
#5
Re: Help w/ this query!
sorry i had wrong posting. But I edited it.
I am selecting the right fields.
i mean in that example there must be only 2 rows to be displayed because i only input 2 TROUBLE REPORTED, 2 FINDINGS and 2 SERVICE PERFORMED
this is the format
Code:
TROUBLE REPORTED/FINDINGS/SERVICE PERFORMED
TROUBLE1/FINDINGS1/SERVICE PERFORMED1
TROUBLE2/FINDINGS2/SERVICE PERFORMED2
TROUBLE3/FINDINGS3/SERVICE PERFORMED3
TROUBLE4/FINDINGS4/SERVICE PERFORMED4
but on the output it displays repeated values. PLS HELP.
this is the last problem to finish my project
-
March 13th, 2009, 02:42 PM
#6
Re: Help w/ this query!
First, there is no query that will give you want you want.
Second, the left join will only be useful it you expect codes to be deleted from the codes tables and want the null values so you can display 'invalid code' or something like that. Otherwise, just do a regular inner join.
A join will join all records in one table with matching records in another table. Since you have 2 records in each table you will get 8 results (2*2*2 = 8).
This first join will give you
Code:
BROKEN-CRACKED/BURNED
BROKEN-CRACKED/BURNED
CUTTED WIRE/BROKED
CUTTED WIRE/BROKED
The second join wil give you want you are currently getting.
To get this to work you will have to write code to concatenate the results from 3 queries together or code to remove duplicates from the query you are currently using. I generally do the latter because I feel that's it's more efficient to execute one query but I may be wrong on that.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|