0

i just learn about how can i get latest inserted id in relation ship with this reference, in database i have two table as channel_content_type and channel_plain_text_container, each channel_plain_text_container row referenced to channel_content_type with content_id column on id of channel_content_type

that means channel_content_type have more row on channel_plain_text_container as has many, now how can i find lastest inserted id in that?

this sql command is not correct and return no any row 

SELECT ptext.id
FROM channel_content_type content
JOIN channel_plain_text_container ptext ON (content.id = ptext.content_id)
LEFT OUTER JOIN channel_content_type p2 ON (content.id = p2.id)
WHERE p2.id IS NULL

Thanks in advance

DolDurma
  • 15,753
  • 51
  • 198
  • 377

1 Answers1

0

You can sort your result by the Id on a descending order and get the top row:

SELECT  ptext.id
FROM channel_content_type content
JOIN channel_plain_text_container ptext ON (content.id = ptext.content_id)
LEFT OUTER JOIN channel_content_type p2 ON (content.id = p2.id)
WHERE p2.id IS NULL
Order by ptext.id desc

In the above query the Order by ptext.id desc sorts the result by the id, in a descending order. The firs row in the result set contains the result that you expect.

Sparrow
  • 2,548
  • 1
  • 24
  • 28
  • I made a change in the query. Please try it again – Sparrow Aug 04 '17 at 21:16
  • Does your original query return any results? – Sparrow Aug 04 '17 at 21:18
  • soooo...when you add the last line (order by ptext.id desc), the result is null but without that line, it is returning some data? really? Does the query generate any errors? – Sparrow Aug 04 '17 at 21:25
  • command work fine without any problem, i think problem is `WHERE p2.id IS NULL`, because after remove this line from command i get result – DolDurma Aug 04 '17 at 21:39
  • Since I don't know anything about your DB and the logic behind the query, I can't comment about the where clause. My solution to your question remains the same... order it by Id in a descending order and read the first row. Best of luck! – Sparrow Aug 04 '17 at 21:41