DB connectivity toolkit: syntax error in a SELECT data from joined tables

[Giuvan]

Hello everyone

I'm trying to put in labview an SQL query on joined tables.

As example I take a DB for storing the data of 2on2 basketball games, whose tables are

matches(matchId,teamA,teamB)

teams(teamId,PlayerAname,PlayerBname,Nationality)

nationalities(NatId,natName)

To get a result table with the match number and the player names along with their nationality I use this query on MySQL (which works on the command line interface)

SELECT MatchID,
                 t1.PlayerAName, t1.PlayerBName, n1.natName,
                 t2.PlayerAName, t2.PlayerBName, n2.natName
FROM matches m
INNER JOIN teams t1 ON t1.teamID = m.teamA
INNER JOIN teams t2 ON t2.teamID = m.teamB
INNER JOIN nationalities n1 ON n1.natID = t1.nationality
INNER JOIN nationalities n2 ON n2.natID = t2.nationality

When I put it in labview, using the "select data" block, I get a syntax error as shown in the attached screenshot.

Am I mistanking something in using the JOIN statements, or the aliases?

Thanks in advance!

[tst · Accepted solution]

The select VI is designed to be a simplified way to select from a table. I doubt it can do joins (and it certainly wouldn't need the "FROM", even if it can). What you can do instead is call the execute query VI and give it the full SQL query. I don't remember if it returns the data in the recordset, so you might have to do that separately.

[Giuvan]

Hi tst!

I wasn't realizing that I forgot to take out the FORM while copying'n pasting from mysql (guess what,I was doing this to try to avoid typos!). Now it works all right!

Believe it or not that simple select block can handle both the aliases and all types of JOIN! I do't like very much the chain of blocks execute query+n*(fetch element)+free object, so I try to avoid that as much that I can...

Thank you a bunch, have a good week end!

[tst]

---

@Giovasa wrote:

I do't like very much the chain of blocks execute query+n*(fetch element)+free object, so I try to avoid that as much that I can...

---

Sounds like a prime candidate for a subVI. You don't have to use it every time, but it probably would help for cases where you do want to put the SQL query directly in code.

Incidentally, if you're doing joins, you might consider using views, as that allows you to hide the details of the interactions of the tables in the DB itself.