WHERE EXISTS (SELECT * FROM table_2 t2 WHERE t2.date = t1. IN is very problematic when date could potentially be NULL so if you don't want to use a JOIN then I would suggest EXISTS. So my question is are you allowed to have multiple lateral joins. I now get 25 results for both reviews and publications with repeated/duplicated data. FROM thorpublication ap WHERE ap.authorid a.id LIMIT 5) r ON TRUE. WHERE t1.date IN (SELECT date FROM table_2) This returns the dataset with only 5 reviews - but if I add a second lateral query. If you want to keep the way you had it which typically EXISTS would be better than IN but to to use IN you need an actual SELECT statement in your where. SELECT GENERATE_SERIES('', '', '1 day'::INTERVAL) AS date I prefer INNER JOIN over IN or Exists so here is a syntax that should work with a JOIN: WITH table_1 AS ( Below is a simplified outline of what I am attempting to do: breed table (ID, BreedName) animal table (ID, breedID, breed2ID) SELECT animal.ID, breed.BreedName FROM animal LEFT JOIN breed ON WHERE animal.ID7 What I need to do is also get the BreedName to join for animal. WHERE date IN table_2 is not valid because you never actually reference a value/column from table_2. Note however that you had a second syntax issue in regards to your WHERE statement. In terms of your actual query this syntax should work in PostgreSql, Oracle, and sql-server, well the later typically you will proceed WITH with a semicolon ( WTIH), but that is because typically sql-server folks (myself included) don't end previous statements which need to be ended prior to a CTE being defined. The we get person names participating as team members:įinally you use IFNULL(p.name, p2.name) to select the person name as an individual or team member.Per the other comments the second Common Table Expression is preceded by a comma not a WITH statement so WITH cte1 AS (SELECT.) participant_type = 'P' ) LEFT OUTER JOIN teamPersons tp ON (c. Then we get ID of persons participating as team members: id įirstly you get names of individuals by joining ContestParticipant and Person tables, but you use LEFT OUTER JOIN so Team rows remains: participant_type = 'T' ) LEFT OUTER JOIN persons p2 ON tp. make those two fields this new tables primary key. remove products from the bill table, create a new table called 'billproducts' with two fields: one pointing at products, one pointing at bill. It is possible to get the same result without UNION: How do you create the table structure in PostgreSQL to make a many-to-many relationship. So how to get all persons participating in the contest in a single SQL query? Team 1 and Steve participate in contest INSERT INTO contestParticipants VALUES ( 1, 'T' ), ( 3, 'P' ) Dan and Tom are in Team 1 INSERT INTO teamPersons VALUES (1, 1 ), (1, 2 ) Sample Data: - There are 3 persons: Dan, Tom and Steve INSERT INTO persons VALUES ( 1, 'Dan' ), ( 2, 'Tom' ), ( 3, 'Steve' ) Type 'P' points to Person, type 'T' to teamPersons (team_id) CREATE TABLE contestParticipants (participant_id INT, participant_type CHAR ) Persons who participate as a team CREATE TABLE teamPersons (team_id INT, person_id INT ) Persons who can participate as individuals or team members CREATE TABLE persons (id INT, name VARCHAR (30 ) ) Contest table points either to Team or Person table depending on the participant type: Sometimes in a single query, it is required to join different tables based on a condition in one of the tables.įor example, you need to get all persons participating in a contest as individuals or as members of a team.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |