Member Avatar for newMeg

Hi I have 3 tables in my database.
1) photo
2) category
3) subcategory

in my photo table i save my subcategoryid which is tied to the category table.

What i need to do is get the all the subcategoryname from subcategory table where photo.subcategoryid='1' and photoid='2'.

I can actually get the value, but what i want is also to display all the other values in subcategory table that share the same categoryid for the particular photoid.

lets say my photoid is 1 and my subcategoryid is 2 , so when check in category table , the categoryid for subcategory 2 is 3. How can i display other subcategories which is the same as categoryid 3

i hope my question is clear. Please help me on this.

eg


category table
categoryid
1
2
3
4

subcategorytable
subcategoryid
1
2
3
4

categoryid
1
1
2
2

  • 3 Contributors
  • 2 Replies
  • 126 Views
  • 18 Hours Discussion Span
  • Latest Post Latest Post by SheSaidImaPregy

Dani AI

Generated

Short answer: find the photo's subcategory, get that subcategory's category, then return every subcategory that belongs to that category (optionally excluding the photo's own subcategory). This follows the normalization idea suggested; 's request for table descriptions is covered by the assumptions below.

A simple, efficient way is to join the tables so the category is resolved once and then use it to fetch all matching subcategories. Replace @PhotoID with the photo you want:

SELECT s2.SubcategoryName
FROM Photo p
JOIN Subcategory s  ON s.SubcategoryID = p.SubcategoryID
JOIN Subcategory s2 ON s2.CategoryID = s.CategoryID
WHERE p.PhotoID = @PhotoID
  AND s2.SubcategoryID <> s.SubcategoryID;

That returns all other subcategory names in the same category as the photo's subcategory. Remove the <> line if you want the photo's own subcategory included.

Alternative patterns:

  • A subquery can work, but it repeats lookups and can be slower.
  • For SQL Server, CROSS APPLY can pull the category once and join to subcategories.

Practical notes and troubleshooting:

  • Qualify columns with table aliases to avoid ambiguous-name errors.
  • Ensure Subcategory.SubcategoryID is a primary key and there is an index on Subcategory.CategoryID and on Photo.SubcategoryID for good performance.
  • Handle NULLs: if a photo has no SubcategoryID, the joins return no rows; add guards if needed.
  • If subcategory names are not unique and you need each only once, add DISTINCT.
  • Enforce foreign keys so the relationships remain consistent.

This approach keeps the logic set-based and performs well on appropriately indexed tables while matching the normalized model discussed above.

Recommended Answers

All 2 Replies

Member Avatar for Ramy Mahrous

Describe for me what's Photo, Category and Subcategory table?? the descrription of each of which I may help you!

Member Avatar for SheSaidImaPregy

Question, why is your subcategory in your category table? Currently it is like this:

Category
      /        \
Photos          Subcategory

Why don't you make it like this:

Category
             \
               Subcategory
                           \
                            Photos

Like this:

Category
- CategoryID

Subcategory
- SubcategoryID
- CategoryID

Photos
- PhotoID
- SubcategoryID
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.