I have a table design handling 'notes' for various entities.
The notes table have a primary key, notes_id, a note field and a created_by date field.
For an entity, say customer, a relational table exists, customer_notes, containing two id's, the customer_id and the note(s)_id. I want to select all notes that are related to one customer, and sort based on the notes dates.
Being a SQL beginner, how to get this going?
Currently I have:
SELECT * FROM note
IN (SELECT * FROM customer_note
WHERE customer_id = 11)
Your query is wrong because you are including all columns in the subquery and not just the note id.
You are better off joining the tables together, like so:
SELECT -- specify whichever columns you want from either table here n.id n.note, n.note_date FROM note n -- n is the alias for the note table INNER JOIN customer_note cn -- cn is the alias for the customer_note table ON (cn.note_id = n.id) -- These are the columns that the two tables share / join on WHERE cn.customer_id = 11 -- Your filter criterion ORDER BY note_date DESC; -- Sort on note_date (DESC or ASC)