CODEWITHSUNDEEP
sqlsqlite
Totte Karlsson
Totte Karlsson

Reputation: 1341

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.

Upvotes: 0

Views: 75

Answers (4)

Adrián
Adrián

Reputation: 419

Another option is to rewrite query as:

SELECT *
FROM   note
WHERE  note.id 
IN     (SELECT note_id
        FROM   customer_note
        WHERE  customer_id = 11)
ORDER  BY note_date DESC;

Upvotes: 1

nasukkin
nasukkin

Reputation: 2540

Welcome to the world of SQL :)

Firstly, the query that you were trying to write could work with a little adjustment.

SELECT n.*
FROM   note n
WHERE  n.id IN (
  SELECT DISTINCT cn.note_id
  FROM   customer_note cn
  WHERE  cn.customer_id = 11
)

... However, while the above would work, I think you're better off learning about JOINs. Inner-joins, in particular, which is probably the most common type of join you'll encounter and exactly what you need in this case. 

SELECT n.*
FROM   note n
JOIN   customer_note cn
ON     cn.note_id = n.id
WHERE  cn.customer_id = 11

This syntax is much more natural to read and understand, and it performs exactly what you need. You can use JOIN to produce a cartesian product (or "combination") between two tables. The ON statement directly following the JOIN table definition allows you to filter the cartesian product on the provided criteria. In this case, you're interested in where the customer_note table aligns with notes by the notes' ids. Finally, the WHERE statement allows you to only select based on the customer_note entries for a specific ID.

(Note that in SQLite, INNER-JOIN is so common that you can simply say JOIN rather than INNER JOIN. This is syntactic sugar common to most modern SQLs and reduces line noise.)

And, since you asked about sorting the results, we can take the final query one step further and do that.

SELECT    n.*
FROM      note n
JOIN      customer_note cn
ON        cn.note_id = n.id
WHERE     cn.customer_id = 11
ORDER BY  n.note_date ASC

Upvotes: 0

Totte Karlsson
Totte Karlsson

Reputation: 1341

Based on Nicarus answer, I got the following working too:

SELECT * 
FROM note 
WHERE note.id 
IN (
  SELECT note_id 
  FROM customer_note 
  WHERE customer_id = 11
) 
ORDER BY created_on

Using INNER JOIN however, seem cleaner.

Upvotes: 0

Nick
Nick

Reputation: 7431

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)

Upvotes: 0

Related Questions