problem in php mysql

i want t开发者_运维知识库o get distinct category name from both photos & videos tables which have category name as in categories table...

here, m trying to execute a mysql query to get distinct categories name from main category table which are coming in both or even in one table i.e. photos & videos...

mysql query:

SELECT DISTINCT t1.category_name FROM categories t1, photos t2, videos t3 WHERE
t1.category_name=t2.category OR t1.category_name=t3.category AND t2.block=0 AND 
t3.block=0 ORDER BY t1.category_name asc

here's the structure of all tables...

CATEGORIES:: (MAIN TABLE)

PHOTOS:: (CONTAINS PHOTOS OF ABOVE CATEGORIES)

VIDEOS:: (CONTAINS VIDEOS OF ABOVE CATEGORIES)

please, help me...

I'd use an EXISTS clause

SELECT c.category_name
FROM categories c
WHERE EXISTS (
    SELECT 1 FROM photos p
    WHERE p.category = c.category_name
    UNION
    SELECT 1 FROM videos v
    WHERE v.category = c.category_name
)

If i understand the question right this would work:

select distinct categories.name 
from categories 
left join photos on photos.category = categories.name
left join videos on videos.category = categories.name
where (photos.id IS NOT NULL) OR (videos.id IS NOT NULL);

But you should use a foreign key for your connection between category and photo/video. http://dev.mysql.com/doc/refman/5.1/de/innodb-foreign-key-constraints.html

Change the datatype of photos.category to (INT) and the videos.category to (INT) it will be way faster when the table become larger

SELECT  t1.category_name 
FROM categories t1,
JOIN photos p ON p.category = t1.id
JOIN videos v ON v.category = t1.id
GROUP BY t1.category_name
ORDER BY t1.category_name ASC

You need to change the datatype of photos.category to INT instead of varchar and do the same for videos.category, then try below query.

SELECT  Cat.category_name 
FROM categories As Cat,
JOIN photos AS P ON P.category = cat.id JOIN videos AS vid ON vid.category = Cat.id
GROUP BY Cat.category_name
ORDER BY Cat.category_name ASC

thanks.