SQL problem, help if possible please.

[L1sa]

Hi,

I am guessing this is something simple to you guys however I am new to sql and am unsure how to create this query (I must do it in SQL).

I am wanting to show in one query which supplier sells all species of plants. So for instance I want the query to display the results like so:

Supplier Species

PlantCO Plant, Tree, flower

(not neccessarily in a row, could be every species in separate row).

I tried a count to count the number of suppliers which sell all species, however, it didn't work out too good:

SELECT DISTINCT LEVERANC.LEV_NAAM, Count(PLANTEN.PLANTNAAM) AS PLANTNAAM
FROM PLANTEN INNER JOIN (LEVERANC INNER JOIN OFFERTES ON LEVERANC.LEV_CODE = OFFERTES.LEV_CODE) ON PLANTEN.ART_CODE = OFFERTES.ART_CO
GROUP BY LEVERANC.LEV_NAAM;

(sorry it is Dutch data, plantnaam being the plant and leveranc being the supplier).

I hope this is enough information and if anybody can provide me with advice or a solution I would appreciate it a lot. Thankyou :)

[LoneStar]

If I understand you correctly you can prob use the following SQL statement.

SELECT <Supplier> FROM <table_name>
WHERE <Column Name> IS NOT NULL, <Column Name> IS NOT NULL....and so on...This is assuming that each Specie is listed in a separate column.
Hope this helps..

Hi,

I am guessing this is something simple to you guys however I am new to sql and am unsure how to create this query (I must do it in SQL).

I am wanting to show in one query which supplier sells all species of plants. So for instance I want the query to display the results like so:

Supplier Species

PlantCO Plant, Tree, flower

(not neccessarily in a row, could be every species in separate row).

I tried a count to count the number of suppliers which sell all species, however, it didn't work out too good:

SELECT DISTINCT LEVERANC.LEV_NAAM, Count(PLANTEN.PLANTNAAM) AS PLANTNAAM
FROM PLANTEN INNER JOIN (LEVERANC INNER JOIN OFFERTES ON LEVERANC.LEV_CODE = OFFERTES.LEV_CODE) ON PLANTEN.ART_CODE = OFFERTES.ART_CO
GROUP BY LEVERANC.LEV_NAAM;

(sorry it is Dutch data, plantnaam being the plant and leveranc being the supplier).

I hope this is enough information and if anybody can provide me with advice or a solution I would appreciate it a lot. Thankyou :)

[NeoPa]

If that answer doesn't solve your problem, perhaps you could post the data layout you're working with.
What tables do you have?
Which fields are in those tables?
How are the tables logically joined?
Ansers are much easier to come by when people have the info available.

[L1sa]

It didn't solve the problem, but perhaps it is just me!

To help clarify a bit better. I am to find the supplier (leveranc) who sells all species (soort) of plants (plantnaam). The entities and relationships are as follows:

Planten- ART_CODE, PLANTNAAM, SOORT
OFFERTES- LEV_CODE, ART_CO_LEV, ART_CO (being the table which links planten and leveranc).
leveranc- LEV_CODE, LEV_NAAM, ADRES, WOONPLAATS (CITY)

JOINED VIA: (leveranc.lev_code=offertes.lev_code and planten.art_code= offertes.art_co_lev).

I need to show the supplier name (LEV_NAAM) and the species of the plants(SOORT) only. Thus I need to use both the leveranc tables and the planten tables (using offertes as the link).

There are 11 lev_naam (suppliers) and 10 different species (soort) if this also helps.

I hope I have made it all clearer now =)

[NeoPa]

When you say your query should show all suppliers who sell all species - do you mean you want the query to show all species sold for each supplier?
Or do you mean you only want to show those suppliers who sell every species and ignore suppliers who don't sell all of them?
Also, your second post is a lot clearer - I appreciate expressing it in English is difficult for you. I have some Dutch friends (internet friends) who struggle a bit but I'm always impressed by how well they can communicate in a foreign language.
Am I right in thinking the three tables are connected logically such that the OFFERTES table is like a parent of BOTH of the others?
So, each OFFERTES record would have two fields : One which matched a record in the Planten table, and the other that matched a record in the leveranc table?

[MMcCarthy]

If you want to count the number of suppliers per species. If I understood you correctly this should work.

SELECT PLANTEN.PLANTNAAM, Count(LEVERANC.LEV_NAAM)
FROM PLANTEN INNER JOIN (LEVERANC INNER JOIN OFFERTES ON LEVERANC.LEV_CODE = OFFERTES.LEV_CODE) ON PLANTEN.ART_CODE = OFFERTES.ART_CO
GROUP BY PLANTEN.PLANTNAAM;

[MMcCarthy]

To show the full list including the count you can create this first and all it qryCountLEV_NAAM

SELECT PLANTEN.PLANTNAAM, Count(LEVERANC.LEV_NAAM) As CountLEV_NAAM
FROM PLANTEN INNER JOIN (LEVERANC INNER JOIN OFFERTES ON LEVERANC.LEV_CODE = OFFERTES.LEV_CODE) ON PLANTEN.ART_CODE = OFFERTES.ART_CO
GROUP BY PLANTEN.PLANTNAAM;

Then follow it up with:

SELECT LEVERANC.LEV_NAAM, PLANTEN.PLANTNAAM, qryCountLEV_NAAM.CountLEV_NAAM
FROM (PLANTEN INNER JOIN qryCountLEV_NAAM
ON PLANTEN.PLANTNAAM=qryCountLEV_NAAM.PLANTNAAM)
INNER JOIN (LEVERANC INNER JOIN OFFERTES ON LEVERANC.LEV_CODE = OFFERTES.LEV_CODE) ON PLANTEN.ART_CODE = OFFERTES.ART_CO
GROUP BY LEVERANC.LEV_NAAM, PLANTEN.PLANTNAAM;

[L1sa]

Hey thankyou the first part works fine, however, the second part has created a parameter query, asking for the number of plants, I put it in and it brings up every supplier with a column (count) displaying the number I typed :s


for instance

supplier- a, b, c, d, e and so on
CountLev_Naam- 1,1, 1,1

any suggestions? :s I have had a go but I made a mess of it =(

[MMcCarthy]

I have re-read the question. Will this give you the result you want?

SELECT LEVERANC.LEV_NAAM, Count(PLANTEN.SOORT) As CountSOORT
FROM PLANTEN INNER JOIN (LEVERANC INNER JOIN OFFERTES ON LEVERANC.LEV_CODE = OFFERTES.LEV_CODE) ON PLANTEN.ART_CODE = OFFERTES.ART_CO
GROUP BY LEVERANC.LEV_NAAM;

[L1sa]

Or do you mean you only want to show those suppliers who sell every species and ignore suppliers who don't sell all of them?

Am I right in thinking the three tables are connected logically such that the OFFERTES table is like a parent of BOTH of the others?
So, each OFFERTES record would have two fields : One which matched a record in the Planten table, and the other that matched a record in the leveranc table?

Or do you mean you only want to show those suppliers who sell every species and ignore suppliers who don't sell all of them: this is what I am wanting yes. Yes the offertes table is a parent of the other two.

I have gone through the database and only one supplier does indeed sell every species of plant. Hence why I had trouble which it, I to at first was unsure exactly what was needed. I must create the query bearing in mind other suppliers could be added in future selling every species of plant.

So I cannot simply create a query which will

SELECT LEV_NAAM, PLANTEN.SOORT FROM PLANTEN, LEVERANC WHERE LEV_NAAM= 'Spitman' AND PLANTEN.SOORT='32'

(you get the idea!).

[L1sa]

I have re-read the question. Will this give you the result you want?

SELECT LEVERANC.LEV_NAAM, Count(PLANTEN.SOORT) As CountSOORT
FROM PLANTEN INNER JOIN (LEVERANC INNER JOIN OFFERTES ON LEVERANC.LEV_CODE = OFFERTES.LEV_CODE) ON PLANTEN.ART_CODE = OFFERTES.ART_CO
GROUP BY LEVERANC.LEV_NAAM;

This shows all suppliers and gives a count of how many species of plants they sell. I am only wanting to show the supplier which sells every species. (annoying I know!!) So realistically I am wanting (I think) a count of how many species there are then the supplier which sells them. :s

[L1sa]

Or do you mean you only want to show those suppliers who sell every species and ignore suppliers who don't sell all of them: this is what I am wanting yes. Yes the offertes table is a parent of the other two.

I have gone through the database and only one supplier does indeed sell every species of plant. Hence why I had trouble which it, I to at first was unsure exactly what was needed. I must create the query bearing in mind other suppliers could be added in future selling every species of plant.

So I cannot simply create a query which will

SELECT LEV_NAAM, PLANTEN.SOORT FROM PLANTEN, LEVERANC WHERE LEV_NAAM= 'Spitman' AND PLANTEN.SOORT='32'

(you get the idea!).

To add to this post (since the time to edit had expired sorry!)

The primary key of Planten: ART_CODE is a foreign key in OFFERTES (named ART_CO)
The primary key of Leveranc: LEV_CODE is a foreign key in OFFERTES (named LEV_CODE). I hope this helps define the relationship a bit better oh and thanks for both of your inputs :)

[MMcCarthy]

I have re-read the question. Will this give you the result you want?

SELECT LEVERANC.LEV_NAAM, Count(PLANTEN.SOORT) As CountSOORT
FROM PLANTEN INNER JOIN (LEVERANC INNER JOIN OFFERTES ON LEVERANC.LEV_CODE = OFFERTES.LEV_CODE) ON PLANTEN.ART_CODE = OFFERTES.ART_CO
GROUP BY LEVERANC.LEV_NAAM;

Does the PLANTEN table contain one and only one record for each species?

[L1sa]

Does the PLANTEN table contain one and only one record for each species?

Here is a screenshot of the relationships if this helps:

PLANTEN contains one attribute SOORT within this there are several species:

[MMcCarthy]

Does the PLANTEN table contain one and only one record for each species?

SELECT LEVERANC.LEV_NAAM, Count(PLANTEN.SOORT) As CountSOORT, Count(LIST_SOORT.SOORT) As TotalSOORT
FROM PLANTEN INNER JOIN (LEVERANC INNER JOIN OFFERTES ON LEVERANC.LEV_CODE = OFFERTES.LEV_CODE) ON PLANTEN.ART_CODE = OFFERTES.ART_CO, (SELECT DISTINCT SOORT FROM PLANTEN) As LIST_SOORT
GROUP BY LEVERANC.LEV_NAAM;


See if this will work ...

[MMcCarthy]

SELECT LEVERANC.LEV_NAAM, Count(PLANTEN.SOORT) As CountSOORT, Count(LIST_SOORT.SOORT) As TotalSOORT
FROM PLANTEN INNER JOIN (LEVERANC INNER JOIN OFFERTES ON LEVERANC.LEV_CODE = OFFERTES.LEV_CODE) ON PLANTEN.ART_CODE = OFFERTES.ART_CO, (SELECT DISTINCT SOORT FROM PLANTEN) As LIST_SOORT
GROUP BY LEVERANC.LEV_NAAM;


See if this will work ...

If it does give you the count then change it as follows to only return suppliers who can match count.

SELECT LEVERANC.LEV_NAAM, Count(PLANTEN.SOORT) As CountSOORT, Count(LIST_SOORT.SOORT) As TotalSOORT
FROM PLANTEN INNER JOIN (LEVERANC INNER JOIN OFFERTES ON LEVERANC.LEV_CODE = OFFERTES.LEV_CODE) ON PLANTEN.ART_CODE = OFFERTES.ART_CO, (SELECT DISTINCT SOORT FROM PLANTEN) As LIST_SOORT
WHERE Count(PLANTEN.SOORT)=Count(LIST_SOORT.SOORT)
GROUP BY LEVERANC.LEV_NAAM;

[MMcCarthy]

Or you could try.

SELECT LEVERANC.LEV_NAAM, Count(PLANTEN.SOORT) As CountSOORT
FROM PLANTEN INNER JOIN (LEVERANC INNER JOIN OFFERTES ON LEVERANC.LEV_CODE = OFFERTES.LEV_CODE) ON PLANTEN.ART_CODE = OFFERTES.ART_CO, (SELECT DISTINCT SOORT FROM PLANTEN) As LIST_SOORT
WHERE Count(PLANTEN.SOORT)=Count(LIST_SOORT.SOORT)
GROUP BY LEVERANC.LEV_NAAM;[/quote]

[L1sa]

Nope still no luck =(

I have tried other ways of doing it such as:

SELECT l.LEV_NAAM, Count(p.SOORT) AS SOORT
FROM PLANTEN p, LEVERANC l
GROUP BY l.LEV_NAAM
HAVING SOORT =
(SELECT COUNT(*) FROM PLANTEN)


But this fails to work, grrr must say it is rather annoying, I might just leave it out :P

[MMcCarthy]

What happened when you tried by last one. Did it fail or just give the wrong result?

Don't give up yet there are always (well nearly always) more things to try.

[L1sa]

There is a problem with this line:

WHERE Count(PLANTEN.SOORT)=PLANTEN.SOORT

'Can't have static functions using WHERE'

I also tried replacing the WHERE with HAVING but no luck :(

[MMcCarthy]

There is a problem with this line:

WHERE Count(PLANTEN.SOORT)=PLANTEN.SOORT

'Can't have static functions using WHERE'

I also tried replacing the WHERE with HAVING but no luck :(

That's not what I gave you. Copy and paste the following exactly as it is.

SELECT LEVERANC.LEV_NAAM, Count(PLANTEN.SOORT) As CountSOORT
FROM PLANTEN INNER JOIN (LEVERANC INNER JOIN OFFERTES ON LEVERANC.LEV_CODE = OFFERTES.LEV_CODE) ON PLANTEN.ART_CODE = OFFERTES.ART_CO, (SELECT DISTINCT SOORT FROM PLANTEN) As LIST_SOORT
WHERE Count(PLANTEN.SOORT)=Count(LIST_SOORT.SOORT)
GROUP BY LEVERANC.LEV_NAAM;

[L1sa]

I know, the version you gave me also claims there is a problem with the same line (the same problem) I was just trying different variations of it etc =(

[MMcCarthy]

I know, the version you gave me also claims there is a problem with the same line (the same problem) I was just trying different variations of it etc =(

OK Access doesn't really like nested queries and can through in square brackets where they don't belong so we'll try it like this instead.

qry1
SELECT DISTINCT SOORT FROM PLANTEN;

SELECT LEVERANC.LEV_NAAM, Count(PLANTEN.SOORT) As CountSOORT, Count(qry1.SOORT)
FROM PLANTEN INNER JOIN (LEVERANC INNER JOIN OFFERTES ON LEVERANC.LEV_CODE = OFFERTES.LEV_CODE) ON PLANTEN.ART_CODE = OFFERTES.ART_CO, qry1
WHERE PLANTEN.SOORT=qry1.SOORT
GROUP BY LEVERANC.LEV_NAAM;

I'm not sure if it will work but let me know and I'll look at something else.

I know there is a way of doing this.

[L1sa]

This is what it brought up:

The arrow is meant to show which field should be in the final result (since this supplier is the only supplier to sell all species of plants) so there must be a way of showing this.

However, I can't simply define this supplier in the sql because more suppliers could be added who also sell every plant species :s

[MMcCarthy]

SELECT LEVERANC.LEV_NAAM, Count(PLANTEN.SOORT) As CountSOORT, Count(qry1.SOORT)
FROM PLANTEN INNER JOIN (LEVERANC INNER JOIN OFFERTES ON LEVERANC.LEV_CODE = OFFERTES.LEV_CODE) ON PLANTEN.ART_CODE = OFFERTES.ART_CO, qry1
WHERE Count(PLANTEN.SOORT)=Count(qry1.SOORT)
GROUP BY LEVERANC.LEV_NAAM;

Try this one...

[L1sa]

Nope =( (same error as before with the where clause) I will keep on at it, I am sure you have better things to do with your time then attempt to solve this query with me!

I will try different variations and see if it works.

[L1sa]

SELECT DISTINCT LEVERANC.LEV_NAAM, qryCountLEV_NAAM.LEV_CODE, qryCountLEV_NAAM.SPECIES
FROM PLANTEN INNER JOIN ((LEVERANC INNER JOIN qryCountLEV_NAAM ON LEVERANC.LEV_CODE = qryCountLEV_NAAM.LEV_CODE) INNER JOIN OFFERTES ON LEVERANC.LEV_CODE = OFFERTES.LEV_CODE) ON PLANTEN.ART_CODE = OFFERTES.ART_CO
GROUP BY LEVERANC.LEV_NAAM, qryCountLEV_NAAM.LEV_CODE, qryCountLEV_NAAM.SPECIES
HAVING (((qryCountLEV_NAAM.LEV_CODE)='013'));

This displays the correct supplier and count of species, however, as you can see from the last line I selected the right supplier by giving the LEV_CODE which will work for now if there are no more suppliers added :s perhaps this last line can be adapted somehow?

[MMcCarthy]

SELECT DISTINCT LEVERANC.LEV_NAAM, qryCountLEV_NAAM.LEV_CODE, qryCountLEV_NAAM.SPECIES
FROM PLANTEN INNER JOIN ((LEVERANC INNER JOIN qryCountLEV_NAAM ON LEVERANC.LEV_CODE = qryCountLEV_NAAM.LEV_CODE) INNER JOIN OFFERTES ON LEVERANC.LEV_CODE = OFFERTES.LEV_CODE) ON PLANTEN.ART_CODE = OFFERTES.ART_CO
GROUP BY LEVERANC.LEV_NAAM, qryCountLEV_NAAM.LEV_CODE, qryCountLEV_NAAM.SPECIES
HAVING (((qryCountLEV_NAAM.LEV_CODE) IN (SELECT DISTINCT LEV_CODE FROM qyrCountLEV_NAAM HAVING MAX(COUNT(SPECIES)))));

You might have to play with this a bit as I'm not sure what was in the final version of qryCountLEV_NAAM. If you can't work it out post the full query and I'll try to work it out.

[NeoPa]

If you want a list of all suppliers (Leveranc) and all the species (Soort) they sell, as well as a count of all the plants (Planten) found in each of those species - try :

SELECT LEVERANC.LEV_NAAM, PLANTEN.SOORT, _
    Count(PLANTEN.ART_CODE) As CountPlant
FROM (OFFERTES INNER JOIN PLANTEN ON OFFERTES.ART_CO = PLANTEN.ART_CODE) _
    INNER JOIN LEVERANC ON OFFERTES.LEV_CODE = LEVERANC.LEV_CODE
GROUP BY OFFERTES.LEV_CODE, PLANTEN.SOORT

[NeoPa]

Try this.
It should give you the right selection of records and you can add fields to it when you're comfortable you're getting them.

SELECT LEVERANC.LEV_NAAM,
    Count(PLANTEN.PLANTNAAM) AS PlantCount
FROM (OFFERTES INNER JOIN PLANTEN ON 
    OFFERTES.ART_CO = PLANTEN.ART_CODE)
    INNER JOIN LEVERANC ON 
    OFFERTES.LEV_CODE = LEVERANC.LEV_CODE)
GROUP BY LEVERANC.LEV_NAAM
HAVING PlantCount =
    (SELECT Count(PLANTNAAM)
    FROM (SELECT DISTINCT [PLANTNAAM]
    FROM PLANTEN))

BTW I missed one of your answers earlier which gave me the info I needed.
So, sorry for the delay.