Displaying data in categories

josephman1988

Trying to put it as simply as possible.

I have guides, the guides are associated to games, then, they are associated to categories. I have the query that joins the necessary tables and fields so we can dispaly the correct guides with the correct game page, however the problem lies trying to only showing a specific guide category, eg, 3 sections, each section disaplying different guides.

Query Code:

SELECT guidesgamesconnect.gamesgId, guidesgamesconnect.guidesguId, guides.guTitle, guides.guName,  guides.guAuthor, guides.guDate,  guides.guVersion, guidescategories.gcName, guidescategories.gcId
FROM guidesgamesconnect, guidescategoriesconnect
LEFT JOIN guides  

ON guides.guId = guidesgamesconnect.guidesguId
LEFT JOIN guidescategories
ON guidescategories.gcId = guidescategoriesconnect.guidescategoriesgcId
WHERE guidesgamesconnect.gamesgId = 71 and guidescategoriesconnect.guidescategoriesgcId= 1

This is what that query produces:

As you can see, the to far right fileds are added is the gamecategories namd and id, however, the second one shouldnt be there .. the below table is the gamecategories connection table that associates the guide with the category:

This is were the problem lies i believe.
So why are the joining table fields different to the fields before the joining of them.

How would I succeed in only showing guides with the category id of '1' as attempted above.

Thanks in advance

saidbakr

I will try to deal with it using my concept which may meet your concept. Suppose a shop that has products which every product should found under one or more category. i.e Sony Play station that may be classified under Electronics category and Games category.

Each product should has product_id in products table. Each category will have category_id in category table and then there is third table will handle products_category and each product_id and category_id are going to be composite primary key.

By this approach you will able to handle your products under categories. I should mention that category table should identify each category_id and its parent_category_id to handle hierarical.

The following SQL suppose that Games category has category_id = 4 and it will select all products belongs to this category.

SELECT products.* FROM products, products_category
WHERE 
products.product_id = products_category.product_id AND
products_category.category_id = 4
ORDER BY products.product_id ASC

josephman1988

Thankyou for your reply.

Say i take that conecpt, instead of that i have a 'games' table and a 'guides' table.
games table has a 'games_id, and guides have a 'guides_id', i have a 3rd table names 'guidesgamesconnect' with a 'gamesgames_id' and 'guidesguides_id' both primary.

Query for this without the guides categories:

SELECT guidesgamesconnect.gamesgId, guidesgamesconnect.guidesguId, guides.guTitle, 	guides.guName, 	guides.guAuthor, 	guides.guDate, 	guides.guVersion
FROM guidesgamesconnect
LEFT JOIN guides
ON guides.guId = guidesgamesconnect.guidesguId
WHERE guidesgamesconnect.gamesgId = 71

Is this the beasic conecpt you mean?

Now I produce a table called 'guidescategories' with a 'gc_id' then a 'guidescategoriesconnect' with 'guidesguides_id' and 'gc_id'

Now, this is where I need the help.
Ive been trying for 2 days to do this with no success.

Hope you can shed some light onto this, regards.