![]() ![]() On a side note, looking at the ERD, you are indicating that student table has one-to-many relationship with gender table and race table, so one student can have multiple different genders or races? or should it be one race or gender can correspond to multiple students? or are you better off just combining all into one table, race and gender are attributes about a student, then you don't even have to do a join? These are interesting questions to consider. None of above is really needed in this case. Scalar: it is used when you want to generate a scalar value to be.Temporary table sitting on top of the larger query, it's embedded Inline: it is used in the same way as the WITH.Nested: it is used when you'd like the temporary table to act as aįilter within the larger query, and it often sits within the WHERE.Table from an existing table and place it at the top of the larger WITH: this is used when you'd like to pseudo-create a temporary.WHERE g.Gender_ID = s.gender_id AND r.Race_ID = s.race_id (s.first_name || ' ' ||s.last_name) AS Student_Name,įROM Student AS s, Gender AS g, Race AS r If you just don't want to use INNER JOIN, but achieve the result as INNER JOIN, using a CROSS JOIN with WHERE clause does the job as well. Also, subquery is usually used when there are more layers to the question rather than a case of just fully stitching tables together. Because what you are trying to achieve is to create a joint view of multiple tables fully stitched together using a common key, which is exactly what JOIN is for. Your original query is the most straight-forward way. How can I get the same result using a subquery instead of using inner join? **Desired Output using subquery below: ** Insert into Student values(4, 'Cindy', 'Williams', 2, 2) īelow is the ERD relationship diagram in case if you need to take a look: Insert into Student values(3, 'Sam', 'Crawford', 3, 5) NUMXLOGINSERTLOCKS) or because we are waiting for another backend to finish copying a WAL record into wal. LWLock:WALInsert can be reported because there are not enough WALInsert locks (c.f. Insert into Student values(2, 'Sara', 'Lee', 2, 3) In the latter case we are not actually waiting to acquire the lock, we are waiting for the lock to be released without then acquiring it. Insert into Student values(1, 'John', 'Smith', 1, 1) Insert into Race values(4, 'Native American') I want to know how I can get the same result using a subquery instead of using inner join on PostgreSQL?īelow are my code for the three tables (Student, Gender, Race) that I have created: create table Student(Student_ID int primary key, first_name varchar(30), last_name varchar(30), gender_id int, race_id int, constraint gender_id_fk foreign key(gender_id) references gender(gender_id), constraint race_id_fk foreign key(race_id) references race(race_id)) Ĭreate table Gender(Gender_ID int primary key, Gender varchar(30)) Ĭreate table Race(Race_ID int primary key, Race varchar(30)) Inner join race on student.race_id = race.race_id Inner join gender on student.gender_id = gender.gender_id If it's not for some reason to do with SQL's deeper architecture, I'd like to understand why.I am joining two tables with the student table using the inner join below: select (first_name || ' ' ||last_name) as "Student Name", gender, race from student This feels like a more correct & less wasteful way to do it. In the past I've done operations like this by doing two SELECTS, one of which is against the item_templates table, the other against the unique_items table, and taken their UNION. ![]() Or if this is something you can't do conceptually - have each row choose its own join - I'd love to get a link to a deeper explanation. If there's a quick fix for the syntax, I'd love to hear it. This is the sort of thing I've been trying, without success: SELECT item_table, item_id, * So when I find a record of an item in the item_instances table, I want to look up more information about it, and I need to pull that info from the correct table. They are now stored in the unique_items table. Say I've got a table of item_instances in my game, of all the items in the game world, and some of the items are cookie-cutter copies of generic items from the item_templates table, and other items started off as templates, and then acquired unique properties as players used them. I'm attempting to use a CASE expression to select which table each row should be joined with. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |