0

I have two tables:

CREATE TABLE users(
    userID int primary key not null auto_increment,
    username varchar(16),
    passcode varchar(16),
    email varchar(50) not null
);

CREATE TABLE favorites(
    userID int not null,
    favID1 varchar(50) not null,
    favID2 varchar(50) not null,
    favID3 varchar(50) not null,
    favID4 varchar(50) not null,
    favID5 varchar(50) not null,
    favID6 varchar(50) not null,
    favID7 varchar(50) not null,
    favID8 varchar(50) not null,
    favID9 varchar(50) not null,
    favID10 varchar(50) not null,
    favID11 varchar(50) not null,
    favID12 varchar(50) not null,
    FOREIGN KEY fk1(userID) REFERENCES users(userID)
);

And I would like to get the contents of the favorites table with just the username from the users table, what would the statement for it look like? I'm fairly new to SQL and databases, so apologies if this is trivial. Every other resource I've looked at doesn't seem to relate to what I want to do.

Josie Jitzel Alvarez
  • 11
  • 1
  • You would want to join both the tables basis userID column in both the tables and display username & contents of favorites table. – Namandeep_Kaur Apr 27 '20 at 06:16
  • Does this answer your question? [MySQL join with where clause](https://stackoverflow.com/questions/1219909/mysql-join-with-where-clause) Has example code showing same situation, OP just trying to add an extra step beyond that – Daniel Brose Apr 27 '20 at 06:16

1 Answers1

1

Your database design has some problems. Instead of maintaining separate columns for each favorite, you should modify the favorites table such that a single record stores one, and only one, user-favorite relationship:

CREATE TABLE favorites (
    userID int not null,
    favID varchar(50) not null,
    FOREIGN KEY fk1(userID) REFERENCES users(userID)
);

Now, if you want to report the favorite IDs for a given user, you need only use a basic join, e.g.

SELECT
    u.userID,
    u.username,
    GROUP_CONCAT(f.favID ORDER BY f.favID) AS favIDs
FROM users u
LEFT JOIN favorites f
    ON u.userID = f.userID
GROUP BY
    u.userID,
    u.username;

Perhaps the biggest problem with your current design of the favorites table is that it only admits up to 12 favorite IDs. Should your system ever have the need to support more than that, the table itself would have to be modified, i.e. you would need a DDL change. With my suggested design, you would only need to add more records/data, which is a DML change.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • Correct (of course). It's possible that there is some significance to the order in which the favourites are arranged, which your present model ignores. – Strawberry Apr 27 '20 at 06:22
  • @Strawberry Your answer also fails to address this point, because you never posted an answer. – Tim Biegeleisen Apr 27 '20 at 06:23
  • Ok. How's isolation going? – Strawberry Apr 27 '20 at 06:25
  • @Strawberry Honestly, picking between being stuck at home, and stuck in the office, is a hard one. At least, here in Singapore they allow you to exercise outside unlimited, so we are not going stir-crazy. – Tim Biegeleisen Apr 27 '20 at 06:26
  • thank you, I'll try it out. Just another question, this favorites table is just meant to store the ids to another table for a restaurant table (which has restaurant info) but i'm curious, would it just be easier to have the restaurant info in this favorites table? Also, can I implement auto-increment for the ids in the favorite table (order of insertion does matter)? – Josie Jitzel Alvarez Apr 27 '20 at 19:38
  • For your first question, no, the restaurant data should not be stored in favorites. The reason for this is _normalization_. A given restaurant ID could occur more than once, and we want to avoid storing the same information more than once. As for order, don't worry about that. And the primary key in favorites is the combination of the user and favorite ID. – Tim Biegeleisen Apr 27 '20 at 23:16
  • @JosieJitzelAlvarez Just use a separate (surrogate) AI id as your PRIMARY KEY, and make (userID,favID) unique. Also, there's no reason for them not to be integers. – Strawberry Apr 28 '20 at 13:56