SQL JOIN Advice

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
facets
Forum Contributor
Posts: 273
Joined: Wed Apr 13, 2005 1:53 am
Location: Detroit

SQL JOIN Advice

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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
facets
Forum Contributor
Posts: 273
Joined: Wed Apr 13, 2005 1:53 am
Location: Detroit

Post 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?
facets
Forum Contributor
Posts: 273
Joined: Wed Apr 13, 2005 1:53 am
Location: Detroit

Post 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?/
facets
Forum Contributor
Posts: 273
Joined: Wed Apr 13, 2005 1:53 am
Location: Detroit

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

if my previous query works (which it should) why not extend the join clause against that set of clauses?
facets
Forum Contributor
Posts: 273
Joined: Wed Apr 13, 2005 1:53 am
Location: Detroit

Post 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? ;)
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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...
facets
Forum Contributor
Posts: 273
Joined: Wed Apr 13, 2005 1:53 am
Location: Detroit

Post 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.
Post Reply