• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 312
  • Last Modified:

MYSQL Selecting distinct records from two look up tables

Hi

table category - table filled with categories - for example 30 different categories
(catgoryID, category_name)

I have two look up tables

Table 1 - look up table stores the categories that EACH user can assign themselves to
(contains userID and CategoryID)
- Multiple users can assign themselves to multiple categories

Table 2 - look up table stories the the categories EACH resource can assign itself to
(contains resourceID and CategoryID)
- Multiple resources can be assigned to multiple categories

Is there one query which will pull back a list of the distinct categories that appear in either of the look up table.

For example there are 30 categories

Lookup table 1 has 10 records - only 6 of them are Unique categories

Lookup table 2 has 20 records - only 2 of theses are unique when you take into account the 6 found in the other table

in total I would like my query to return the 8 distinct categories out of the 30 in the categories table.

Hope that makes sense?

Thanks in advance.


0
bigMlittleC
Asked:
bigMlittleC
  • 3
  • 3
  • 2
  • +2
1 Solution
 
Rajkumar GsSoftware EngineerCommented:
select distinct catgoryID, category_name from category where catgoryID in
(
  select CategoryID from lookuptable1
  union
  select CategoryID from lookuptable2
)

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
if the uniqueness is per table, then this should do:

select c.*
  from category c
 where c.categoryID IN ( select t1.categoryid from table1 t1 group by t1.categoryid having count(*) = 1 )
     or  c.categoryID IN ( select t2.categoryid from table2 t2 group by t2.categoryid having count(*) = 1 )

Open in new window


if the uniqueness test must check table1 and table2:


select c.*
  from category c
 where c.categoryID IN ( 
   select categoryid from (
       select t1.categoryid from table1 t1 group by t1.categoryid having count(*) = 1 
     UNION ALL
       select t2.categoryid from table2 t2 group by t2.categoryid having count(*) = 1 
    )
  group by categoryid
  having count(*) = 1
 )

Open in new window


hope this helps, otherwise clarify
0
 
tigin44Commented:
a query like this will give the commonly used CategoryID values

SELECT L1.CategoryID
FROM lookup1 L1
    INNER JOIN lookup2 L2 ON L1.CategoryID = L2.CategoryID
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
jrm213jrm213Commented:
I think this should work

Select category.categoryID, category_name
from category inner join table1 on category.categoryID = table1.CategoryID
Union
Select category.categoryID, category_name
from category inner join table2 on category.categoryID = table2.CategoryID


by default the union statement returns only distinct results.

0
 
bigMlittleCAuthor Commented:
Thanks. I forgot about Union!

Many thanks to everyone else for your responses.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
huh? union will not return values that are "unique", but just return each value once ...
so, either you explained wrong, or you closed too fast ...
0
 
bigMlittleCAuthor Commented:
angelIII:

does the 'select distinct catgoryID' mean that only Unique values will be returned anyway?
0
 
jrm213jrm213Commented:
I think you guys are just mixing distinct and unique

distinct meaning it just returns 1 time even if there are more
unique meaning it only occurs once

When the author wrote
Lookup table 1 has 10 records - only 6 of them are Unique categories
I think most of us took "Unique" to mean "different" not categories only in the table once.
0
 
bigMlittleCAuthor Commented:
jrm213jrm213: - Thanks

Sorry everyone, my mistake with the original wording of the question. I just wanted the 'Distinct' Categories that occur in either of the two look up tables.

Sorry for the confusion.



0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
np.
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 3
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now