Combining multiple sql statements

Question

I have two tables. Let's call them weapons and weapon_powers.

Weapons has ID (primary)
weapon powers has ID (primary) and WEAPONTYPE.

WEAPONTYPE is the ID of the weapon the power is assigned to.

A power equipped on weapon 1 would have a weapontype of 1.

How do I combine these two statements into one?

select * from weapons where ID = somevariable

select * from weapon_powers where WEAPONTYPE = somevariable

I want every record from the first table who's id matches the variable and I also want every record from teh second table who's weapons type also matches that same variable Also I am a mysql noob. — LinkReincarnate
– LinkReincarnate, Commented Sep 13, 2013 at 1:52
What's wrong with the output you get from executing both statements separately? — Blorgbeard
– Blorgbeard, Commented Sep 13, 2013 at 1:53

lc. · Accepted Answer · 2013-09-13 01:48:28Z

3

If I'm reading your description right, it looks like you have:

WEAPONS   <------->0..*   WEAPON_POWERS
-------                   -------------
ID (PK)                   ? (PK)
...                       WEAPONTYPE (FK WEAPONS(ID))
                          ...

In that case I think you just want a join:

SELECT *
FROM weapons
LEFT OUTER JOIN weapon_powers ON weapons.ID = weapon_powers.WEAPONTYPE
WHERE weapons.ID = @somevariable

This query will return all powers equipped for the given weapon, or a single row with data from weapons and NULL for the weapon_powers.* columns if there are no powers equipped.

answered Sep 13, 2013 at 1:48

lc.

117k21 gold badges163 silver badges190 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Combining multiple sql statements

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related