Complicated single table query

[claufranck]

My table structure is:
refsub (table name)
PROJET SUBSECTO

subsecto refsub (table name)
SUBSECTO LONGNAME

refchifrefsub (table name)
projet descript

refsub
Subsecto is a fk to a table that contains a list of subsectors
Projet is a FK that links the subsector to the project

subsecto
subsecto is a pk for the subsector
longname full subsector name

refchif
project is a pk for project number
descript description

refchif --> refsub <-- subsecto

A project can have multiple subsectors, but a query like this would't work

SELECT r.projet as project, descript as des FROM refchif r LEFT OUTER JOIN refsub rsb ON rsb.PROJET=r.PROJET LEFT OUTER JOIN subsecto ss ON rsb.SUBSECTO=ss.SUBSECTO WHERE ( (ss.subsecto like 'AGRIGENERA') and (ss.subsecto like 'LIVESTOCK_')) group by r.projet order by r.projet asc

using 'or' works

but several projects can be classified in several subsectors at once and I want to search by one or 2 or several of these subsectors for the same project number.

I know it very complex and a little vague I will provide anything you need to help me

 

[rac2]

You can use a list of values as a criterion.

SELECT r.projet as project, 
       descript as des 

FROM refchif r 
LEFT OUTER JOIN refsub rsb ON rsb.PROJET=r.PROJET 
LEFT OUTER JOIN subsecto ss ON rsb.SUBSECTO=ss.SUBSECTO 

WHERE ss.subsecto IN ( 'AGRIGENERA', 'LIVESTOCK' ) 

GROUP BY r.projet 
ORDER BY r.projet ASC

This is the same as

...

WHERE ss.subsecto =  'AGRIGENERA'
   OR ss.subsecto =  'LIVESTOCK'

...

I dont think you can use pattern matching in a list however.

If these are ad hoc queries and you just need to get the data then you can write a new query each time with as many conditions as you need. For example,

...

WHERE ss.subsecto = 'AGRIGENERA'
   OR ss.subsecto LIKE  'LIVESTOCK_'
   OR ss.subsecto LIKE '%GMO%'
   
...

If this is an application involving a search form which may allow one, two, three, or more conditions for a search and the conditions are strings such as those above then you must write code to create the query on the fly. This can be done either in the script that processes the search request or in a stored procedure. This is much more complicated.

 

[claufranck]

I will try all that.

Using 'or' works. The problem is use 'and'

Several projects have both criteria and some more maybe, so i want ot know which projects have both criteria or 3 or 4, al query's are dynamically generated, so it is not complicated, the thing is to get to work first then program it

 

[rac2]

Ah. You wish to find projects that have all specified subsectos.

SELECT r.projet as project,
       descript as des

FROM refchif r
JOIN refsub rsb ON rsb.PROJET=r.PROJET
JOIN subsecto ss ON rsb.SUBSECTO=ss.SUBSECTO

WHERE ss.subsecto LIKE 'AGRIGENERA'
   OR ss.subsecto LIKE 'LIVESTOCK_'

GROUP BY r.projet

HAVING COUNT(*) = 2

ORDER BY r.projet ASC

This will be all of the projet with exactly those two subsecto.


Note Bene. No need to use OUTER JOIN to refsub and subsecto tables because they cannot be deficient. By definition every foreign key in refchif exists in the corresponding primary table.

 

[r937]

SELECT ...
  FROM ...
 WHERE ss.subsecto like 'AGRIGENERA'
    OR ss.subsecto like 'LIVESTOCK_'
GROUP
    BY r.projet 
[b]HAVING COUNT(*) = 2[/b]

r937.com | rudy.ca

 

[claufranck]

OK thank you so much that is working great just what I needed just bear with me a little longer. The whole query is a little more complicated and more joins, that was the only one where I was having issues. Now i got the results I needed almost, using your query I get 14 results isung mine I get all previous 14 plus a few extra total 18, checked one by one and all 4 extras have inly one of the subsectors trying to understand why they get there. wher eis te complete query:

SELECT r.projet as project, GROUP_CONCAT(distinct c.fullname separator ', ') as country, r.titre1 as title, r.client as client, date_format(r.datedebut,'%m/%d/%Y') as datebegin,date_format(r.datefin,'%m/%d/%Y') as dateend, r.certletter as cert, archive as archive FROM refchif r LEFT OUTER JOIN refcou ro ON ro.PROJET=r.PROJET LEFT OUTER JOIN country c ON ro.COUNTRY=c.COUNTRY LEFT OUTER JOIN refsub rsb ON rsb.PROJET=r.PROJET LEFT OUTER JOIN subsecto ss ON rsb.SUBSECTO=ss.SUBSECTO LEFT OUTER JOIN sector se ON ss.SECTOR=se.SECTOR WHERE ( (ss.subsecto like 'AGRIGENERA') or (ss.subsecto like 'LIVESTOCK_')) group by r.projet HAVING COUNT(*) = 2 order by r.projet asc

refcou link projects with a table with country and codes and in different languages

sector is the main category for subsectors

Tried removing the join for sector same thing

the extra rows are because of the join with country, is there way to get the results you got without remomving the join to country.

Left outer join have been used since all data and most of the database estructure existed before I came to work with it, so i tried to maintain as much as possible and haven't yet been able to validate all data and data integrity

Thanks you so much

 

[r937]

try

HAVING COUNT(DISTINCT ss.subsecto) = 2

if that's not right, then just change it to match the conditions you are searching for

r937.com | rudy.ca

 

[claufranck]

Thank,s guys so much It all works great now

 

[claufranck]

Ok it works great thank you all for the help.

Just one last thing, what if I want to the same search with different joined tables, how should I build the having?

i.e. (wrng but to give you guys an idea)

HAVING COUNT(DISTINCT ss.subsecto) = 2, COUNT(DISTINCT c.country) = 3

I tried it but to no avail

Thanks so much