CODEWITHSUNDEEP
mysql
rockstardev
rockstardev

Reputation: 13527

Joining several terms into one table?

I have the following table:

GENRES
gID | gNAME
1   | Fiction 
2   | Non-Fiction
3   | Comedy
4   | Horror

And this table

PRODUCTS
pID | pNAME 
1   | 4 hour work week
2   | screw it let's do it

And then the assign table:

ASSIGNTABLE
aID | pID | gID
1   | 1   | 1
2   | 1   | 2
3   | 2   | 2
4   | 2   | 3
5   | 2   | 4

I need a query that will return the following:

pID | pNAME                 | pGENRE
1   | 4 hour work week      | Fiction
2   | screw it let's do it  | Non-Fiction

In other words, I need to ONLY get ONE genre per product. How do i do this?

Upvotes: 0

Views: 32

Answers (1)

eggyal
eggyal

Reputation: 125865

As stated in my comment above, MySQL tables are unordered. If by "first one" you mean that you want the genre with the smallest gID, you can use a subquery to group the ASSIGNTABLE by pID and identify the desired genres:

SELECT    PRODUCTS.*, GENRES.gNAME
FROM      PRODUCTS
  JOIN  ( SELECT pID, MIN(gID) gID FROM ASSIGNTABLE GROUP BY pID ) t USING (pID)
  JOIN    GENRES USING (gID)

See it on sqlfiddle.

Upvotes: 2

Related Questions