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_NA AM, Count(PLANTEN.P LANTNAAM) AS PLANTNAAM
FROM PLANTEN INNER JOIN (LEVERANC INNER JOIN OFFERTES ON LEVERANC.LEV_CO DE = OFFERTES.LEV_CO DE) ON PLANTEN.ART_COD E = OFFERTES.ART_CO
GROUP BY LEVERANC.LEV_NA AM;

(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_NA AM, Count(PLANTEN.P LANTNAAM) AS PLANTNAAM
FROM PLANTEN INNER JOIN (LEVERANC INNER JOIN OFFERTES ON LEVERANC.LEV_CO DE = OFFERTES.LEV_CO DE) ON PLANTEN.ART_COD E = OFFERTES.ART_CO
GROUP BY LEVERANC.LEV_NA AM;

(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_c ode=offertes.le v_code and planten.art_cod e= 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.PLANTNA AM, Count(LEVERANC. LEV_NAAM)
FROM PLANTEN INNER JOIN (LEVERANC INNER JOIN OFFERTES ON LEVERANC.LEV_CO DE = OFFERTES.LEV_CO DE) ON PLANTEN.ART_COD E = OFFERTES.ART_CO
GROUP BY PLANTEN.PLANTNA AM;

[MMcCarthy]

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

SELECT PLANTEN.PLANTNA AM, Count(LEVERANC. LEV_NAAM) As CountLEV_NAAM
FROM PLANTEN INNER JOIN (LEVERANC INNER JOIN OFFERTES ON LEVERANC.LEV_CO DE = OFFERTES.LEV_CO DE) ON PLANTEN.ART_COD E = OFFERTES.ART_CO
GROUP BY PLANTEN.PLANTNA AM;

Then follow it up with:

SELECT LEVERANC.LEV_NA AM, PLANTEN.PLANTNA AM, qryCountLEV_NAA M.CountLEV_NAAM
FROM (PLANTEN INNER JOIN qryCountLEV_NAA M
ON PLANTEN.PLANTNA AM=qryCountLEV_ NAAM.PLANTNAAM)
INNER JOIN (LEVERANC INNER JOIN OFFERTES ON LEVERANC.LEV_CO DE = OFFERTES.LEV_CO DE) ON PLANTEN.ART_COD E = OFFERTES.ART_CO
GROUP BY LEVERANC.LEV_NA AM, PLANTEN.PLANTNA AM;

[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_NA AM, Count(PLANTEN.S OORT) As CountSOORT
FROM PLANTEN INNER JOIN (LEVERANC INNER JOIN OFFERTES ON LEVERANC.LEV_CO DE = OFFERTES.LEV_CO DE) ON PLANTEN.ART_COD E = OFFERTES.ART_CO
GROUP BY LEVERANC.LEV_NA AM;

[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_NA AM, Count(PLANTEN.S OORT) As CountSOORT
FROM PLANTEN INNER JOIN (LEVERANC INNER JOIN OFFERTES ON LEVERANC.LEV_CO DE = OFFERTES.LEV_CO DE) ON PLANTEN.ART_COD E = OFFERTES.ART_CO
GROUP BY LEVERANC.LEV_NA AM;

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_NA AM, Count(PLANTEN.S OORT) As CountSOORT
FROM PLANTEN INNER JOIN (LEVERANC INNER JOIN OFFERTES ON LEVERANC.LEV_CO DE = OFFERTES.LEV_CO DE) ON PLANTEN.ART_COD E = OFFERTES.ART_CO
GROUP BY LEVERANC.LEV_NA AM;

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_NA AM, Count(PLANTEN.S OORT) As CountSOORT, Count(LIST_SOOR T.SOORT) As TotalSOORT
FROM PLANTEN INNER JOIN (LEVERANC INNER JOIN OFFERTES ON LEVERANC.LEV_CO DE = OFFERTES.LEV_CO DE) ON PLANTEN.ART_COD E = OFFERTES.ART_CO , (SELECT DISTINCT SOORT FROM PLANTEN) As LIST_SOORT
GROUP BY LEVERANC.LEV_NA AM;


See if this will work ...