37
linuxxx
6y

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

Comments
  • 7
    It's just Venn diagrams mate 😂
  • 5
    @epse I'm trying to do some join now but I'm lost xD
  • 3
    Good to know I'm not the only one with that problem. ^^
  • 2
    Where are you getting lost?

    Select
    table1.field,
    table2.field
    From table1
    Join table2
    on (table1.id = table2.relatedField)
    Where table1.id = 123;
  • 3
    @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.
  • 3
    @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.
  • 0
    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.
  • 3
    @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 :)
  • 1
    @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.
  • 1
    @rEaL-jAsE Not really tbh, that's one of my issues as well :/
  • 4
    Glad I'm not the only one, sad you're struggling. Hope you get an epiphany and understand it soon.
  • 3
    @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
  • 1
    @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.
  • 0
    @linuxxx , there must be a typo of table name "notes"
  • 1
    @kiru15 I checked that about 10 times :P
  • 1
    @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
  • 2
    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.
  • 2
    @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!
  • 1
    @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
  • 1
    @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)
  • 1
    @billgates @linuxxx , oops! my bad. Ya It should be nt.tagid
  • 2
    @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 😅
  • 0
    @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
  • 1
    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.
  • 1
    @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)
  • 0
    @xewl isnt grouping for aggregations like count and min max? Not for returning actual data.
  • 1
    @linuxxx sorry i meant like a screenshot of the current data in notes_tagged
  • 1
    @billgates A screen picture but fuck it haha
  • 0
    @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
  • 0
    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 :)
  • 0
    @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
  • 1
    @billgates tell that to my polymorphic stock system >;-)

    Edit: Come to mention, I might've done the GROUP BY inside an inner select :/
  • 0
    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.
  • 1
    @Jilano way cool picture bro, that actually worked for me! Thank you!!!!
  • 0
    This may help you ;P
  • 0
    *stretches* good morning ranters.
    8 hours later and still no result 😦
  • 0
    @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.
  • 0
    So uch for testing the combined way. SQLIte apparently does not support OUTER joins...
  • 0
    Below is brute force way with 2 seperate queries.

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

    public List<Item> GetItemsForTag(int tagId)
  • 0
    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
    )
  • 0
    Strange isnt this the same as OUTER JOIN? Why SQLite complain....
Add Comment