Totte Karlsson Totte Karlsson - 5 months ago 8
SQL Question

One to many query using third table SQLite

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
WHERE note.id
IN (SELECT * FROM customer_note
WHERE customer_id = 11)


and 11 is a customers id. The above gives me an 'only a single result for a select that is part of an expression' error.

There are many questions here about similar things but most deal with only two tables.

Answer

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)