God damnit, me and mysql joins do NOT work together that well, this shit is like magic to me 😅

It's just Venn diagrams mate 😂

@epse I'm trying to do some join now but I'm lost xD

Good to know I'm not the only one with that problem. ^^

Where are you getting lost?

Select
table1.field,
table2.field
From table1
Join table2
on (table1.id = table2.relatedField)
Where table1.id = 123;

@C0D4 I've got one table which links noted and tags together. I can already select all note things (title/id/content/creation time) based on a tag id but I can't seem to also get all tag titles which belong to a note which I'm selecting based on one tag which is linked to a note.

@linuxxx if I read that right, are you trying to return both the notes and all the tags (I’m expecting many tags per note relation) for a single note?

You might need to select the note details, JOIN to tags table, and group_concat() the tag names in your select and then group your entire query by note Id, if you’re trying to do this all in 1 query.

With the group_concat, you can process that data back out as a comma list server side.

Yeah, if there's one place I prefer abstractions it's over that stuff. I'm a fan of LINQ-queries for those things, with extensions methods in C# you can get code that is neat, easy to read, and really powerful. Sometimes the abstraction will come back to haunt you, but for most cases it's awesome.

@C0D4 I'm still not really discovering how this works haha.

I've got:
Notes (note id, user id, title etc)
Tags (tag id, user id and title)
Notes_tagged (note id, tag id)

I've got to get all note details and tag titles based on one tag :)

@rEaL-jAsE It's not just reading stuff, I've got to understand it as well. I still have troubles understanding this shit after 8 years.

@rEaL-jAsE Not really tbh, that's one of my issues as well :/

Glad I'm not the only one, sad you're struggling. Hope you get an epiphany and understand it soon.

I can't find the exact picture that I had in mind, but hope it'll help a little:

@linuxxx
Not sure is this the optimized way or not
SELECT n.* , t.* from notes_tagged nt
INNER JOIN notes n ON n.id = nt.noteid
INNER JOIN tag t ON t.id= nt.tagid
Where t.id = something

@rEaL-jAsE It's approximately that yes haha, @Jilano no offense but I don't understand those 😅

@kiru15 I ran your query (trying to research why it doesn't work now) but getting a "unknown table notes" now :P
Problem is that I can hardly grasp what's going wrong but I'll keep trying.

@linuxxx , there must be a typo of table name "notes"

@kiru15 I checked that about 10 times :P

@linuxxx your query doesn't make sense?

You want to get all notes for a tag and then for each note get its tags.

What's the use case for that?

You could split it into 2 queries

Select n.id from Notes n
join Map m ON n.id = m.NoteId
Where m.tagId = ...

Select * from Map m
join Tag t on t.id = m.tagId
where m.noteId IN (result above)

I think you can insert the first select into the () but that is the inelegant bruteforce way.

Maybe need to use OUTER JOIN to combine these 2 but essentially this is what u want

I can relate. Straight forward joins are, well, straight forward, but I can spend hours trying to make a view of table1 joined with the lowest value of table2.column1 within the group of matches from table1.c2 = table2.c2.

You'd think it would just be a matter of order by, distinct/group by, but in that specific task I found out my "lowest of" join failed due to mariadb doing some magic with nested subqueries. Even the official docs propose workarounds that border on looking like hacks.

@billgates The use case is that a user can get all notes (including tags belonging to those notes) based on a tag.
I'll try them, thanks!

@kiru15 Where should be nt.TagId not t.id i think, you dont want to involve the tag table in the where, just want the details if there is an entry in nt with that tagId

@billgates It's coming close but it's only getting the title of one tag, not all linked tags (notes_tagged has a note id and a tag id)

@billgates @linuxxx , oops! my bad. Ya It should be nt.tagid

@kiru15 No worries, already trying that but I'm now facing the problem that I'm only getting the title of the tag in doing the WHERE with while I need to get the titles of all tags linked to all notes 😅

@linuxxx the 2nd query? I cant really tell without seeing the data in the tables.

You have one note right now with multiple tags linked (multiple entries in the nt table for a single NoteId). Do you have any other in notes, etc in the db?

Try just the 2nd query with a soecific note id with multiple tags/entries in nt table

I've been doing joins for years now. GROUP_CONCAT sure's a way to go.

a tip:
GROUP BY notes.id

When you join, especially on pivots, you might get double results which of course you won't need, when you GROUP_CONCAT() stuff.

@billgates It's like this:
Notes:
Note-id
Note-title
Note-content
Note-creation-date

Tags:
Tag-id
Tag-title

Notes_tagged:
Note-id
Tag-id

(also user id's involved and column names are different but that doesn't matter in this context)

@xewl isnt grouping for aggregations like count and min max? Not for returning actual data.

@linuxxx sorry i meant like a screenshot of the current data in notes_tagged

@billgates A screen picture but fuck it haha

@linuxxx hm... well if you start with my 2nd query and use Where in (1005) u should get the rows u match here along with their tag info so i think 4 rows.

Or are you using kirus. KirU i think on of the joins should be an Outer

g2g but good news is actually im gonna be doing something like this too whenever i get time. So whoever solves it first gives the other guy the answer :)

@rEaL-jAsE You're right, my bad

@linuxxx No worries, haha
Everyone has its own way if understanding things. What works for some might not for others

@Jilano actually its none of these. just realized these dont have the mapping table in the middle

The query he would need Has to be 2 joins using the mapping table in between

@billgates tell that to my polymorphic stock system >;-)

Edit: Come to mention, I might've done the GROUP BY inside an inner select :/

SQL "join" for dummies: Usually you don't need inner, outer, left, right, roundabout, whatever. Just write "select <columns> from <table1> join <table2> on <table1>.<column>=<table2>.<column>" where the two "<column>"s are the ones that should have matching values.

@Jilano way cool picture bro, that actually worked for me! Thank you!!!!

This may help you ;P

*stretches* good morning ranters.
8 hours later and still no result 😦

@jeremyIglehart Well, you're welcome lad!

@C0D4 Hello! :D

@linuxxx
What your asking for is a bi-directional join.

Using the notes_tagged table to lookup both notes and tags at the same time, that’s fine (despite the number of joins your going to need) which is were I think your getting confused.

Think of mysql as a one way road to a data set, your trying to get to the end of that road (from tags to notes_tagged to notes), and then turn around and head back down it to identify all the related tags per note found.

I would recommend breaking this into two queries, 1 per direction. But if you want it in 1 query, you need to walk through the 5... joins (I’m still asleep and walking to work) for this scenario,l ultimately joining into the notes_tagged table twice, 1 for notes and 1 for tags, because your changing the context of the bridged table, plus any additional joins for users and other tables as you mentioned as required.

So uch for testing the combined way. SQLIte apparently does not support OUTER joins...

Below is brute force way with 2 seperate queries.

https://github.com/allanx2000/...

public List<Item> GetItemsForTag(int tagId)

SELECT i.*, t.*
FROM tbl_item_tags m
JOIN tbl_tags t on t.tag_id = m.tag_id
JOIN tbl_items i on m.item_id = i.item_id
WHERE m.item_id IN (
SELECT m2.item_id
FROM tbl_item_tags m2
WHERE m2.tag_id = 1
)

Strange isnt this the same as OUTER JOIN? Why SQLite complain....