CODEWITHSUNDEEP
mysql
vephelp
vephelp

Reputation: 542

build query to select data based on another table

tables enter image description here

sample data

walls

wall_id   wall_name
  1        wall_1
  2        wall_2
  6        wall_6

wall_categories

wall_id   category_id
  1           2
  2           1
  6           1
  6           2

categories

category_id  category_name
  1            Wallpaper
  2            Photography

html

<a href="file.php?category=wallpaper">Wallpaper</a>
<a href="file.php?category=photography">Photography</a>

What I need to do is when the user click the Wallpaper link, I want the images with wallpaper as category to be displayed same with photography. I have built an initial query but it generates duplicate records as I'm using join, there must be something else that need to be added to make it work.

    SELECT DISTINCT walls.wall_id, walls.wall_name, walls.wall_views, walls.upload_date,
 categories.category_name FROM categories INNER JOIN wall_categories ON 
wall_categories.category_id=categories.category_id INNER JOIN walls ON 
walls.wall_id=wall_categories.wall_id;

or since the category_id is fixed, we can use walls and wall_categories table only. Then lets' say we can use the following html.

<a href="file.php?category=1">Wallpaper</a>
<a href="file.php?category=2">Photography</a>

Upvotes: 0

Views: 46

Answers (1)

Palpatim
Palpatim

Reputation: 9262

You're not limiting your query by category_id, so it's returning all wall records that have an associated category.

SELECT DISTINCT walls.wall_id, walls.wall_name, walls.wall_views, walls.upload_date,
 categories.category_name FROM categories INNER JOIN wall_categories ON 
wall_categories.category_id=categories.category_id INNER JOIN walls ON 
walls.wall_id=wall_categories.wall_id
WHERE category.category_id = ?
;

And then bind ? to the appropriate category ID from your user's selection.

Upvotes: 1

Related Questions