Show items only once

[mischa]

I would like to show all linked components (once) in a query.
With inner joins (option 2 when double clicking on a relationship line), it shows all linked components but with duplicates. When selecting option 1 (only include rows where the joined field from both tables are equal) I only get 1 row due to the fact that not all components have values in all columns.

Therefore I would like to ask if someone could help me create a query that shows all components that have one or more links to other parts. These links have be checked 3 tables.

I attached a overview of the tables involved.

 

[plog]

I think this is a structural issue. Why do you have 3 Component Link tables? How do they differ and why aren't they all in one table?

 

[mischa]

I think this is a structural issue. Why do you have 3 Component Link tables? How do they differ and why aren't they all in one table?

This is done in order to make difference between systematic layers. For example a computer exists of multiple components such as the motherboard. These components subsequently consist of other components, called sub components, such as resistors and IC's.

 

[jdraw]

Can you show us an example using your data of what you
-have as input, and
-want as output?

 

[plog]

Can you provide sample data from your tables, then what the query should produce based on that sample data?

 

[Mihail]

@jdraw and @ plog
By reading your posts (05:21 PM) I remember a joke:
- If two guys say you that you have drink too much then is time to go to bed.
- Even if they have drink too ?

 

[mischa]

I attached the input and output to this post.
I hope one of you could generate the output as shown in the excel file.

Thanks in advance!

 

[plog]

You provided 7 tables in that database--do I need to use all of them? Because the below SQL produces your results using just 1:

SELECT tblComponent.ComponentPN, tblComponent.NameComponent
FROM tblComponent
WHERE ((Not (tblComponent.NameComponent)="DoNotLink"));

You need to provide a sample set that includes all the cases we must catch, but not include any extraneous data like extra tables.

 

[mischa]

You provided 7 tables in that database--do I need to use all of them? Because the below SQL produces your results using just 1:



You need to provide a sample set that includes all the cases we must catch, but not include any extraneous data like extra tables.

Thanks for you time Plog!
Yes, all given tables are required. No more no less.
At the moment there is only sample data in the database.
The query should check in all the link tables (which are linking the components to other categories) which components are entered. After these components have been gathered from all tables, it should generate a list of components without duplicate information.
The sample in my previous post is still leading and has not changed.

 

[plog]

From your results they are not all necessary. I was able to get your results by using just one table. If all are necessary, you need to include some sample data and results which can be achieved only by employing those tables.

 

[mischa]

I will make a sample of for this case in the coming days.
Thanks for your reply!

 

[Hanjo]

would SELECT DISTINCT not solve our issue?

 

[mischa]

would SELECT DISTINCT not solve our issue?

The SELECT DISTINCT solves the problem.
Thanks you very much !