How to perform a two table MySQL JOIN operation? I have a query and it is not correct

Question

I have two tables like below;

tablea

4c4fedf7    OMoy3Hoa
4c4fedf7    yiWDGB4D
broe4AMb    A9rLRawV
broe4AMb    mi9rLmZW
nhrtK9ce    yEsBoYLj
rEEtK9gt    A9rLRawV
rEEtK9gt    mi9rLmZW
rEEtK9Hh    A9rLRawV
rEEtK9Hh    msBWz8CQ

tableb

mr23wsSW    formb   4c4fedf7
OBqSU9pm    forma   broe4AMb
UvYOMvLe    formc   broe4AMb
Bhc5yfr4    forma   nhrtK9ce
R9vZJ5Kd    formb   nhrtK9ce
Bhc5y629    forma   rEEtK9gt
Bhc5y488    forma   rEEtK9Hh
Bhc5y489    formb   rEEtK9Hh

In tablea, I have prodid and tagid. Its a composite primary key. prodid acts as a foreign key, pointing to tableb. In tableb, I have prod, form, and link. prodid of tablea points to link of tableb. What I want is, if I give tagid of tablea as inputs like array, like, A9rLRawV,mi9rLmZW , I want output as;

OBqSU9pm
UvYOMvLe
Bhc5y629

This is because, I want prod, which has both A9rLRawV and mi9rLmZW in it's corresponding entry (prodid) in tablea.

I have a query like;

SELECT b.prod AS links
FROM tablea a
INNER JOIN tableb b ON a.prodid = b.link
WHERE a.tagid IN ('A9rLRawV','mi9rLmZW')
GROUP BY b.prod

But, it returns result like;

OBqSU9pm
Bhc5y629
Bhc5y488

How can I fix this? I use PHP + MySQL.

A `LEFT JOIN` should do it. See http://stackoverflow.com/questions/38549/difference-between-inner-and-outer-joins — Gerald Schneider, Dec 09 '15 at 06:57
The SELECT query is correct, where did you run the query? Could you copy your PHP code as well? — Dan Sin, Dec 09 '15 at 06:59
@DanSin I dont think PHP code is necessary. It can be executed in PHP MyAdmin — Alfred, Dec 09 '15 at 07:01
@blasteralfred : can u please post the sample data in text here so we can try in mysql with a query... entering sample data may take time typing and may be error prone based on your data with random chars. — Jas, Dec 09 '15 at 07:06

score 3 · Answer 1 · answered Dec 09 '15 at 07:24

3

You missed one entry in your tablea when marking it:

So the entries corresponding to prodid rEEtK9Hh will show up as well.

But this should be your expected result:

SELECT b.prod AS links
FROM tablea a
RIGHT JOIN tableb b ON a.prodid = b.link
WHERE a.tagid IN ('A9rLRawV','mi9rLmZW')
GROUP BY b.prod

Output:

|     prod |
|----------|
| BHc5y488 |
| BHc5y489 |
| BHc5y629 |
| OBqSU9pm |
| UvYOMvLe |

See: http://sqlfiddle.com/#!9/6e875/2

answered Dec 09 '15 at 07:24

Gerald Schneider

17,416
9
60
78

After running different joins again I noticed that your original query produces the exact same result. Seems like there is not really a problem with it. – Gerald Schneider Dec 09 '15 at 07:29
I have the same result with Gerald when running your query. To get the result you want, maybe you need to add other conditions. – Wilianto Indrawan Dec 09 '15 at 07:30
I dont want that entry. I am looking for `A9rLRawV + mi9rLmZW` combination – Alfred Dec 09 '15 at 07:34
Well, these entries belong to that combination. At least with your given sample data. – Gerald Schneider Dec 09 '15 at 07:34
@GeraldSchneider then how can I achieve my expected output? – Alfred Dec 09 '15 at 07:39
1

Either you remove the data that doesn't correspond to your expectations or you adapt your expectations to the given data. It depends on what that data is and how crucial it is. – Gerald Schneider Dec 09 '15 at 07:41

score 0 · Answer 2 · answered Dec 09 '15 at 06:49

0

In your tables one table should have primary key for example table a set prodid as primary key so there not allowed duplication records so after it in table b link work as a foreign key and you will get the result

answered Dec 09 '15 at 06:49

Mohit maru

817
8
15

in tablea, `prodid`+`tagid` is a composite primary key. In tableb, `prod` is a primary key. – Alfred Dec 09 '15 at 06:59

How to perform a two table MySQL JOIN operation? I have a query and it is not correct

2 Answers2