Distinct record from 3 fields; not sure about SQL

Distinct record from 3 fields; not sure about SQL

Post by sb530 » Wed, 14 Jan 2009 19:08:50


I have 3 fields for categories, ie one company can have more than 1
category.

There are 64 categories in total.

One company could be dealing with catgeories 1 and 10; another
categories 20 and 30.

Thus for each record there are 3 fields for category data, If a
company decides that it deals wtih category 20 only, the other 2
fields are empty.

Campanies are situated at different region. For a given region I need
to come up with a sorted distinct list (ie no repeat) from these 3
fields.

I am not sure about SQL. Can SQL experts give me some tips.

Thank you.
 
 
 

Distinct record from 3 fields; not sure about SQL

Post by Luuk » Wed, 14 Jan 2009 19:29:37


XXXX@XXXXX.COM schreef:

I suggest you create a table with 2 fields, `company` and `categorie`

in your example above the 'one company' looks like:
`company` `categorie`
1 1
1 10
2 20
2 30

This table could also be a VIEW of your original table..

--
Luuk

 
 
 

Distinct record from 3 fields; not sure about SQL

Post by sb530 » Wed, 14 Jan 2009 19:56:23

My apology. The list I am supposed to come up is a distinct list of
categories for a given region.

Thanks.


> 1 > 10
> 2 gt;20
> > gt;30
>
> This table could also be a VIEW of your origin>l >able.>
>
> --
 
 
 

Distinct record from 3 fields; not sure about SQL

Post by Luuk » Wed, 14 Jan 2009 20:29:10


XXXX@XXXXX.COM schreef:
> My apology. The list I am supposed to come up is a distinct list of
> categories for a given region.
>
> Thanks.
>
>

top-post changed...

but i dont know how your database structure looks like...
Where is your region?

If its in you companies-table, than you can join that table with the
table is suggested, i dont see a problem with that....

--
Luuk
 
 
 

Distinct record from 3 fields; not sure about SQL

Post by strawberr » Wed, 14 Jan 2009 20:55:22


> >> 2 gt;gt;> 20
> >> > gt;gt;> 30
>
> >> This table could also be a VIEW of your origin>l >a>>e..
>
> My apology. The list I am supposed to come up is a disti>ct list of
> categories for a g>ven >egion.
> > > gt; Tha>ks>
>
>
>
> top>po>t changed...
>
> but i dont know how your database structu>e looks like...
> Where>is>your region?
>
> If its in you companies-table, than you can join tha> table with the
> table is suggested, i dont see a probl>m >ith t>at....
>
> --
> Luuk

Did you take Luuk's advice and normalise your data?
 
 
 

Distinct record from 3 fields; not sure about SQL

Post by Captain Pa » Wed, 14 Jan 2009 21:19:29


> > 2 gt;gt;20
> > > gt;gt;30
>
> > This table could also be a VIEW of your origin>l >a>le.. >>> > > --> > > Luuk
> My apology. The list I am supposed to come up is a distin>t list of
> categories for a giv>n >egion.
> >> Thanks.
>
Please do not top post (top posting fixed).
Luuk's advice is what you need to do. Normalise your database and the
problem is trivial.
 
 
 

Distinct record from 3 fields; not sure about SQL

Post by Michael Au » Thu, 15 Jan 2009 10:24:36


sounds like someone trying to get us to do his homework for him. I
can't imagine a real DBA asking such a simple question.

OP - do what the rest of us have had to do. READ, READ, READ and TEST.
There are more examples out there than you could read in several months...