CODEWITHSUNDEEP
mysqlsql
Aline Golvalve
Aline Golvalve

Reputation: 13

Better way to do these MySQL Queries?

Well lets me explain my question:

I got these tables:

product

photo

video

and i want to do joins to list all photos and videos of a product ... well i can think in 2 way to do it:

First:

select b.photo_url,c.video_url from product a left join photo b using(product_id) left join video c using(product_id) where a.product_id=X;

or doing two queries like:

select b.photo_url from product a left join photo b using(product_id) where a.product_id=X;
select c.video_url from product a left join video c using(product_id) where a.product_id=X;

Well the first query looks better but is there a problem if i got 2 video and 3 photos of this product I'll need to parse it with php to don't get repeat records; Anyway I would like to know which these query is better.

Upvotes: 0

Views: 49

Answers (1)

AlwaysBTryin
AlwaysBTryin

Reputation: 1964

I don't see any connections between particular videos and particular photos, other than the common product. Which you say may have multiple of each. So why not do a union?

SELECT photo_url FROM photo WHERE product_id=X
UNION
SELECT video_url FROM video WHERE product_id=X;

If you are concerned about distinguishing URL types, then add a column:

SELECT photo_url AS url, 'photo' AS url_type FROM photo WHERE product_id=X
UNION
SELECT video_url AS url, 'video' AS url_type FROM video WHERE product_id=X;

Upvotes: 1

Related Questions