Join two mysql tables

Question

I have two mysql tables that have the following structure:

Table 1:

---ID---------NAME-------
---1----- page name 1 ---
---2----- page name 2 ---
---3----- page name 3 ---

Table 2:

----ID---PAGE ID---------NAME------
-----1-----1-------- page name 1 ---
-----2-----2-------- page name 2 ---
-----3-----3-------- page name 3 ---
-----4-----1-------- page name 1 ---
-----5-----2-------- page name 2 ---
-----6-----3-------- page name 3 ---
-----7-----1-------- page name 1 ---
-----8-----2-------- page name 2 ---
-----9-----3-------- page name 3 ---

As you can see in table 2 each page is mentioned several times.

I want to join Table 1 on Table 2 only with the newest records in Table 2, in this case the newest three records only! ("newest" means records with higher IDs) Is that possible using mysql?

What defines "newest record"? The ones with the highest IDs? A date/time field you've not listed? — Marc B
– Marc B, Commented Mar 15, 2011 at 14:30
Define "newest". I cannot see any timestamp in Table2 (that is not really your table name, is it?) — user330315
– user330315, Commented Mar 15, 2011 at 14:30
And also which field in Table2 is the foreign key that relates it to Table1? — AbiusX
– AbiusX, Commented Mar 15, 2011 at 14:31
What's the difference between Table1.Name and Table2.Name? Is that data just duplicated? — Ocaso Protal
– Ocaso Protal, Commented Mar 15, 2011 at 14:36

bensiu · Accepted Answer · 2011-03-15 14:34:55Z

1

Table2

----ID---PAGE ID----
-----1-----1-------- 
-----2-----2-------- 
-----3-----3-------- 
-----4-----1-------- 
-----5-----2-------- 
-----6-----3-------- 
-----7-----1-------- 
-----8-----2-------- 
-----9-----3--------

and

SELECT Table1.ID, PAGE_ID, NAME
FROM Table1
LEFT JOIN Table2 ON Table1.ID = Table2.PAGE_ID
WHERE ... put your condition for latest if other when highest ID
ORDER BY Table1.ID DESC
LIMIT 3

answered Mar 15, 2011 at 14:34

bensiu

25.7k56 gold badges81 silver badges117 bronze badges

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

Comments

Kolyan · Accepted Answer · 2011-03-15 14:40:34Z

0

SELECT Table1.ID, PAGE_ID, NAME 
FROM Table1 
LEFT JOIN Table2 ON Table1.ID = Table2.PAGE_ID 
WHERE Table2.ID = MAX(Table2.ID) 
GROUP BY Table1.ID 
LIMIT 3

should work

answered Mar 15, 2011 at 14:40

Kolyan

1

Collectives™ on Stack Overflow

Join two mysql tables

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related