9 Replies - 420 Views - Last Post: 02 December 2018 - 10:42 AM

#1 Nykc   User is offline

  • Gentleman of Leisure
  • member icon

Reputation: 737
  • View blog
  • Posts: 8,653
  • Joined: 14-September 07

Querying multiple tables in my database producing weird results.

Posted 01 December 2018 - 08:31 AM

So mostly a front-end developer here

I built a nice functional, simple database to track my gpk collection as well as checklists so there are over 14000 records in it.

Table 1 is for base sets.
Table 2 is for inserts
Table 3 is for parallels - which is not important at the moment.

So when I run a query on an individual table I get the data I want,
but when I run a query for both tables it does a join and combines the two and produces weird results.

What I am trying to accomplish is a way to query both tables and print all the cards in a given set including the inserts.

Now I could write two separate queries to have them display on the page, but is there a way to do it in one query? Do I need to modify my db structure maybe, I figured gpk_series would act like the common denominator here between the two tables.

I provided some screen shots for clarity and to show my current structure.

Attached Image
Attached Image
Attached Image
Attached Image

I guess the other solution would be too combine the two tables and add a new column called set_type or something that could determine whether it is a base or insert. these types of cards didn't come into play until the later series in gpk cards so I blame topps for the complications. (this would also bring in the parallels table into play which I do not know how to normalize - its literally a repeat of the base cards with one of 4/5 different colored borders)

Once I can figure out how to run these queries for the results I need, I can start spitting the results out on the web.

As a user I would like to see a checklist of all the 2018S2 cards, including base_sets, Inserts and parallels...

Which would return:
Attached Image

Thanks

This post has been edited by Nykc: 01 December 2018 - 08:52 AM


Is This A Good Question/Topic? 0
  • +

Replies To: Querying multiple tables in my database producing weird results.

#2 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15427
  • View blog
  • Posts: 61,803
  • Joined: 12-June 08

Re: Querying multiple tables in my database producing weird results.

Posted 01 December 2018 - 10:56 AM

I am not sure what GPK are, or but explain to me what a 'base set' vs 'insert' vs parallels are and how they interact. I am getting the feeling the table structure is borked from the get go and needs to be reexamined.
Was This Post Helpful? 0
  • +
  • -

#3 Nykc   User is offline

  • Gentleman of Leisure
  • member icon

Reputation: 737
  • View blog
  • Posts: 8,653
  • Joined: 14-September 07

Re: Querying multiple tables in my database producing weird results.

Posted 01 December 2018 - 12:03 PM

Garbage Pail Kids sticker collection.

So a base set are all the cards that make a set - usually numbered 1a - 42b... 1a, 1b, 2a, 2b, etc....
Insert cards are those stupid little extras that are impossible to collect all of, but Topps and other manufacturers put them in there. Chase cards, think Patch, Autographs etc, cards that are not required to complete the set.

Parallels are literally the same cards as the base set - but have different colored borders, usually there are 4-5 parallel sets.

Here is an example:
http://geepeekay.com/gallery_80s.html

So in retrospect the table structure might be borked as I am not a DBA or Backend developer by any means. -- however the table structure for base sets, and even inserts works great for the basic queries I run. But now that I need to tie these together to put on a web page, it could probably be more optimized.

So if maybe the solution is rebuild the database structure, that is fine - I am currently working off of excel sheets and a database tucked away in my localhost running a CLI and MySql workbench

This post has been edited by Nykc: 01 December 2018 - 12:04 PM

Was This Post Helpful? 0
  • +
  • -

#4 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15427
  • View blog
  • Posts: 61,803
  • Joined: 12-June 08

Re: Querying multiple tables in my database producing weird results.

Posted 01 December 2018 - 12:12 PM

Aaaaaaaaaaah okay. Damn I didn't know those were still a thing. Hell - a few days back I got sucked into watching the movie on some random local channel that pumps out greatly-terrible flicks.

Give me a few minutes to suggest an alternative.
Was This Post Helpful? 0
  • +
  • -

#5 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15427
  • View blog
  • Posts: 61,803
  • Joined: 12-June 08

Re: Querying multiple tables in my database producing weird results.

Posted 01 December 2018 - 12:45 PM

What I propose is an alternative and is a more card centric view.

The theory would be the card table holds most of the information, and there are ansilary tables that can be added to as you insert.

Have a 'SET' table that holds an ID and a name.
Have a 'variant' table that is an id and variant description (presumably there are a finite, and repeatable list of variants)

Your card table would hold an id, set id, name, card number, variant id (if there is one), and a date entered.



crea te table #SET(ID int NOT NULL  IDENTITY(1,1) PRIMARY KEY, NAME varchar(50))
cre ate table #VARIANT_DESCRIPTION(ID int NOT NULL  IDENTITY(1,1) PRIMARY KEY, DESCRIPTION varchar(50))

cr eate table #CARD(ID int NOT NULL  IDENTITY(1,1) PRIMARY KEY,SET_ID int, NAME varchar(50), CARD_NUMBER varchar(10),  VARIANT_ID int, DATE_ENTERED datetime  DEFAULT GETDATE())


in sert into #SET(NAME) values ('test set')

in sert into #VARIANT_DESCRIPTION(DESCRIPTION) values ('AUTOGRAPH')
in sert into #VARIANT_DESCRIPTION(DESCRIPTION) values ('Patch')
in sert into #VARIANT_DESCRIPTION(DESCRIPTION) values ('Chase')

in sert into #CARD(NAME, SET_ID, CARD_NUMBER) values ('test', 1, '1a')
in sert into #CARD(NAME, SET_ID, CARD_NUMBER, VARIANT_ID) values ('test 2', 1, '1b', 1)

select * from #SET
select * from #VARIANT_DESCRIPTION
select * from #CARD


select a.*, b.NAME, c.DESCRIPTION
from #Card a
join #SET b on a.SET_ID = b.ID
left join #VARIANT_DESCRIPTION c on a.VARIANT_ID = c.ID



dr op table #Card
dr op table #VARIANT_DESCRIPTION
dr op table #SET





#SET
ID          NAME
----------- --------------------------------------------------
1           test set

(1 row affected)

#VARIANT_DESCRIPTION
ID          DESCRIPTION
----------- --------------------------------------------------
1           AUTOGRAPH
2           Patch
3           Chase

(3 rows affected)

#CARD
ID          SET_ID      NAME                                               CARD_NUMBER VARIANT_ID  DATE_ENTERED
----------- ----------- -------------------------------------------------- ----------- ----------- -----------------------
1           1           test                                               1a          NULL        2018-12-01 13:39:16.557
2           1           test 2                                             1b          1           2018-12-01 13:39:16.560

(2 rows affected)

ID          SET_ID      NAME                                               CARD_NUMBER VARIANT_ID  DATE_ENTERED            NAME                                               DESCRIPTION
----------- ----------- -------------------------------------------------- ----------- ----------- ----------------------- -------------------------------------------------- --------------------------------------------------
1           1           test                                               1a          NULL        2018-12-01 13:39:16.557 test set                                           NULL
2           1           test 2                                             1b          1           2018-12-01 13:39:16.560 test set                                           AUTOGRAPH



That last output is the interesting synthesis. It would indicate the set name at the end, and if there is a variant type assigned to it the variant name.

Am I in the ball park of this making sense?

You can probably add a 'release date' to the #set table if you want to track that information as well.

Going further you can add a 'grade id' and create a 'grade' table that indicates if the card is fine, brand new, used, etc.
Was This Post Helpful? 1
  • +
  • -

#6 Nykc   User is offline

  • Gentleman of Leisure
  • member icon

Reputation: 737
  • View blog
  • Posts: 8,653
  • Joined: 14-September 07

Re: Querying multiple tables in my database producing weird results.

Posted 01 December 2018 - 02:53 PM

yeah thats pretty much spot on. ill give that a go and repost more questions if they arise.

thanks for the help,

and yeah they made a pretty big comeback, there are like 15,000 possible stickers/cards now and 5000 base/regular cards
Was This Post Helpful? 0
  • +
  • -

#7 baavgai   User is offline

  • Dreaming Coder
  • member icon


Reputation: 7505
  • View blog
  • Posts: 15,553
  • Joined: 16-October 07

Re: Querying multiple tables in my database producing weird results.

Posted 01 December 2018 - 02:54 PM

First: screen shots of text? Seriously?!? Text is far more helpful and easier on, well, everyone.

Both your tables are identical structure? Why, then, do you need two tables? You want one table and an additional field to indicate base or insert. Though, as already pointed out, a complete reconsideration of the design seems appropriate.
Was This Post Helpful? 1
  • +
  • -

#8 Nykc   User is offline

  • Gentleman of Leisure
  • member icon

Reputation: 737
  • View blog
  • Posts: 8,653
  • Joined: 14-September 07

Re: Querying multiple tables in my database producing weird results.

Posted 01 December 2018 - 05:20 PM

Yeah seems I was over thinking it.

I was trying to get the text to lay out correctly on the screen and failing miserably so I posted screen shots. It's been a while since I hit these forums. But noted.

Thanks for the input, I will rebuild. What got me overthinking everything was the parallels honestly. If I were to account for them there would be so much duplication, but then again - another field would probably suffice for that too.

I always over think these damn databases.
Was This Post Helpful? 0
  • +
  • -

#9 Nykc   User is offline

  • Gentleman of Leisure
  • member icon

Reputation: 737
  • View blog
  • Posts: 8,653
  • Joined: 14-September 07

Re: Querying multiple tables in my database producing weird results.

Posted 02 December 2018 - 08:24 AM

So this definitely solves my querying issues and seems a little easier to manage. Of course the syntax was a little different for me, MySQL 8 -- identity(1,1) = auto_increment
but once I rebuilt things and figured out the queries, it works fine.

Now to rebuild and see about getting these queries to write to a page.
Was This Post Helpful? 0
  • +
  • -

#10 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15427
  • View blog
  • Posts: 61,803
  • Joined: 12-June 08

Re: Querying multiple tables in my database producing weird results.

Posted 02 December 2018 - 10:42 AM

Woohoo! Glad to hear it got you on a good path.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1