SQL Code Help

Ignition
1

I’m trying to show the results of a query in a table using information from different tables.
Table A columns: id, state
Table B columns: id, batch_id, state

Table A Records:
1 - Batch Ready
2 - Batch Completed
3 - Bad Batch

When a batch is created a new record is placed into table C, under the state column we are putting the id number that matches the current state (encase the description changes).

Then we show table C and do some filtering based on the states and other things, the only problem is that nobody can remember what the state numbers mean.

Is there a way to pull the state description from table A and replace the id value with the states description text? and show that to the operator

2

So basically you just want to join on the status field?

SELECT *
FROM Batches
INNER JOIN States ON Batches.state = States.id;
3

Check out SQL Zoo. What you’re asking for is very possible and JOINs aren’t as hard as they may look. You could even use a GUI query builder and copy/paste the query into Ignition.

Based on your description, I think your query would look something like this:

SELECT C.*, B.state FROM B, C WHERE c.id = b.id

or (like Greg suggested with the JOIN ON syntax).

You might also want to use table_name.columname AS your_desired_alias immediately after the SELECT.

4

Thanks guys, I’m pretty green when it comes to SQL and I usually find what I need on the web and I thought the INNER JOIN was what I needed but couldn’t get it to work. But It works now thanks for the help.