CODEWITHSUNDEEP
mysqljoin
King Julien
King Julien

Reputation: 11298

Join two mysql tables

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?

Upvotes: 1

Views: 380

Answers (2)

Kolyan
Kolyan

Reputation: 1

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

Upvotes: 0

bensiu
bensiu

Reputation: 25564

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

Upvotes: 1

Related Questions