Accessing Row from Mysql DB by chaining query of foreign key from another table

Question

I'm having difficulty trying to find the best way to get my results from a table. I want to get the targeted row from a table by one using the primary key from another using a foreign key.

The tables are would be set similar to this(minus a lot of other attributes for space):

user Table:
  user_Id(pk)
  name
  type
  venue_Id(unique/indexed)

venue Table:
  venue_Id(fk)
  rating

Logic flow is: user_Id is provided by a session variable. Query DB table 'user' to find that user. Go to type of user to identify if user is person or venue. Assuming user is venue, go to DB table 'venue' and query table for rating using foreign key from unique/indexed venue_Id from user table.

The query looks like

SELECT rating FROM `venue` WHERE `user_Id` = '$user_Id' AND `type` = 'venue'

Is this possible, and if so, what is the correct way to go about it?

@spencer7593 try joining the user table and venue table – abdullacm Sep 29 '14 at 04:12 — abdullacm, Sep 29 '14 at 04:12

score 3 · Accepted Answer · edited May 23 '17 at 11:49

3

You have a few ways to retrieve this information.

Using JOIN:

SELECT v.rating 
FROM venue v INNER JOIN user u
ON v.venue_id= u.venue_id 
AND u.`user_Id` = '$user_Id' AND u.`type` = 'venue'

Using an IN sub-query

SELECT rating 
FROM venue 
WHERE venue_id IN (SELECT venue_id FROM user
   WHERE `user_Id` = '$user_Id' AND `type` = 'venue')

BTW, you should consider protect your code from potential SQL Injections

edited May 23 '17 at 11:49

Community

1
1

answered Sep 29 '14 at 04:13

cha

10,301
1
18
26

+1. (My preference is for *all* column references qualified, including columns in the select list, i.e. `SELECT v.rating`. That doesn't have any immediate impact on the query; but there are some benefits: 1) someone reading the SQL will know (without having to look at the table definitions), which table is the source of the columns. 2) Later, when a column named `rating` is added to the `user` table, this query will continue to work, instead of throwing an "ambiguous column" error. – spencer7593 Sep 29 '14 at 13:47

score 2 · Answer 2 · answered Sep 29 '14 at 04:16

Its a bit unclear you explained that way. From what I get, there is 2 table User and Venue. In User table u have: user_id, venue_id, name, type. While in Venue table u have: venue_id, rating.

You are expecting to get rating (Venue Table) while you use the WHERE clause in user_id and type which both stored on User Table.

Your Query: SELECT rating FROM venue WHERE user_Id = '$user_Id' AND type = 'venue'

It is impossible to get it done like above because you are selecting from venue table while user_id and type is not from venue table. So it will make it unidentified even you have chaining the FK. Because FK will only to show and make some constraint to parent child table.

The query should be something like this:

SELECT rating FROM venue v JOIN user u on v.venue_id = u.venue_id WHERE u.user_Id = '$user_Id' AND u.type = 'venue'

Correct me if I am wrong..

spencer7593 · Answer 3 · 2014-09-29T04:22:45.323

Combining rows from two tables based on the tables having columns with equal values is called an equi-join operation, it's the pattern we typically use to "follow" foreign key relationships.

As an example:

$sql = "SELECT v.rating
          FROM `venue` v
          JOIN `user` s
            ON s.venue_Id = v.venue_Id
           AND s.type` = 'venue'
         WHERE s.user_Id` = '" . mysqli_real_escape_string($con, $user_Id) ."'"

This isn't the only pattern, there are several other query forms that will return an equivalent result.

As an example of using an EXISTS predicate:

$sql = "SELECT v.rating
          FROM `venue` v
         WHERE EXISTS 
               ( SELECT 1 
                   FROM `user` s
                  WHERE s.venue_Id = v.venue_Id
                    AND s.type` = 'venue'
                    AND s.user_Id` = '" 
                         . mysqli_real_escape_string($con, $user_Id) 
                         ."'"
               )";

The original query appears to be vulnerable to SQL Injection; the example queries demonstrate the use of the mysqli_real_escape_string function to "escape" unsafe values and make them safe to include in SQL text. (That function would only be appropriate if you are using the mysqli interface. Using prepared statements with bind placeholders is another approach.

Accessing Row from Mysql DB by chaining query of foreign key from another table

3 Answers3