MySQL Select multiple rows from one row in 2 different tables

Question

I have this situation:

Table A (Ex. Dog's name):
ID | Name
1  | Nabu
2  | Lapo
3  | Kim
3  | Bau

Table B (Ex. Dog's characteristics):
ID | AID | BID | BV
1  |  1  |  1  |  1
2  |  1  |  2  |  1
3  |  1  |  3  |  0
4  |  2  |  1  |  1
5  |  2  |  2  |  0
6  |  2  |  3  |  1
7  |  3  |  1  |  0
8  |  3  |  2  |  1
9  |  3  |  3  |  1
10 |  4  |  1  |  1
11 |  4  |  2  |  1
12 |  4  |  3  |  1

I need to make an "exact search" in A using the B characteristics fields

Ex.

(BID = 1 && BV = 1) AND (BID = 2 && BV = 1)  have to return A.ID = 1 & 4
(BID = 1 && BV = 0) AND (BID = 2 && BV = 1)  have to return A.ID = 3

How can i do this in 1 or less then one query each characteristic and a comparaction!?

Thanks!

One separate query each Dog Characteristic! Or having to check each characteristic for each dog and search for a right answer! — DigitalDaigor
– DigitalDaigor, Commented Feb 11, 2014 at 13:33

ModulusJoe · Accepted Answer · 2014-02-11 13:42:46Z

2

If you join the table on itself:

SELECT A.id FROM A 
LEFT JOIN B as B1 on A.ID = B1.AID
LEFT JOIN B as B2 on A.ID = B2.AID and B1.ID != B2.ID
WHERE
(B1.BID = 1 && B1.BV = 1) AND (B2.BID = 2 && B2.BV = 1)

You should be able to find all rows in A where you two matching entries from B where the B.IDs are different.

edited Feb 11, 2014 at 13:42

answered Feb 11, 2014 at 13:14

ModulusJoe

1,45411 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

DigitalDaigor Over a year ago

That's awesome, works grate! I really forget to test inception-join! Thank!

ModulusJoe Over a year ago

Interestingly there is actually an issue with this solution, it will only return the lower of the two IDs that match. Alternatively maybe I dont understand the logic for 1 and 4 being returned for the first query. Hopefully the idea itself helpd though.

DigitalDaigor Over a year ago

I have removed the "and B1.ID < B2.ID" and in my little test works great! I have to test in some bigger and a peaty much real example this night!

DigitalDaigor Over a year ago

The bigger problem will be if I have 10 or more different characteristics, this mean re-join the same table 10 or more times. But maybe is not a big problem...

ModulusJoe Over a year ago

In that situation the suggestion with the solution provided by @GordonLinoff may work better.

|

Gordon Linoff · Accepted Answer · 2014-02-11 13:13:24Z

1

You can do this with aggregation and a having clause:

select AID
from table B
group by AID
having sum(BID = 1 AND BV = 1) > 0 and
       sum(BID = 2 AND BV = 1) > 0;

Each condition in the having clause counts the number of rows that meet one of the conditions. Your logic requires that both be present, so the number of rows needs to be bigger than 0.

answered Feb 11, 2014 at 13:13

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

MySQL Select multiple rows from one row in 2 different tables

2 Answers 2

6 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related