Solved Join between specific version and "All" field (1 Viewer)

jaryszek

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

i have 2 tables:

1593092145090.png


and :

1593092173662.png


What i want is to get left join between AppVersion and System from TAble2ToJoin and get all matching records but inclusind relation "AnyVersion" to "All". So all versions (4.1,3.1, etc.) should be joined with "All" from Table2Join.

1593092219956.png


Result table should look like here:

1593092404307.png


Can you please help? I have no idea how to do this.

Best wishes,
Jacek
 

Attachments

  • DatabaseAllJoin.accdb
    432 KB · Views: 83

theDBguy

I’m here to help
Staff member
Local time
Today, 09:02
Joined
Oct 29, 2018
Messages
21,358
Hi. Have you tried using the Nz() function? Just a thought...
 

jaryszek

Registered User.
Local time
Today, 09:02
Joined
Aug 25, 2016
Messages
756
theDbGuy thanks but it is not problem here.
I need to join 4.1, 3.1 and each version to "All".

i do not have any nulls here.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 11:02
Joined
Feb 28, 2001
Messages
26,999
To do this, write your query twice - once with specific version mixes, the second time that would match the ALL for you. When the two queries each give you the right sub-sections, then rewrite the query so that the WHERE clause has "WHERE ( first criteria ) OR ( second criteria )... "
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:02
Joined
Oct 29, 2018
Messages
21,358
theDbGuy thanks but it is not problem here.
I need to join 4.1, 3.1 and each version to "All".

i do not have any nulls here.
What do you get with this query?
SQL:
SELECT Table1.AppVersion, Table1.AppName. Nz(Table2.System, "Windows") As MySystem
FROM Table1 LEFT JOIN Table2 ON Table1.AppVersion=Table2.AppVersion
 

plog

Banishment Pending
Local time
Today, 11:02
Joined
May 11, 2011
Messages
11,611
First, AppVersion should not be in Table2. You link a foreign table to the primary key of the other table, not by another field. So the whole premise seems odd.

With that said, to truly JOIN those you will need a sub-query:

Code:
SELECT Table1.AppVersionID, Table1.AppVerion, Table1.AppName, IIf(IsNull([System]),"All",[Table1].[AppVerion]) AS VersionKey
FROM Table1 LEFT JOIN Table2ToJoin ON Table1.AppVerion = Table2ToJoin.AppVersion;

Paste that SQL into a new query object and name it 'sub1'. It creates a field that uses the AppVersion value if it matches or defaults it to 'All' if there is no match. Then to get the results you want, use this query:

Code:
SELECT sub1.AppVersionID, sub1.AppVerion, sub1.AppName, Table2ToJoin.System
FROM sub1 INNER JOIN Table2ToJoin ON sub1.VersionKey = Table2ToJoin.AppVersion;

Using these 2 queries ensures that if you ever update the [System] value for the 'All' record in Table2ToJoin it will use that new [System] value without having to modify either of these queries.
 
Last edited:

jaryszek

Registered User.
Local time
Today, 09:02
Joined
Aug 25, 2016
Messages
756
thank you theBdGuy but this is short time solution, instead of Windows i can have a lot of differents strings which i can not predict.
The_Doc_Man - i do not understand

plog - o wow nice!! It seems to be ok. But one question. What if i will have nulls in table ? I will match records with nulls as "ALL" based on Sub1 query.
1593146441876.png


and this is not true because Null Appversion it not All, it is null, hmm how to avoid that?

Jacek
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:02
Joined
May 7, 2009
Messages
19,169
see Table2ToJoin, i added yet another record.
see the query, Join.
 

Attachments

  • DatabaseAllJoin.zip
    21.5 KB · Views: 90

jaryszek

Registered User.
Local time
Today, 09:02
Joined
Aug 25, 2016
Messages
756
thank you arnelgp, it is nice solution.

I am wondering if plog can figure out something more with his query. i like the most solution
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:02
Joined
May 7, 2009
Messages
19,169
the solution is somewhat same with plag.
it is both Hard coded.
 

isladogs

MVP / VIP
Local time
Today, 16:02
Joined
Jan 14, 2017
Messages
18,186
This can also be done using a union query
Code:
SELECT Table1.AppVersionID, Table1.AppVerion, Table1.appname, 'Windows' AS System
FROM Table1
UNION SELECT Table1.AppVersionID, Table1.AppVerion, Table1.AppName, Table2ToJoin.System
FROM Table1 INNER JOIN Table2ToJoin ON Table1.AppVerion = Table2ToJoin.AppVersion;
 

jaryszek

Registered User.
Local time
Today, 09:02
Joined
Aug 25, 2016
Messages
756
thank you Colin,

this is nice but what if you would have

1593159782282.png


it will required all the time manually input system name into first part of union query.

Jacek
 

isladogs

MVP / VIP
Local time
Today, 16:02
Joined
Jan 14, 2017
Messages
18,186
In that case the first part of the union query would need altering but I'll leave that to you.... 😀
 

plog

Banishment Pending
Local time
Today, 11:02
Joined
May 11, 2011
Messages
11,611
To work with Nulls like you want these are the sub and main queries to use:

sub1:
Code:
SELECT Table1.AppVersionID, Table1.AppVerion, Table1.AppName, IIf(IsNull([Table1].[AppVerion]),[Table1].[AppVerion],IIf(IsNull([System]),"All",[Table1].[AppVerion])) AS VersionKey
FROM Table1 LEFT JOIN Table2ToJoin ON Table1.AppVerion = Table2ToJoin.AppVersion;


Code:
SELECT sub1.AppVersionID, sub1.AppVerion, sub1.AppName, Table2ToJoin.System
FROM sub1 LEFT JOIN Table2ToJoin ON sub1.VersionKey = Table2ToJoin.AppVersion;
 

isladogs

MVP / VIP
Local time
Today, 16:02
Joined
Jan 14, 2017
Messages
18,186
I think he just meant altering as in 'changing the query', not using the Alter Table statement.
Thanks Isaac. That was exactly what I meant.
This should work. I've just added an extra UNION clause

Code:
SELECT Table1.AppVersionID, Table1.AppVerion, Table1.appname, 'Windows' AS System
FROM Table1
UNION SELECT Table1.AppVersionID, Table1.AppVerion, Table1.appname, 'Ubuntu' AS System
FROM Table1
UNION SELECT Table1.AppVersionID, Table1.AppVerion, Table1.AppName, Table2ToJoin.System
FROM Table1 INNER JOIN Table2ToJoin ON Table1.AppVerion = Table2ToJoin.AppVersion;

I'm not sure which approach would be faster: UNION or subquery. Both methods have advantages.
For a small recordset, I doubt the speed matters, but for a large number of records it might be worth checking.
 
Last edited:

Users who are viewing this thread

Top Bottom