Good approach to writing advanced queries (1 Viewer)

jaryszek

Registered User.
Local time
Today, 03:56
Joined
Aug 25, 2016
Messages
756
Hi Guys,

i have to get CustomerTopologyID based on Temptable:



I can not simple create joins like here because i have:



generally i am breaking this query into 2 steps: first where i am getting CutomerID and TopologyID, and in second query i am left join it to CustomerTopology table to get CustomerTopologyID.

It is good approach?
I do not how to join this in one step?
What about performance of database if i will add to many not truly necessarry queries?

Best,
Jacek
 

Attachments

  • Screenshot_11.jpg
    Screenshot_11.jpg
    75.7 KB · Views: 314
  • Screenshot_12.jpg
    Screenshot_12.jpg
    74.2 KB · Views: 261

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:56
Joined
May 7, 2009
Messages
19,169
why do you need to have another table?
all info can be extracted from temp_soft, customer and topology.
dont over over normalized, leading to abnormal structure.
 

jaryszek

Registered User.
Local time
Today, 03:56
Joined
Aug 25, 2016
Messages
756
thank you arnelgp.

I have unfortunately this table because this is the part of bigger model.
And i have to somehow join it to my tempTable and question is about approach, will you do the same or write it in one query somehow? How to do this via wizard?

Jacek
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 21:56
Joined
Jan 20, 2009
Messages
12,849
Just change the inner joins to outer joins in the same direction as the others.
 

jaryszek

Registered User.
Local time
Today, 03:56
Joined
Aug 25, 2016
Messages
756
thank you very much Galaxiom but it didnt work, i have still error:



Please help,
Jacek
 

Attachments

  • Screenshot_13.jpg
    Screenshot_13.jpg
    87.4 KB · Views: 236

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:56
Joined
May 7, 2009
Messages
19,169
add another temp_software to your layout.
reverse all those arrows.
topology linked to orig temp_soft
customer linked to the other temp_soft.
 

Minty

AWF VIP
Local time
Today, 10:56
Joined
Jul 26, 2013
Messages
10,354
I'm with Arne - this isn't needed as you are already storing this in the first table.
Also your temp table is storing the name not the FK from your two look up tables.

Simply move the FK to your table on the right and you have your junction table with the additional information.
 

jaryszek

Registered User.
Local time
Today, 03:56
Joined
Aug 25, 2016
Messages
756
ok thank you once again!

Also your temp table is storing the name not the FK from your two look up tables.

yes because i am getting string names...

add another temp_software to your layout.
DONE

reverse all those arrows.
DONE

topology linked to orig temp_soft
customer linked to the other temp_soft.

and this i am not understanding.
Can you explain more?



Best,
Jacek
 

Attachments

  • Screenshot_14.jpg
    Screenshot_14.jpg
    89 KB · Views: 232

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:56
Joined
May 7, 2009
Messages
19,169
link customer to Other temp_soft
 

CJ_London

Super Moderator
Staff member
Local time
Today, 10:56
Joined
Feb 19, 2013
Messages
16,553
I can see why you are doing it - you want to identify the customer topology ID from the customer and topology names.

You need two queries to do what you require.

you can go either way, but the easiest is to link topology, customers and customertopologies together with ordinary (inner) joins and return customertopologyID and the two names. Save this query

then create another query left joining your temp table to the other query on the names.

You can do this in one query if required - provide the sql you have and I can show you how
 

jaryszek

Registered User.
Local time
Today, 03:56
Joined
Aug 25, 2016
Messages
756
Hmm this is very strange:



i am getting error here but some of relationships have number 2, some 3.

I do not know why.
Whe i have situation like here:



it is working but adding topologyId will throw an error as above.
Maybe i will upload example database ?

Best,
Jacek
 

Attachments

  • Screenshot_15.jpg
    Screenshot_15.jpg
    86.3 KB · Views: 228
  • Screenshot_16.jpg
    Screenshot_16.jpg
    67.8 KB · Views: 232

jaryszek

Registered User.
Local time
Today, 03:56
Joined
Aug 25, 2016
Messages
756
Thank you CJ_London,

i am attaching test database.
Can you please show me 2 methods? With 2 queries and one query?
 

Attachments

  • Test.accdb
    880 KB · Views: 93

CJ_London

Super Moderator
Staff member
Local time
Today, 10:56
Joined
Feb 19, 2013
Messages
16,553
qry1
Code:
SELECT CustomerTopologyID,CustomerName, TopologyName
FROM tblTopologies INNER JOIN (tblCustomers INNER JOIN tblCustomerTopology ON tblCustomers.CustomerID = tblCustomerTopology.CustomerIDFK) ON tblTopologies.TopologyID = tblCustomerTopology.TopologyIDFK

qry2
Code:
SELECT CustomerTopologyID, Temp_SoftwareComponentsOses.*
FROM Temp_SoftwareComponentsOses LEFT JOIN qry1 ON (Temp_SoftwareComponentsOses.CustomerName = qry1.CustomerName) AND (Temp_SoftwareComponentsOses.TopologyName = qry1.TopologyName)

single query
Code:
SELECT CustomerTopologyID, Temp_SoftwareComponentsOses.*
FROM Temp_SoftwareComponentsOses LEFT JOIN (SELECT CustomerTopologyID,CustomerName, TopologyName
FROM tblTopologies INNER JOIN (tblCustomers INNER JOIN tblCustomerTopology ON tblCustomers.CustomerID = tblCustomerTopology.CustomerIDFK) ON tblTopologies.TopologyID = tblCustomerTopology.TopologyIDFK)  AS qID ON (Temp_SoftwareComponentsOses.TopologyName = qID.TopologyName) AND (Temp_SoftwareComponentsOses.CustomerName = qID.CustomerName);
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:56
Joined
May 7, 2009
Messages
19,169
see change in structure of the tables
I delete one junction.
you are creating unnecessary table.
customer table can directly connect to topology.
no need to mediate between customer and topology.
see query1.

check your title.
 

Attachments

  • Test (5).accdb
    1.1 MB · Views: 91

jaryszek

Registered User.
Local time
Today, 03:56
Joined
Aug 25, 2016
Messages
756
Thank you very much for help and support.
And sample!

CJ London are you writing these queries usin wizard or sql editor?
How to learn them ?:)

arnelgp nice approach. Why your method is better then one additional table?
This is a matter of developer approach?

Best,
Jacek
 

CJ_London

Super Moderator
Staff member
Local time
Today, 10:56
Joined
Feb 19, 2013
Messages
16,553
the first two use the query GUI. The third is basically a copy/paste of the first query into the second query using the sql window.

you can see the first query as highlighted in red here. I changed the alias to help differentiate it from the second query

Code:
SELECT CustomerTopologyID, Temp_SoftwareComponentsOses.*
FROM Temp_SoftwareComponentsOses LEFT JOIN ([COLOR="red"]SELECT CustomerTopologyID,CustomerName, TopologyName
FROM tblTopologies INNER JOIN (tblCustomers INNER JOIN tblCustomerTopology ON tblCustomers.CustomerID = tblCustomerTopology.CustomerIDFK) ON tblTopologies.TopologyID = tblCustomerTopology.TopologyIDFK[/COLOR])  AS qID ON (Temp_SoftwareComponentsOses.TopologyName = qID.TopologyName) AND (Temp_SoftwareComponentsOses.CustomerName = qID.CustomerName);
 

Users who are viewing this thread

Top Bottom