CODEWITHSUNDEEP
sqldatabasesqlite
kxf951
kxf951

Reputation: 155

Query results displaying multiple times

Im making a MTG card database for my class

I have 5 tables:
card holds a cardId, name, text, power, toughness, rarity, manacost, setId, CMC. cardId is the primary key

color holds a colorId and color. color is the string, colorId is the primary key
type holds a typeId and type. Type is a string, typeId is the primary key
card_colors is a linking table that holds entry pairs of cardId and colorId
card_types is a linking table that holds entry pairs of cardId and typeId.

How can I search for cards by name, and have their respective types and colors to show up in one row? Currently my query shows multiple entries of each card, one for every color.

Here's my current query:

 SELECT card.name, type.name, color.color
 FROM card, card_types, card_color
 JOIN type, color
 ON (card.cardId = card_types.cardId) AND (card.cardId = card_color.cardId)
 WHERE card.name LIKE "ENTER NAME HERE";

If you want to check it out you can find my .sqlite file here:
http://www.filehosting.org/file/details/446996/mtg_database.sqlite

Upvotes: 0

Views: 63

Answers (2)

citsonga
citsonga

Reputation: 323

The best I can do with the limited information you've provided:

select * 
from card_types 
join card
    on card_types.cardId = card.cardId
where cardId = 1 /* specify value */

You must show your table structure and much more to help you any further. I can't read your mind.

Upvotes: 2

Z .
Z .

Reputation: 12837

You need to add a condition linking card_type and type tables. with no condition you are multiplying the join of card and card_type by types.

Upvotes: 1

Related Questions