0

I'm trying to get these three items out of my database however I don't think I'm using the correct JOIN.

The 3 fields I need are:

  • fldDate - Which is held inside of tblCompetition
  • fldCatName - Which is held inside of tblCategory
  • fldName - Which is held inside of tblImage

I have posted my Diagram of my tables with their field names to help explain:

Table Design

My Query I have at present is:

$query ="SELECT `fldDate`, `fldCatName`, `fldName`
FROM `tblMembEntComp`
JOIN `tblImage` ON `tblMembEntComp`.`fldMemberID` = `tblMembEntComp`.`fldMemberID`
JOIN `tblCompetition` ON `tblMembEntComp`.`fldCompID`= `tblCompetition`.`fldCompID`
WHERE `fldMemberID` = 1;"

The error appearing is: Unknown column 'fldCatName' in 'field list', can someone explain where I'm going wrong.

Sunny0101
  • 444
  • 1
  • 5
  • 18
  • `tblCategory` is not being joined so `fldCatName` doesn't exist. – user3783243 Mar 14 '19 at 16:57
  • So I need to 3 different JOINS ? – Sunny0101 Mar 14 '19 at 16:58
  • you need to join the `tblCategory` table to the `tblImage` table. – RisingSun Mar 14 '19 at 16:59
  • Hi @NigelRen I need to join on the competition table, as I need the CompDate that's held within that one – Sunny0101 Mar 14 '19 at 17:00
  • You're not selecting `fldCompDate`, why do you need to join with it? – Barmar Mar 14 '19 at 17:01
  • @Barmar, yes, `fldDate` is on the `tblCompetition` table. – SS_DBA Mar 14 '19 at 17:02
  • I would turn your Query around and start from the `tblCategory` but that is just me, I'm dyslexic so I do everything backwards. Mainly because your not selecting any fields from the first table `tblMembEntComp` – ArtisticPhoenix Mar 14 '19 at 17:05
  • 2
    @ArtisticPhoenix So you're the one they created `RIGHT JOIN` for? – Barmar Mar 14 '19 at 17:06
  • @ArtisticPhoenix the table is the glue for all the other tables. Plus, if you read the whole question, the table is used in the `WHERE` clause so it is needed. – RisingSun Mar 14 '19 at 17:07
  • 1
    @Barmar - you should have seen me a few years ago trying to figure out the difference between `Right Join, Left Join, Inner Join, Join` etc. I sure now I know Join is Inner join, and that 80% you'll never need a Right Join, but... – ArtisticPhoenix Mar 14 '19 at 17:09
  • @ArtisticPhoenix, don't forget `FULL OUTER JOIN`, very handy. :-) – SS_DBA Mar 14 '19 at 17:09
  • @WEI_DBA - I actually use a FULL OUTER JOIN to compare two databases and publish changes from one to the other. We have a Live table our clients actively search on, then we have another table we make updates on. It takes us some time to put a record together and we don't want clients searching on them tell its ready. We also allow a few clients to edit data in the same DB, so we allow them to edit the non live one and then review it before putting it live (there is also a versioning DB tied to this). – ArtisticPhoenix Mar 14 '19 at 17:15
  • Your query would be easier to write/read/maintain and less error prone if you use aliases. – HoneyBadger Mar 14 '19 at 17:30

1 Answers1

2

You are not joining the tblCategory table with the tblImage table. That is why MySQL cannot find the field fldCatName Your query should be

$query ="SELECT `fldDate`, `fldCatName`, `fldName`
FROM `tblMembEntComp`
JOIN `tblImage` ON `tblMembEntComp`.`fldMemberID` = `tblMembEntComp`.`fldMemberID`
JOIN `tblCompetition` ON `tblMembEntComp`.`fldCompID`= `tblCompetition`.`fldCompID`
JOIN `tblCategory` ON `tblImage`.`fldCatID` = `tblCategory`.`fldCatID`
WHERE `tblMembEntComp`.`fldMemberID` = 1;"

You might want to use different kinds of joins depending on your use case. This will help

RisingSun
  • 1,693
  • 27
  • 45
  • Thank you @RisingSun so I tried this in phpMyAdmin and the error coming back was: `'fldMemberID' in where clause is ambiguous`. Is this because I have 3 joins which is perhaps too many? – Sunny0101 Mar 14 '19 at 17:07
  • 1
    it is because `fldMemberID` exists in multiple tables. You just need to specify which table to use. I will update the query above – RisingSun Mar 14 '19 at 17:08
  • 1
    YOU ROCK!!! P.S thank you guys or gals for all your comments etc, still a beginner here so I know the query I wrote is probably no where near the most efficient way. – Sunny0101 Mar 14 '19 at 17:10
  • Sorry to bother you again (hopefully you see this, say if I wanted this to change to `WHERE the tblCategory . fldCatID = 1` do you know why this brings back way too many results? – Sunny0101 Mar 14 '19 at 19:52
  • 1
    it is because you are using just `JOIN` statements. The link I provided in the answer gives good details on the different `JOIN` operations. Different combinations will give different results. I suggest you play around with those and start with just 2 tables so it is easier to understand. – RisingSun Mar 14 '19 at 20:04
  • Also, the number of results returned depends on the relationships of your tables: one to one, one to many, many to many. – RisingSun Mar 14 '19 at 20:07