How to tell in a Query I don't want duplicates?

Go To StackoverFlow.com

0

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.

2012-04-04 18:31
by user519753
For two Red entries you have different Feeldesc values, so which one you want to return - sll 2012-04-04 18:34
What would you like to happen with the result above? Since 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
well, which row do you want from the vegetable table?? - dotjoe 2012-04-04 18:35
The reason I can't decide a rule is that this is on a massive database with thousdands of entries and Feeldesc would be varchar - user519753 2012-04-04 18:37
@user519753 I think you missunderstood @Joachim Isaksson. For example the rule could be choose the FeelDesc for the MIN(VegetabkeID) or the MAX(VegetabkeID) - Conrad Frix 2012-04-04 18:41
@user519753 - Besides a database with thousands of entries is hardly massiveLamak 2012-04-04 18:44
For example: I have a red car. There are six red cars in the street. When I want to get my car, I could query the street with 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
Oh @Conrad Frix that would make sense thanks for simplifying what Joachim Isaksson was trying to say. would you have an example on going about this - user519753 2012-04-04 18:47
@user519753 Why don't you post your expected result - Mosty Mostacho 2012-04-04 18:54


1

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.

2012-04-04 23:06
by kgrittn


0

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 :)

2012-04-04 18:47
by BlindAndFurious
Ads