CODEWITHSUNDEEP
mysql
Mohamad
Mohamad

Reputation: 35349

Reading a composite key in mysql

I'm trying to generate a dynamic property in a framework I'm using, and I'm having some difficulty.. I have two tables joined in a many to many relationship (platforms, titles, and games) games has two primary keys, titleid and platformid

select * from games where CONCAT(platformid,titleid) = 110

110 is just an example, but the record does exist with a platform of 1 and a titleid of 10...

why wont that work?

PS: I know I can rewrite the query with a where clause (platformid = 1 AND title = 10), but for the purposes of my framework, this is not an option...

EDIT: Fixed genreid error (should have been titleid, but in my actual code this was correct... )

EDIT 2: This is a scenario: 

select * where="platforms.id=#game.platformId# AND genres.id=#game.genreId# AND games.id<>#game.id#"

This is a scenario when I want to find records OTHER than the one currently selected...

Upvotes: 2

Views: 4532

Answers (1)

Quassnoi
Quassnoi

Reputation: 425683

First, a table cannot have two PRIMARY KEYs. You probably mean a composite key.

Second, your query would work but would also match a (platformid, genreid) = (11, 0).

You may use tuple comparison syntax (of course if your framework would let you do this):

SELECT  *
FROM    games
WHERE   (platformid, genreid) = (1, 10)

or make a more complex concatenation:

SELECT  *
FROM    games
WHERE   CONCAT_WS(',', platformid, genreid) = '1,10'

Upvotes: 9

Related Questions