Is this SQL select code following good practice?
- by acidzombie24
I am using sqlite and will port to mysql (5) later.
I wanted to know if I am doing something I shouldnt be doing. I tried purposely to design so I'll compare to 0 instead of 1 (I changed hasApproved to NotApproved to do this, not a big deal and I haven't written any code). I was told I never need to write a subquery but I do here. My Votes table is just id, ip, postid (I don't think I can write that subquery as a join instead?) and that's pretty much all that is on my mind.
Naming conventions I don't really care about since the tables are created via reflection and is all over the place.
select
id,
name,
body,
upvotes,
downvotes,
(select 1 from UpVotes where IPAddr=? AND post=Post.id) as myup,
(select 1 from DownVotes where IPAddr=@0 AND post=Post.id) as mydown
from Post
where
flag = '0'
limit ?, ?"