CODEWITHSUNDEEP
mysqldatabasejoin
gregz
gregz

Reputation: 45

combing two SELECT statements into a JOIN

I have two tables: table_1: item_id, name ... table_2: image_id, item_id, url

The following function retrieves the 'urls' of the items with the '$name' that is passed in. I first need to get the 'item_id' from table_1 to use in the query. I only need to return the 'url's from table_2.

It currently works fine, but I feel it could be streamlined to be done in one query with a JOIN rather than two separate queries. I've tried using different 'JOIN's to make it work but I can't seem to get it right.

Here's the function as it stands now with the two queries...

function get_items( $name ) {

    global $wpdb;

    $sql1 = "SELECT `item_id` FROM `table_1` WHERE `name` = '$name'";
    $results1 = $wpdb->get_var( $sql1 );

    $sql1 = "SELECT `url` FROM `table_2` WHERE `item_id` = '$results1'";
    $results2 = $wpdb->get_results( $sql1, ARRAY_A );

    return $results2;

}

and here is the 'JOIN' that I tried implementing, but was unsuccessful (I've also switched it around and did a 'LEFT/RIGHT JOIN' as well resulting in the same result) ...

$sql1 = "SELECT `table_2`.`url` 
         FROM `table_2` 
         INNER JOIN `table_1`
         ON `table_2`.`item_id` = `table_1`.`item_id`
         WHERE `table_1`.`item_id` = '$name'";

Any advice on combining these two queries?

Upvotes: 1

Views: 86

Answers (2)

Aman
Aman

Reputation: 76

select url from table_2 where item_id IN (select item_id from table_1 where name = $name )

Upvotes: 0

John Woo
John Woo

Reputation: 263683

The problem with your query is this WHERE table_1.item_id = '$name', it should be the name not the item_id

SELECT  b.url
FROM    table1 a
        INNER JOIN table2 b
            ON a.item_id = b.item_id
WHERE   a.name = '$name'

Upvotes: 2

Related Questions