MySQL – Using LIMIT on Table with One-to-Many Relationship

join;MySQL

I have 3 tables (A, B and C) that connect 1:m:1. I need to JOIN the 3 tables and apply a limit not on the total number of records, but on the number of records used from table A. This means that more records in total than the limit will be returned.

Here is a fiddle. This uses MySQL of:

SELECT A.id as Aid, A.name, C.id AS Cid, C.item
FROM A
INNER JOIN B b ON A.id = B.Aid
INNER JOIN C c ON B.Cid = C.id
WHERE A.owner = 1
ORDER BY A.id DESC
LIMIT 3

I would like to limit the number of records used from table A to 3, even though that means that 4 records will result (the ones connected to Aid's of 4, 3 and 2) because there are multiple records in table C that relate to those 3 records in table A.

I am wondering if perhaps this needs to use a SELECT (that has a LIMIT of 3) inside another SELECT that joins to tables B and C, but I don't know how to do this.

Best Answer

Like:

SELECT Al.id AS Aid,
       Al.name,
       C.id AS Cid,
       C.item
FROM
  (SELECT A.id,
          A.name
   FROM A
   WHERE A.owner = 1
   ORDER BY A.id DESC
   LIMIT 3) AS Al
INNER JOIN B b ON Al.id = B.Aid
INNER JOIN C c ON B.Cid = C.id

fiddle

Related Solutions

MySQL – Get Column from Multiple Tables

If all the tables use the MyISAM Storage Engine and have the same table structure, I have some good news for you.

You can create a single table that consumes no additional space except a .frm file and some mapping info. The key is to take advantage of the MERGE (MRG_MyISAM) Storage Engine.

Here is how you can do this:

CREATE TABLE XMerge LIKE X1;
ALTER TABLE XMerge ENGINE=MRG_MYISAM
UNION=(X1,X2,X3,X4) INSERT_METHOD=LAST;

Using this method, you can query the 4 tables at the same time like this:

SELECT ReqF FROM XMerge WHERE EmpName='John';

Was that simple, or what ???

In your case, you have 75 tables. You would do this:

CREATE TABLE XMerge LIKE X1;
ALTER TABLE XMerge ENGINE=MRG_MYISAM
UNION=(X1,X2,X3,X4,X5,X6,X7,X8,X9,
X10,X11,X12,X13,X14,X15,X16,X17,X18,X19,
X20,X21,X22,X23,X24,X25,X26,X27,X28,X29,
X30,X31,X32,X33,X34,X35,X36,X37,X38,X39,
X40,X41,X42,X43,X44,X45,X46,X47,X48,X49,
X50,X51,X52,X53,X54,X55,X56,X57,X58,X59,
X60,X61,X62,X63,X64,X65,X66,X67,X68,X69,
X70,X71,X72,X73,X74,X75) INSERT_METHOD=LAST;
SELECT ReqF FROM XMerge WHERE EmpName='John';

The beauty of this is that creating a MERGE table takes milliseconds. Just make sure every table has an index on EmpName. Better to do 75 indexed lookups that 75 full table scans. If there is no index on EmpName, you need to do this:

ALTER TABLE X1 ADD UNIQUE KEY (EmpName);
ALTER TABLE X2 ADD UNIQUE KEY (EmpName);
.
.
.
ALTER TABLE X75 ADD UNIQUE KEY (EmpName);
CREATE TABLE XMerge LIKE X1;
ALTER TABLE XMerge ENGINE=MRG_MYISAM
UNION=(X1,X2,X3,X4,X5,X6,X7,X8,X9,
X10,X11,X12,X13,X14,X15,X16,X17,X18,X19,
X20,X21,X22,X23,X24,X25,X26,X27,X28,X29,
X30,X31,X32,X33,X34,X35,X36,X37,X38,X39,
X40,X41,X42,X43,X44,X45,X46,X47,X48,X49,
X50,X51,X52,X53,X54,X55,X56,X57,X58,X59,
X60,X61,X62,X63,X64,X65,X66,X67,X68,X69,
X70,X71,X72,X73,X74,X75) INSERT_METHOD=LAST;
SELECT ReqF FROM XMerge WHERE EmpName='John';

Give it a Try !!!

Having trouble with Merge Join with SSIS

Normally, I'd say check your data types and or casing but if the lookup works, then that doesn't seem as probable. Safe to assume the lookup would be against the 7.8M row table b? Check your metadata anyway for the 4 keys on both inputs, maybe there's a length discrepancy

Best Answer

Related Solutions

MySQL – Get Column from Multiple Tables

Having trouble with Merge Join with SSIS

Related Question