CODEWITHSUNDEEP
mysqldatabaseinner-join
Colin
Colin

Reputation: 2487

MySQL Inner Join Query Not Returning Correct Results

I'm new with inner joins and I can't seem how to figure out what is going wrong here. I want all of the rows in the table "events" to be returned. Here are my tables:

+----+---------+----------+    +----+---------+
| ID |  name   | venue_id |    | ID | name    |
+----+---------+----------+    +----+---------+
| 1  | Hub dub | 2        |    | 2  | hub dub |
| 2  | Test 2  | 2        |    +----+---------+
| 3  | Test 3  | 2        |
| 4  | Test 4  | 2        |
+----+---------+----------+

Here is the query:

SELECT DISTINCT a.*, b.name AS venue a 
FROM events b 
INNER JOIN venues ON a.venue_id = b.id 
WHERE a.name LIKE '%hub%' OR b.name LIKE '%hub%'

For some reason, in my more complicated version, only Event ID# 1 is returned.

Upvotes: 3

Views: 290

Answers (6)

Ivan Pereira
Ivan Pereira

Reputation: 2219

I think what you want is Left Join table A with B.

SELECT a.*, b.name AS venue FROM events b 
   LEFT JOIN venues a ON a.venue_id = b.id
   WHERE a.name LIKE '%hub%' OR b.name LIKE '%hub%'

http://www.sqlfiddle.com/#!2/a2581/1/0

Upvotes: 0

Max
Max

Reputation: 7090

The result is correct, you has join the two tables by venue_id and id, the result is all rows from table venues with column name of table events, after that you filter the column name present on the two tables with string contains %hub%, the result is only first row.

PS You missed the a label for the table venues.

Upvotes: 0

Akhil
Akhil

Reputation: 2602

Is this what you wanted?

SELECT DISTINCT a.*, b.name AS venue a FROM events b LEFT JOIN venues ON a.venue_id = b.id and b.name LIKE '%hub%'

Upvotes: 0

echo_Me
echo_Me

Reputation: 37233

do it like that

         SELECT DISTINCT a.*, b.name AS venue 
         FROM events b 
         INNER JOIN venues a ON a.venue_id = b.id 
         WHERE a.name LIKE '%hub%' OR b.name LIKE '%hub%'

Upvotes: 0

Praveen Kumar Purushothaman
Praveen Kumar Purushothaman

Reputation: 167182

Is it a typo?

SELECT DISTINCT a.*, b.name AS venue a
-------------------------------------^

It should be this way:

SELECT DISTINCT a.*, b.name AS venue 
FROM events b 
INNER JOIN venues a ON a.venue_id = b.id 
WHERE a.name LIKE '%hub%' OR b.name LIKE '%hub%'

It is called as alias. It should be always in the table side, after FROM.

Upvotes: 1

Kermit
Kermit

Reputation: 34055

The syntax is just a bit off. The alias (a) needs to go after the table (you have it after the column alias).

SELECT DISTINCT a.*, b.name AS venue 
FROM events b 
INNER JOIN venues a ON a.venue_id = b.id 
WHERE a.name LIKE '%hub%' OR b.name LIKE '%hub%'

Upvotes: 1

Related Questions