CODEWITHSUNDEEP
mysqljoin
RandomNumberFun
RandomNumberFun

Reputation: 642

Inner join to get "name" property from foreign key in child table

I have the following tables:

CREATE TABLE lookup.cart
(
    id int(10) unsigned PRIMARY KEY NOT NULL AUTO_INCREMENT,
    name varchar(100) NOT NULL
);

CREATE TABLE lookup.it_tags
(
    id int(10) unsigned PRIMARY KEY NOT NULL AUTO_INCREMENT,
    cart_id int(10) unsigned NOT NULL,
    it_tag varchar(25) NOT NULL,
    CONSTRAINT it_tags_ibfk_1 FOREIGN KEY (cart_id) REFERENCES lookup.cart (id) ON DELETE CASCADE
);
CREATE INDEX pn_cart_index ON lookup.it_tags (cart_id);

diagram

What I want to do is search the database on IT_Tag from the IT_TAGS table and return with a cart name based on the key stored in it_tags. Where the name property is in cart.name.

Assuming my tables are set up correctly what is the correct (assuming again) inner join to return with my desired data?

Upvotes: 0

Views: 102

Answers (2)

Raymond Nijland
Raymond Nijland

Reputation: 11602

The results are possible with multiple queries

JOIN With WHERE clause

SELECT 
 *
FROM 
 cart 
INNER JOIN 
 it_cart 
ON
 cart.id = it_cart.cart_id
WHERE
  it_cart.it_tag = 'tag'

JOIN Without WHERE clause

SELECT 
 *
FROM 
 cart 
INNER JOIN 
 it_cart 
ON
     cart.id = it_cart.cart_id
  AND
     it_cart.it_tag = 'tag'

Deliverd table/subquery JOIN method

SELECT
 *
FROM (
      SELECT
         cart_id
       FROM
         it_cart 
       WHERE
         it_cart.it_tag = 'tag'

) AS it_cart 
INNER JOIN
 cart
ON
 it_cart.cart_id = cart.id

With IN operator 

SELECT 
 *
FROM 
 cart 
WHERE
 cart.id IN (
   SELECT
     cart_id
   FROM
     it_cart 
   WHERE
     it_cart.it_tag = 'tag'
)

Common Table Expressions WITH AS MySQL 8.0+ only

WITH it_cart_cte AS (
  SELECT
     cart_id
   FROM
     it_cart 
   WHERE
     it_cart.it_tag = 'tag'
)
SELECT 
 *
FROM 
 cart 
JOIN 
 it_cart_cte 
ON
 cart.id = it_cart.cart_id

Upvotes: 1

Marco
Marco

Reputation: 57573

If I understand what you need, query could be

SELECT c.name
FROM cart c
INNER JOIN it_tags t ON c.id = t.cart_id
WHERE it_tag = <what you need>

Naturally the search part could be something different than equals to.
You could need a LIKE operator or something else...

Upvotes: 2

Related Questions