CODEWITHSUNDEEP
mysqlsql
Ted
Ted

Reputation: 3875

How to refer different table by ID?

I have a main table that needs to hold references to other tables.
Table users has an ID, and another table's REF_ID.

Table users
===========
ID   TABLE_ID   REF_ID  NAME
============================
1    1          1       John
2    2          20      Stacey
.    .          .       .
.    .          .       .
n    2          324     Ben

Table Posts(1)
===========
ID   TITLE
===========
1    "hey there!"
2    "what's up?"
.    .
.    .
n    "Look what I found!"

Table Comments(2)
===========
ID   CONTENT
===========
1    "yes, looks amazing"
2    "Wow!"
.    .
.    .
n    "yep"

The main table holds a long list of users. Each of which has either a post or a comment. My questio is how do I work with them?

  1. To insert: I guess I Should commit 2 different queries to insert, first is the insert to the related post/comment table, getting the lastInsertId and then feeding that ID to the main refs table, correct?

  2. How can I in one query, using only the main table (users) ID's, get the relevant info as well as from which table I got it, such as:

    ID   REF_ID   NAME   TITLE
1    1        John   "hey there!"

Upvotes: 0

Views: 400

Answers (3)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

First you have a bad design, to solve it right now you have to do this:

 SELECT u.ID,   u.REF_ID,   u.NAME,   p.TITLE
 FROM users u
 JOIN posts p
   ON u.ref_id = p.ID
 WHERE u.TABLE_ID = 1 

 UNION ALL

 SELECT u.ID,   u.REF_ID,   u.NAME,   c.CONTENT
 FROM users u
 JOIN comments c
   ON u.ref_id = c.ID
 WHERE u.TABLE_ID = 2

A better design should be join Posts and Comments on the same table

Table users

ID   REF_ID  NAME

Table posts

REF_ID  TYPE_ID TITLE

This way your REF_ID is unique. In your version you can have Ref_id =1 and that can be either on table Post or table comments.

Type_id can be 1 or 2 depending if is a post or a comment. Also allow you add other types later if you need. At different of previous model where you will need add a new table.

Finally the query become easier:

 SELECT u.ID,   u.REF_ID,   u.NAME,   p.TITLE
 FROM users u
 JOIN posts p
   ON u.ref_id = p.ID
 -- WHERE u.TYPE_ID = 1   // optional

Upvotes: 2

Hassan Kazmi
Hassan Kazmi

Reputation: 11

Visit https://learn.microsoft.com/en-us/sql/relational-databases/tables/primary-and-foreign-key-constraints

You have to make "ID" as a Primary and "REF_ID" as Foreign key. The Foreign key of the main table will be the Primary key of your other two tables. Simple, You have to make relationships between these tables.

Visit https://www.tutorialspoint.com/sql/pdf/sql-foreign-key.pdf for more on tables relationship.

Upvotes: 0

Daniel E.
Daniel E.

Reputation: 2480

1- I didn't understood the question, sorry.

2- It's the principle of joins :

SELECT u.ID, u.REF_ID, u.Name, p.Title
FROM users u
INNER JOIN posts p ON p.ID = u.REF_ID

Upvotes: 0

Related Questions