omfgitsmark
commented
4 years ago Is there any reason why you are joining prices? Because it's never being used in a WHERE clause and that's what is causing it to not return both sides. Only side A of a multi-face card will have prices associated with it since it is impossible to only purchase one side, and the price could only ever be exactly the same for both sides, so it is omitted for any additional sides.
Anyways, if you remove the price join it returns the proper 81 results:
Result: 81 rows returned in 2895ms
At line 1:
SELECT distinct c.name , c.type , c.subtypes , c.text from cards as c
join legalities as l on l.uuid = c.uuid outer left join rulings as r on
r.uuid = c.uuid where l.format = 'commander' and l.status = 'Legal'
and c.type like '%creature%' and c.subtypes like '%werewolf%' order by 1 asc ;EDIT: Also, you can get rid of the 1 = 1 :grin: Out of curiousity, what generated this query?
MTGJSON Version: 4.X.X
AllPrintings.sqlite
Description of Bug:
When you run queries against the SQLite DB file, you don't get creature information for the non-primary portion of the card, for flip cards, this relates to the information when the card is flipped, and for double-sided cards the missing data is from the back side of the card.
Steps to duplicate: Open the SQLite db with with your preferred browser. Then run the following search query:
SELECT distinct c.name , c.type , c.subtypes , c.text from cards as c join legalities as l on l.uuid = c.uuid join prices as p on p.uuid = c.uuid outer left join rulings as r on r.uuid = c.uuid where 1=1 and l.format = 'commander' and l.status = 'Legal' and c.type like '%creature%' and c.subtypes like '%werewolf%' order by 1 asc ;And compare the output with the following Gathering search URL: Search Link
You'll notice that Gatherer has double the number of results returned.
The difference between the result sets is that the DB isn't returning any results for the non-primary portion of flip or double-sided cards.