Totte Karlsson Totte Karlsson - 1 year ago 64
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:

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 Source

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
   note n  -- n is the alias for the note table
   customer_note cn  -- cn is the alias for the customer_note table
   ON (cn.note_id =  -- These are the columns that the two tables share / join on
   cn.customer_id = 11  -- Your filter criterion
  note_date DESC;  -- Sort on note_date (DESC or ASC)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download