So I got this query and it's pulling from tables like this:
Plantation TABLE
PLANT ID, Color Description
1 Red
2 Green
3 Purple
Vegetable Table
VegetabkeID, PLANT ID, Feeldesc
199 1 Harsh
200 1 Sticky
201 2 Bitter
202 3 Bland
and now in my Query I join them using PLANT ID ( I Use a left join)
PLANT ID, Color Description, Feeldesc
1 Red Harsh
1 Red Sticky
2 Green Bitter
3 Purple Bland
So the problem is that in the Query you can see Red shows up twice! I can't have this, and I'm not sure how to make the joins happen but stop reds from coming up twice.
Red Harsh
and Red Sticky
are equally correct and you want to eliminate one, you need to decide on a rule - Joachim Isaksson 2012-04-04 18:34
MIN(VegetabkeID)
or the MAX(VegetabkeID)
- Conrad Frix 2012-04-04 18:41
SELECT car FROM street WHERE color = 'red'
, but that would yield six cars. So I'dd have to add another condition to the WHERE clause: e.g. pick the biggest, most expensive, oldest, or the one with the lowest numbered licence-plate - wildplasser 2012-04-04 18:44
It seems remotely possible that you're asking how do to group indication -- that is, showing a value which identifies or describes a group only on the first line of that group. In that case, you want to use the lag() window function.
Assuming setup of the schema and data is like this:
create table plant (plantId int not null primary key, color text not null);
create table vegetable (vegetableId int not null, plantId int not null,
Feeldesc text not null, primary key (vegetableId, plantId));
insert into plant values (1,'Red'),(2,'Green'),(3,'Purple');
insert into vegetable values (199,1,'Harsh'),(200,1,'Sticky'),
(201,2,'Bitter'),(202,3,'Bland');
The results you show (modulus column headings) could be obtained with this simple query:
select p.plantId, p.color, v.Feeldesc
from plant p left join vegetable v using (plantId)
order by plantId, vegetableId;
If you're looking to suppress display of the repeated information after the first line, this query will do it:
select
case when plantId = lag(plantId) over w then null
else plantId end as plantId,
case when p.color = lag(p.color) over w then null
else p.color end as color,
v.Feeldesc
from plant p left join vegetable v using (plantId)
window w as (partition by plantId order by vegetableId);
The results look like this:
plantid | color | feeldesc
---------+--------+----------
1 | Red | Harsh
| | Sticky
2 | Green | Bitter
3 | Purple | Bland
(4 rows)
I had to do something like the above just this week to produce a listing directly out of psql which was easy for the end user to read; otherwise it never would have occurred to me that you might be asking about this functionality. Hopefully this answers your question, although I might be completely off base.
Check array_agg function in the documentation it can be used something like this:
SELECT
v.plantId
,v.color
,array_to_string(array_agg(v.Feeldesc),', ')
FROM
vegetable
INNER JOIN plant USING (plantId)
GROUP BY
v.plantId
,v.color
or use
SELECT DISTINCT
v.plantId
,v.color
FROM
vegetable
INNER JOIN plant USING (plantId)
disclaimer: hand written, syntax errors expected :)
Red
entries you have differentFeeldesc
values, so which one you want to return - sll 2012-04-04 18:34