SELECT ships.id, ships.shipname, addons.value AS power
FROM members
INNER JOIN ships ON ships.userid = members.id AND ships.hitpoints > 0 AND members.sector = 14 AND members.id <> 1
INNER JOIN customships ON customships.shipID = ships.id
INNER JOIN addons ON addons.id = customships.partID
Is the way my databases are setup a bad practice ? Cause with this query I will have some of the data repeated because the table ADDONS contains three items I need.
This is what the query produce
> id shipname power
> 53 unknown 2
> 53 unknown 250
> 53 unknown 2
It looks normalized to me. You can handle the repeats in your code or you can do another query to pull it out separately. I usually do the latter because it’s easier to maintain, but it can be much slower and can become more complicated. (I actually had a large one time me last week and had to move it to use joins) You could restructure the code to only pull out the addons when you need them. There’s a lot you can do, but what you is up to you.
Syntax wise, I’d move the and to a where.
SELECT ships.id, ships.shipname, addons.value AS power
FROM members
JOIN ships ON ships.userid = members.id
JOIN customships ON customships.shipID = ships.id
JOIN addons ON addons.id = customships.partID
WHERE ships.hitpoints > 0 AND members.sector = 14 AND members.id <> 1
I wouldn’t say that’s bad though. Just cleaner. You probably don’t need the INNER part either. JOIN is the same thing in every database I know of. LEFT/RIGHT must be defined specifically though.