I have two tables, one listing categorization information, and another listing product information. They can be...
By submitting your personal information, you agree that TechTarget and its partners may contact you regarding relevant content, products and special offers.
joined by a category_id column that is present in both tables. Naturally there are multiple rows in the product table for each category_id. I would like to perform a simple join but returning only one product per category rather than all products with the duplication of category information. Also, some categories do not have any associated products, so I imagine a LEFT OUTER JOIN should be used. Hope this is clear, otherwise please let me know.
Very clear. And you were right about the LEFT OUTER JOIN.
select c.category_id , c.category_name , p.product_id , p.product_name from categories as c left outer join products as p on c.category_id = p.category_id and p.something = ( select max(something) from products where category_id = c.category_id )
What's the "something" column? Your call. Pick a column for which you know there will be only one highest value. The product primary key suggests itself. A "date_added" timestamp will also work. In data modelling terminology, you can use any candidate key for this purpose.
Dig Deeper on Oracle and SQL
Related Q&A from Rudy Limeback
Read an example of an SQL case expression from our SQL expert Rudy Limeback.continue reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback.continue reading
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clausecontinue reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.