Alias in left Join

[alcalde]

Hello, does Access use alias in the Left Join?
I got this part from Postgre SQL, and I would like to convert it to Access.


Select ...., ....., .......

FROM antr a, email e, bew b

LEFT JOIN k_akfz staat3 ON (b.staukfz1 = staat3.refakfz AND staat3.sprache='E')
LEFT JOIN k_akfz staat1 ON (b.pokfz = staat1.refakfz AND staat1.sprache='E')

Thanks in advance
have a nice day.

 

[arnelgp]

what you have there is a Cross join (cartesian) query
and only in table BEW do you have joins, but join is on
Same table and on different fields?
you can try pasting that query on the SQL view of the query designer.

 

[ebs17]

The challenge with Jet-SQL is that JOINs must be bracketed individually.

SELECT
   ....,
   .....,
   .......
FROM
   antr a,
   email e,
   (bew b
      LEFT JOIN k_akfz staat3
      ON b.staukfz1 = staat3.refakfz
         AND
      staat3.sprache = 'E'
   )
   LEFT JOIN k_akfz staat1
   ON b.pokfz = staat1.refakfz
      AND
   staat1.sprache = 'E'

 

[alcalde]

Here is the whole SQL:

SELECT b.bewnr,
b.bewsem,
a.abschl,
a.stg,
b.vorname,
b.nachname,
b.geschl,
b.gebdat,
b.gebort,
staat2.ltxt AS "citizen of",
b.pozusatz AS "c/o",
b.postrasse,
b.zusastrasse,
b.poplz AS plz,
b.poort AS city,
staat1.ltxt AS country,
e.email,
iz1.inhalt AS "University",
iz2.inhalt AS "City Uni",
staat.ltxt AS "Country Uni",
iz9.inhalt AS "Degree",
iz10.inhalt AS "Degree Ori",
iz4.inhalt AS "Subject Ba",
iz5.inhalt AS "Duration",
iz6.inhalt AS "Completed",
iz7.inhalt AS "Start",
iz8.inhalt AS "End",
b.eingangdat
FROM antr a, email e, bew b
LEFT JOIN k_akfz staat3 ON (b.staukfz1 = staat3.refakfz AND staat3.sprache='E')
LEFT JOIN k_akfz staat1 ON (b.pokfz = staat1.refakfz AND staat1.sprache='E')
LEFT JOIN k_akfz staat2 ON (b.staat = staat2.refakfz AND staat2.sprache='E'),
identroll i
LEFT JOIN identzusatz iz1 ON (i.identnr=iz1.identnr AND iz1.spaltenname='BaUni')
LEFT JOIN identzusatz iz2 ON (i.identnr=iz2.identnr AND iz2.spaltenname='BaCity')
LEFT JOIN identzusatz iz3 ON (i.identnr=iz3.identnr AND iz3.spaltenname='BaCountry')
LEFT JOIN k_akfz staat ON (iz3.inhalt=staat.refakfz AND staat.sprache='E')
LEFT JOIN identzusatz iz4 ON (i.identnr=iz4.identnr AND iz4.spaltenname='BaProg')
LEFT JOIN identzusatz iz5 ON (i.identnr=iz5.identnr AND iz5.spaltenname='BaDuration')
LEFT JOIN identzusatz iz6 ON (i.identnr=iz6.identnr AND iz6.spaltenname='BaCompleted')
LEFT JOIN identzusatz iz7 ON (i.identnr=iz7.identnr AND iz7.spaltenname='BaStart')
LEFT JOIN identzusatz iz8 ON (i.identnr=iz8.identnr AND iz8.spaltenname='BaEnd')
LEFT JOIN identzusatz iz9 ON (i.identnr=iz9.identnr AND iz9.spaltenname='BaDegree2')
LEFT JOIN identzusatz iz10 ON (i.identnr=iz10.identnr AND iz10.spaltenname='BaDegree')
WHERE b.bewnr=a.bewnr
AND i.verbindung_integer=b.bewnr
AND i.identnr=e.identnr
AND b.bewsem=20222
AND a.abschl='88'
AND a.stg='228'

AND b.eingangdat between '2022-01-16' and '2022-03-15'
ORDER BY b.bewnr

 

[ebs17]

I got this part from Postgre SQL

This made me think that a "real" database would also use a real data model. But that is obviously not the case.

If dozens of instances of the same table are used, you should clean up the structure, not mess around with statements.

 

[alcalde]

I haven't done the SQL, I got it from a college who left the work. He used to work with Postgres, but I mainly use Access rather than Postgres.
Therefore I ask, whether it is possible to convert this PosstgresSQL to AccessSQL.
He used a lot of shortcuts of the same table and I never used that in Access.

Thanks

 

[sonic8]

If dozens of instances of the same table are used, you should clean up the structure, not mess around with statements.

Using multiple instances of the same base table is not an indicator of broken database design.

Imagine a database for order processing with a generic "person table". For every order there is a Person ordering, there is a Person taking the order, there is a Person packing the order, there is a Person QA checking the order, and finally there is another Person delivering the order.
So, to display the full order with all persons involved, you would need to join 5 instances of the person table. - And this is perfecly fine!

Therefore I ask, whether it is possible to convert this PosstgresSQL to AccessSQL.

The statement should be convertible. However, I believe there will be a problem with the joins using constant literals in the join criteria.
You might need to redesign these parts of the query to doing a sub query on the table with the constant as criterion first, and then join the results to the main query using only the column criteria for the join.
(And, as @ebs17 mentioned already, you must rewrite the query to use Access' horrible bracketed nested join syntax. )

 

[alcalde]

Okay, thanks a lot nice people.
I'll give a try.
Have a nice weekend

 

[ebs17]

According to the Access specifications, up to 16 links are possible in one query. You are also pushing the limits there.

 

[Pat Hartman]

The table seems to be ultra normalized. It would be interesting to know why. I built a table like that but it was for an application to support an insurance company that was constantly adding new lines of business and so with each new LOB came the potential to have new attributes. So I built a table that held each attribute as a separate row and that allowed the users to define new attributes and not require me to modify existing tables or make new ones to support the new LOB. If you look at the select clause, you can see that each field is coming from a different row of the table.