Left Join connecting primary key to normal column causes havoc

Question

Link below is related to my question:

Eloquent Left Join, getting unexpected result

Here's my table structure just to give an overview.

Options:

Event Meta:

Well, it really is the same problem but I thought it's because of Eloquent. When I tried to use PDO querying the problem on my link. It still does the same.

$query = $db->prepare(
    "   SELECT      * FROM event_meta a
        LEFT JOIN   options b ON b.id = a.meta_value
        WHERE       a.meta_key = 'logo'
"); $query->execute();

$object = $query->fetchObject();

echo $object->value, '<br>';
echo $object->meta_value;

So what I did is, I have a table called event_meta and trying to join the options table to connect them I use b.id and for event_meta is a.meta_value

What I want to happen on my end is to retrieve the options.value first to check if it's empty. If it is empty, I will get the event_meta.meta_value's value to output it.

So for example on my query above, I want to get the meta_key called logo. It should get the output of an image name. But since I'm connected to options table it should get the options.value. When outputting the options.value it should be blank. Since the meta_value of logo does not equals to any options.id. But it's retrieving the Top Up value.

@Moderator, I'm trying to connect both of them, just giving a visual look of what my table looks like. Since every first character (Not the whole value) of `meta_value` is connecting to my `options id`. — Wesley Brian Lachenal, Sep 18 '15 at 06:13
Not all really, just copying the select query on what is Eloquent trying to do when querying the data. So what I need both `meta_value` of event_meta and `value` of options. I need to check if value is empty, if it is I will have to get the meta_value — Wesley Brian Lachenal, Sep 18 '15 at 06:18
@BrianCoolidge: Please suggest what you required..1st you need only common rows from both tables where meta_key=logo or all rows from meta_value where meta_key=logo and corresponding rows from option table.....also as per meta_key='logo' your meta_value prmary key value should be 29 not 8... — Zafar Malik, Sep 18 '15 at 06:29
@ZafarMalik, I'm connecting left join in options as `event_meta.meta_value` not `event_meta.id` though. I revised my question. — Wesley Brian Lachenal, Sep 18 '15 at 06:34
@BrianCoolidge: show what output you are getting and what you need...as per your query you should get output as per following ---- 29,1,'logo','6b3b........',NULL,NULL,NULL........also you are joining both tables based on meta_value which seems either longvarchar or text type field, so it will kill the performance. — Zafar Malik, Sep 18 '15 at 06:44

Matt Gibson · Accepted Answer · 2015-09-18T08:58:16.593

Your "Top Up" options.id value is 6. The numeric value of your (presumably binary image) logo value in event_meta.meta_value is 6, because it starts "6b3b.....". That's why they're comparing equal.

When comparing a numeric field with a character field, MySQL will take the numeric value of the character field and compare it with the number. The thing you may be missing is that the numeric value of a character field like "123abc3478uweefauiofyh3489h" in MySQL is 123.

Cast your numeric options.id field to the same type as event_meta.meta_value in your JOIN.

score 0 · Answer 2 · edited Sep 18 '15 at 08:18

0

Use this:

$query = $db->prepare(
            "SELECT *
            FROM options
            LEFT OUTER JOIN event_meta
            ON options.id=event_meta.meta_value
            WHERE event_meta.meta_key= 'event_package';");

edited Sep 18 '15 at 08:18

EM-Creations

4,195
4
40
56

answered Sep 18 '15 at 06:19

Abdulla Nilam

36,589
17
64
85

I changed `event_package` to `logo`. Still produces the same result. I'm getting the `Top Up` value from `options table` It should be blank though. Since the value from `logo` is like loooong. – Wesley Brian Lachenal Sep 18 '15 at 06:23
I revised my question. Please see it. :) – Wesley Brian Lachenal Sep 18 '15 at 06:34

Left Join connecting primary key to normal column causes havoc

2 Answers2

Linked