Programming Class
>1st year student
>can't find this anywhere
>assignment due tomorrow morning
it seems simple, but i can't get it right. i don't even think there's a way to do it, but i figured i would check here before i give up and move on. i asked this question on 'yahoo answers' and a couple other sites, but no replies except one guy who said it isn't possible. i even tried going on that shithole khan academy, which, by looking through the comments, is a bigger waste of time than i ever thought it would be. ok, here's my problem:
i am trying to make it so both hobby names show up in the same row next to each other instead of having a separate row for each hobby name; below i show(stripped down version for simplicity) what i wrote to accomplish this, then my result, and finally what i actually want the result to be. i've tried everything i know and can't get the desired result. how do i do this?
CREATE TABLE famefags
(id INTEGER PRIMARY KEY AUTOINCREMENT,
fullname TEXT,
age INTEGER);
INSERT INTO famefags (fullname, age) VALUES ("Taylor Swift", "25");
INSERT INTO famefags (fullname, age) VALUES ("Scarlett Johansen", "35");
CREATE table hobbies (
id INTEGER PRIMARY KEY AUTOINCREMENT,
person_id INTEGER,
name TEXT);
INSERT INTO hobbies (person_id, name) VALUES (1, "drawing");
INSERT INTO hobbies (person_id, name) VALUES (1, "heiling");
INSERT INTO hobbies (person_id, name) VALUES (2, "dancing");
INSERT INTO hobbies (person_id, name) VALUES (2, "coding");
SELECT famefags.fullname, hobbies.name, hobbies.name AS hobby2 FROM hobbies JOIN fame ON famefags.id = hobbies.person_id;
it shows up as:
fullname name hobby2
Taylor Swift drawing drawing
Taylor Swift heiling heiling
Scarlett Johansen dancing dancing
Scarlett Johansen coding coding
i want it to show up like this:
fullname name hobby2
Taylor Swift drawing heiling
Scarlett Johansen dancing coding
here's an image. this isnt the exact same thing i'm doing. i just made it short so it would be easier and only focus on my specific problem
u so wrong, wait a minute, i will try something
if you can't figure out an outer join its time for a helium bag my nigga
it's not the best solution but it works :)
SELECT f.fullname, h.name, hbis.name AS hobby2 FROM hobbies h, hobbies hbis, famefags f where f.id = h.person_id and f.id = hbis.person_id and h.id != hbis.id and h.id < hbis.id ;
forgot to mention, i must have 2 separate rows. so, i can't just put something like "name2" for the 2nd hobby and have only one row of inserts for each person. there has to be separate inserts for each hobby
>>17850314
i tried that but i always wind up with a null result somewhere
>>17850332
doing this now. ty
>>17850332
it worked, but i don't understand exactly what you did. what am i missing? where does it explain this? we've never been taught anything about an exclamation point in JQ either. maybe i just need more time to look it over and tell what's going on. only looked at it for a few seconds.
>>17850423
use this instead : SELECT f.fullname, h.name, hbis.name AS hobby2 FROM hobbies h, hobbies hbis, famefags f where f.id = h.person_id and f.id = hbis.person_id and h.id < hbis.id ;
the "!=" mean "different".
the part with the exclamation is useless because we already spot the different row with "<"
>>17850435
ok, so "!=" is like "not equal to", right?
thanks a lot. probably saved me having to stay up for hours and maybe not ever figuring it out. i get it now. i forgot the space could be used as "AS" and was confused at first. still not quite sure about everything t but i'll figure it out. ty. rather spend time doing this figuring it out and learning than just stuck. thanks again
>>17850435
alright, i get what you did. i understand what's going on. thanks. really appreciate it.
>>17850435
fuck, i am stupid. never thought of splitting them that way with > or <, but now i have to use what you did and figure out how to use a "join" somewhere in there to do it. kek, i forgot. the whole point of this is to do it using some sort of join. should be easy now though. thanks again
>>17850480
you're welcome, maybe you could use a solution with a JOINTURE but I not really used to this.
My solution work because you wanted 2 different result , so you have to make 2 different variable in my case "h" and "hbis".
These variables need to have 2 different id , is the reason why is use "h.id < hbis.id" otherwise we will get the same result two time.
>>17850508
yes, this is good. ty.
>>17850508
was easy. again ty
SELECT f.fullname, h.name, hbis.name AS hobby2 FROM famefags f JOIN hobbies h JOIN hobbies hbis ON f.id = h.person_id AND f.id = hbis.person_id AND h.id < hbis.id;
>>17850614
you're welcome :) next time try to ask on stackoverflow
>>17850631
i made an account there, b ut haven't asked a question in months, so i would have to ask for my password since i forgot it. then, i didn't think i would get an answer fast enough if i asked there. sometimes it takes a day to get an answer(which is cool. not complaining) so i came here hoping i would get lucky and get an answer in less than a half hour so i could get some sleep tonight.
>>17850631
next time i will tho