Syntax Problem with SQL query (INNER JOIN) in UDTable

Hi there,

maybe someone can help me with the syntax of the sql query with INNNER JOIN. If I use only one table in the sql query everything works fine. But if I tried to join the tables with INNER JOIN I dont’ know how to to get it work! I tried it for example with
Modell = $_.M.Modell
but it doesn’t work

$Data = Invoke-DbaQuery -SqlInstance localhost\SQLEXPRESS -Database test -Query "select M.Modell, M.Beschreibung, G.IMEI FROM Modelle AS M INNER JOIN Geraete AS G ON M.Modell = G.Modell" | ForEach-Object {

                @{ 
                    Modell = $_.M.Modell
                    Beschreibung = $_.M.Beschreibung
                    IMEI = $_.G.IMEI
                }
            }

        $Columns = @(
            New-UDTableColumn -Property 'Modell' -Title 'Modell' -ShowSort -IncludeInExport -IncludeinSearch -ShowFilter -Filtertype text
            New-UDTableColumn -Property 'Beschreibung' -Title 'Beschreibung' -ShowSort -IncludeInExport -IncludeinSearch -ShowFilter -Filtertype text
            New-UDTableColumn -Property 'IMEI' -Title 'IMEI' -ShowSort -IncludeInExport -IncludeinSearch -ShowFilter -Filtertype select
        )
        New-UDTable -Id 'TBSQLAbfrageModelleTest1' -Data $Data -Columns $Columns -ShowSearch -ShowPagination -Dense -Export

Hello @TiX without actually knowing if the data in Geraete is the same as the data in M.Modell it’s impossible to say if this is a valid join…Do you have access to SSMS where you could just try the query?
I have a bunch of examples using database queries on this page of my entire project I uploaded to github here:-

I do have an inner join on this example page, I hope this is of some assistance to you.

@psDevUK thank’s for your anwser and examples, yes the query works in SSMS

select M.Modell, M.Beschreibung, G.IMEI
FROM Modelle AS M INNER JOIN Geraete AS G
ON M.Modell = G.Modell 

also in the UDTable it shows in the search field “search 1 records…” but it isn’t display something.

I think the syntax
Modell = $_.M.Modell
is maybe wrong?

Hi @TiX just to stay in line with post requests…you ok to specify the version you running like Universaldashboard or Powershell Universal and the version you running at. I assume it is the latter and been looking at the docs here:-
https://docs.ironmansoftware.com/dashboard/components/data-display/table#simple-table
But personally not running on PU yet but I have plenty of UD examples I could share from SQL

sorry I forgot to specify that

PSU 1.5.8
UD Framework 3.2.5

I orientated myself on this example

but without server-side processing

if you try the example with server side rendering does it work ?

the same with server side rendering, no data is displayed if I use more than one sql table / inner join

Just to rule out any weirdness…would you be willing to try this solution to display in a table:-
https://marketplace.universaldashboard.io/Dashboard/UniversalDashboard.GoogleTable
Or just to prove not SQL or something, have you exported to CSV then populate the table via the CSV file…

1 Like

@psDevUK
Good idea with the CSV export. My workaround now is export the data / sql query as csv and then import it with import-csv… and then UDTable works as exapected :slight_smile:

1 Like