2. PHPRunner General questions
  3. Help with Query
This topic is locked

Help with Query

 		4/23/2007 1:36:29 AM
PHPRunner General questions
E
 evan_ad2000 author

Hi,
I am trying to create a custom table by drawing information from different tables. I have 3 tables

`assembly`(`AssemblyPartNumber`,`AssemblyName`)

`component`(`ComponentPartNumber`,`ComponentName`)

`assembly_boms` (`AssemblyPartNumber`,`ComponentPartNumber`,`Designator`)
What I'm trying to do is to have `AssemblyPartNumber`,`ComponentPartNumber`,`Designator` from `assembly_boms`

and then add `AssemblyName` from `assembly` and `ComponentName` from component into one table
Thanks,

Evan
V
 		Vladimir 4/23/2007

Hi,
try this query:

SELECT

`assembly_boms`.`AssemblyPartNumber`,

`assembly_boms`.`ComponentPartNumber`,

`assembly_boms`.`Designator`,

`assembly`.`AssemblyName`,

`component`.`ComponentName`

FROM

`assembly_boms`,

`assembly`,

`component`

WHERE

`assembly`.`AssemblyPartNumber`=`assembly_boms`.`AssemblyPartNumber`,

`component`.`ComponentPartNumber`=`assembly_boms`.`ComponentPartNumber`
E
 		evan_ad2000 author 4/23/2007

Hi,

try this query:


Hi,
Thanks for the tip but I tried that before, it gives me a #1064 syntax error, not sure why it isnt working for me. Is it because of the way my db is designed? the thing is, a Component will not have an AssemblyName and an Assembly will not have have a Component Name. The query tries to fill every field making the result funny or not what I expected. I was hoping someone can shed some light into how I can go around doing this. Other than re-create the DB of course..
Thanks,

Evan
J
 		Jane 4/24/2007

Evan,
here is the correct SQL query:

SELECT

`assembly_boms`.`AssemblyPartNumber`,

`assembly_boms`.`ComponentPartNumber`,

`assembly_boms`.`Designator`,

`assembly`.`AssemblyName`,

`component`.`ComponentName`

FROM

`assembly_boms`,

`assembly`,

`component`

WHERE

`assembly`.`AssemblyPartNumber`=`assembly_boms`.`AssemblyPartNumber` and `assembly_boms`.`ComponentPartNumber`=`component`.`ComponentPartNumber`
E
 		evan_ad2000 author 5/14/2007

Evan,

here is the correct SQL query:


Hi Jane,
Thanks for the suggestion. However, it doesn't really quite work out right as AssemblyPartNumber can be a ComponentPartNumber as well. Using this query, the AssemblyPartNumber in the ComponentPartNumber Column is not shown.

Is there another work around? or the only way is to change the relation between the tables?
Thanks,

Evan