Page 1 of 1
SQL JOIN Advice
Posted: Fri Oct 14, 2005 12:08 am
by facets
Hi All,
I'm having some trouble with this join.
ausuitability.suitabilityId is a list 1-5 with a description call suittype.
The 2nd last left join brings that table into the main table and has worked fine until I need to display the SuitType rather than the suitabilitId.
When I add the last Left Join it errors.
How would i display the suittype correctly.
So far $suittype only display the description for the suitabilityFoil.
Code: Select all
SELECT * , aupapercategory.paperCategory FROM ausapapersummary
LEFT JOIN aupapercategory ON ausapapersummary.paperCategoryId = aupapercategory.papercategoryId
LEFT JOIN austock ON ausapapersummary.stockId = austock.StockId
LEFT JOIN auadhesive ON ausapapersummary.adhesiveId = auadhesive.adhesiveId
LEFT JOIN auliner ON ausapapersummary.linerId = auliner.linerId
LEFT JOIN ausupplier ON ausapapersummary.supplierId = ausupplier.supplierId
[b]LEFT JOIN ausuitability ON ausapapersummary.suitabilityFoil = ausuitability.suitabilityId[/b]
LEFT JOIN ausuitability ON ausapapersummary.suitabilityLabel = ausuitability.suitabilityId
WHERE summaryId = 210
I hope i've explained this well enough.
TIA, Will
Posted: Fri Oct 14, 2005 12:13 am
by feyd
it's having errors because of ambiguity between two duplicate sets of field names brought in by joining with the same table twice. This may work
Code: Select all
SELECT * , aupapercategory.paperCategory FROM ausapapersummary
LEFT JOIN aupapercategory ON ausapapersummary.paperCategoryId = aupapercategory.papercategoryId
LEFT JOIN austock ON ausapapersummary.stockId = austock.StockId
LEFT JOIN auadhesive ON ausapapersummary.adhesiveId = auadhesive.adhesiveId
LEFT JOIN auliner ON ausapapersummary.linerId = auliner.linerId
LEFT JOIN ausupplier ON ausapapersummary.supplierId = ausupplier.supplierId
LEFT JOIN ausuitability ON ausapapersummary.suitabilityFoil = ausuitability.suitabilityId OR ausapapersummary.suitabilityLabel = ausuitability.suitabilityId
WHERE summaryId = 210
Posted: Fri Oct 14, 2005 1:40 am
by facets
that's certainly stopped the error from happening but the outcome hasn't changed.
the suitabilityId is displaying correctly (3,3,4,1,5,3,2) but the suitType is displaying as if reading (1,1,1,1,1,1,1)
any additional ideas?
Posted: Fri Oct 14, 2005 7:18 am
by facets
Perhaps this diagram might make things alittle clearer..
Multiple columns in the summary table = 1 column in the suitability table
summary.Foil -> suitabilityId - > suitType
summary.Opacity -> suitabilityId - > suitType
summary.Bronze -> suitabilityId - > suitType
summary.Screen -> suitabilityId - > suitType
So with left join I can only Join one suitability to on summary.column(like foil for example)
So then all suittype's = the same as summary.foil.
basically each summary.foil/opacity etc has suitabilityId between 1-6 which links to 6 suitTypes.
I think this db design is flawed, but I can't change it now.
Can anyone help here?/
Posted: Fri Oct 14, 2005 5:49 pm
by facets
please disregard the above!
so now I can call the correct suitTypes but how do I the following into the main select statement.
keeping in mind i'm left join'ing the same table 6 times..
SELECT suitabilityFoil, suitType FROM ausapapersummary
LEFT JOIN ausuitability ON ausapapersummary.suitabilityFoil = ausuitability.suitabilityId
WHERE summaryId = 210
SELECT suitabilityYellowLight, suitType FROM ausapapersummary
LEFT JOIN ausuitability ON ausapapersummary.suitabilityYellowLight = ausuitability.suitabilityId
WHERE summaryId = 210
SELECT suitabilityLabel, suitType FROM ausapapersummary
LEFT JOIN ausuitability ON ausapapersummary.suitabilityLabel = ausuitability.suitabilityId
WHERE summaryId = 210
SELECT suitabilityOpacity, suitType FROM ausapapersummary
LEFT JOIN ausuitability ON ausapapersummary.suitabilityOpacity = ausuitability.suitabilityId
WHERE summaryId = 210
SELECT suitabilityBronze, suitType FROM ausapapersummary
LEFT JOIN ausuitability ON ausapapersummary.suitabilityBronze = ausuitability.suitabilityId
WHERE summaryId = 210
SELECT suitabilityScreen, suitType FROM ausapapersummary
LEFT JOIN ausuitability ON ausapapersummary.suitabilityScreen = ausuitability.suitabilityId
WHERE summaryId = 210
Posted: Fri Oct 14, 2005 7:13 pm
by feyd
if my previous query works (which it should) why not extend the join clause against that set of clauses?
Posted: Fri Oct 14, 2005 8:25 pm
by facets
thanks for the assistance..
it worked, as in, it didn't error but the suitType is still only being set for only one of the suitabilityFoil not opacity or screen etc.
maybe I need to typecast (not sure if thats the right term) the other columns ??
here's what I think it looks like :
suitabilityFoil -> ausuitability.suitabilityId -> ausuitability.suitType
suitabilityYellowLight -> ausuitability.suitabilityId -> ausuitability.suitType
suitabilityLabel -> ausuitability.suitabilityId -> ausuitability.suitType
suitabilityOpacity -> ausuitability.suitabilityId -> ausuitability.suitType
suitabilityBronze -> ausuitability.suitabilityId -> ausuitability.suitType
suitabilityScreen -> ausuitability.suitabilityId -> ausuitability.suitType
suitabilityIceBucket -> ausuitability.suitabilityId -> ausuitability.suitType
am i making any sense?

Posted: Fri Oct 14, 2005 8:44 pm
by feyd
maybe an export of the table structures involved and some of the data in each table so I could play with this would help, because I don't see how it's failing...
Posted: Fri Oct 14, 2005 8:54 pm
by facets
before I post everything
here's part of my mysql fetch query
$query_data = mysql_fetch_array($sql_query);
$suitabilityFoil = $query_data['suitabilityFoil'];
$suitabilityYellowLight = $query_data['suitabilityYellowLight'];
$suitabilityLabel = $query_data['suitabilityLabel'];
$suitabilityOpacity = $query_data['suitabilityOpacity'];
$suitabilityBronze = $query_data['suitabilityBronze'];
$suitabilityScreen = $query_data['suitabilityScreen'];
$suitabilityIceBucket = $query_data['suitabilityIceBucket'];
$suitType = $query_data['suitType'];
so here i'm only assigning suitType once perhaps this is wrong.
do I need a for loop around this? to assign each suitType to the suitabilityOpacity/screen/foil etc.