Help w/ this query!

[ryanbong]

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

<?
$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

[Alsvha]

If it displays repeated values, have you tried "SELECT DISTINCT <your selected fields>" ?

[ryanbong]

yes. i tried it but its still the same.

this is the wrong output

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

BROKEN-CRACKED/  BURNED/  OVERHAULED
CUTTED WIRE/ 	BROKEN/  REPAINTING

i think somethin wrong with my JOINS

[Alsvha]

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.

[ryanbong]

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

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

[Scott.Macmaster]

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

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.