-1

So I have a table with columns:

deck_id, card_name, quantity, board(irrelevent for this question)

Each row has a deck id which is what I need, a card name, and amount of aforementioned cards.

It would look something like this:

101, "cardofblahblah", 3, "main"
101, "differentcard", 2, "main"
102, "cardofblahblah", 1, "main"
102, "fictionalcard", 3, "main"
102, "madeupcard", 4, "main"
103, "magicalcard", 2, "main"
103, "trickcard", 3, "main"
...
...

So that's what my database looks like. What I need is a select query that will return a deck id of a deck which contains all the cards I specify, for example: I need a deck which contains 1 copy of "madeupcard", and 3 copies of "cardofblahblah". That's what I really need done, but it would be great if someone would also mention how I can do > or < instead of only just setting the amount.

I know how to do the queries, but writing a complex one like this has me stumped.

Thanks for any help you guys can offer.'

EDIT: Just to clarify, I'm looking for deck id's of decks that contain ALL cards that I specify. For example, "which decks contain 3 copy of cardofblahblah AND 1 copy of madeupcard?"

Any kind of output where I can get the deck id's is good. The getting the deck id's is the primary concern for me.

EDIT2: I figured out the solution after going through various other topics on SO. However, I may not have the most efficient query, so if someone would improve it, that would be great.

SELECT table1.deck_id, table2.deck_id FROM decklist AS table1 
    JOIN (SELECT * FROM decklist WHERE card_name = "cardofblahblah" AND quantity = 3) 
    AS table2 ON table1.deck_id = table2.deck_id 
    WHERE table1.card_name = "madeupcard" AND quantity = 1;"

EDIT3: Thanks to Telarian. He gave me a better query.

SELECT  t.deck_id
FROM    decklist t
INNER JOIN  decklist l
    ON  l.deck_id = t.deck_id
WHERE   (t.card_name = "madeupcard" AND t.quantity >= 1)
        AND
        (l.card_name = "cardofblahblah" AND l.quantity >= 3)
Tory Netherton
  • 753
  • 5
  • 23
user594044
  • 255
  • 2
  • 4
  • 13

2 Answers2

2

It seems like this is being way over-complicated...

Won't the following query give you what you want?

SELECT  t.deck_id
FROM    decklist t
    INNER JOIN  decklist l
        ON  l.deck_id = t.deck_id
WHERE   (t.card_name = "madeupcard" AND t.quantity >= 1)
        AND
        (l.card_name = "cardofblahblah" AND l.quantity >= 3)
ypercubeᵀᴹ
  • 113,259
  • 19
  • 174
  • 235
Tory Netherton
  • 753
  • 5
  • 23
  • No, that seems to give me all decks with "madeupcard" and/or "cardofblahblah". I need all decks with both only. – user594044 Jan 29 '12 at 18:36
  • Good call on the comparison operator ypercube. – Tory Netherton Jan 29 '12 at 18:54
  • No, I'm afraid you're wrong. It does not show decks with "madeupcard" and/or "cardofblahblah". It only shows decks with both "madeupcard" and "cardofblahblah". – Tory Netherton Jan 29 '12 at 18:56
  • I should point out that if there were a chance of finding a card name more than once per deck (which I doubt is the case in this situation) then you would need to add DISTINCT to the query to ensure you didn't return a deck multiple times. – Tory Netherton Jan 29 '12 at 18:57
  • Sorry, I'm mistaken. You're right, I made a mistake while inputting the query. – user594044 Jan 29 '12 at 19:50
  • Sorry. I'm a bit new to Stackoverflow, I've accepted your answer. – user594044 Feb 17 '12 at 04:48
0

Try below code

SELECT *
FROM myTable
WHERE ((board='madeupcard' AND quantity=1) OR (board='cardofblahblah' AND quantity=3))

Let me know if this is what you want...

Fahim Parkar
  • 30,974
  • 45
  • 160
  • 276
  • Not exactly. I need deck id's for decks that contain ALL of the cards listed. The one you created will show rows of decks that contain any of them, I need rows shown from decks that contain BOTH "madeupcard" AND "cardofblahblah". – user594044 Jan 29 '12 at 07:48
  • please update question accordingly.. Also provide what input will be and output you are expected... – Fahim Parkar Jan 29 '12 at 07:49