# Mysql query union

**URL:** <https://forum.inductiveautomation.com/t/mysql-query-union/19122>\
**Category:** Ignition\
**Created:** [August 7, 2018, 1:11pm UTC](https://forum.inductiveautomation.com/t/mysql-query-union/19122 "2018-08-07T13:11:08Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![AJBerera](https://avatars.discourse-cdn.com/v4/letter/a/9fc29f/32.png) [@AJBerera](https://forum.inductiveautomation.com/u/AJBerera)\
**Post date:** [August 7, 2018, 1:11pm UTC](https://forum.inductiveautomation.com/t/mysql-query-union/19122/1 "2018-08-07T13:11:08Z")

</div>

I am trying to join a query result data with some property values in a table. The properties are just bound to tag values.  
The trouble is that I do not want to see rows returned if the value of my piece is 0 (String)  
I have tried If statements, nothing seems to work.

```auto
SELECT piece_no AS Piece, wire_type AS Type, end_time AS Time FROM productiondata_enamel
UNION
IF '{LabTests.PieceLine1}' <> '0'
BEGIN
SELECT '{LabTests.PieceLine1}' AS Piece, '{LabTests.TypeLine1}' AS Type, CURRENT_TIMESTAMP as Time
END
UNION
SELECT '{LabTests.PieceLine2}' AS Piece, '{LabTests.TypeLine2}' AS Type, CURRENT_TIMESTAMP as Time
UNION
SELECT '{LabTests.PieceLine3}' AS Piece, '{LabTests.TypeLine3}' AS Type, CURRENT_TIMESTAMP as Time
UNION
SELECT '{LabTests.PieceLine6}' AS Piece, '{LabTests.TypeLine6}' AS Type, CURRENT_TIMESTAMP as Time
UNION
SELECT '{LabTests.PieceLine7}' AS Piece, '{LabTests.TypeLine7}' AS Type, CURRENT_TIMESTAMP as Time
UNION
SELECT '{LabTests.PieceLine8}' AS Piece, '{LabTests.TypeLine8}' AS Type, CURRENT_TIMESTAMP as Time

Order BY Time asc

```

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [August 7, 2018, 3:14pm UTC](https://forum.inductiveautomation.com/t/mysql-query-union/19122/2 "2018-08-07T15:14:53Z")

</div>

I don’t think you can nest IF within a query. Try putting a WHERE clause on each SELECT.

---

<div class="post-metadata">

**Author:** ![AJBerera](https://avatars.discourse-cdn.com/v4/letter/a/9fc29f/32.png) [@AJBerera](https://forum.inductiveautomation.com/u/AJBerera)\
**Post date:** [August 7, 2018, 3:44pm UTC](https://forum.inductiveautomation.com/t/mysql-query-union/19122/3 "2018-08-07T15:44:50Z")

</div>

Tried this, LENGTH, CHAR\_LENGTH, CHARACTER LENGTH… nothing works.

```auto
SELECT piece_no AS Piece, wire_type AS Type, end_time AS Time FROM productiondata_enamel
UNION
SELECT '{LabTests.PieceLine1}' AS Piece, '{LabTests.TypeLine1}' AS Type, CURRENT_TIMESTAMP as Time
WHERE CHAR_LENGTH('{LabTests.PieceLine1}') > 0

Order BY Time asc

```

---

<div class="post-metadata">

**Author:** ![ethomason](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/ethomason/32/43338_2.png) [@ethomason](https://forum.inductiveautomation.com/u/ethomason)\
**Post date:** [August 7, 2018, 4:23pm UTC](https://forum.inductiveautomation.com/t/mysql-query-union/19122/4 "2018-08-07T16:23:39Z")

</div>

Does it work without the where clause? Are the data types of the columns you are trying to union together the same?

---

<div class="post-metadata">

**Author:** ![AJBerera](https://avatars.discourse-cdn.com/v4/letter/a/9fc29f/32.png) [@AJBerera](https://forum.inductiveautomation.com/u/AJBerera)\
**Post date:** [August 7, 2018, 5:23pm UTC](https://forum.inductiveautomation.com/t/mysql-query-union/19122/5 "2018-08-07T17:23:19Z")

</div>

Yes it works without the where clause for all the unions, and all the columns are the same type and name.

---

<div class="post-metadata">

**Author:** ![ethomason](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/ethomason/32/43338_2.png) [@ethomason](https://forum.inductiveautomation.com/u/ethomason)\
**Post date:** [August 7, 2018, 5:30pm UTC](https://forum.inductiveautomation.com/t/mysql-query-union/19122/6 "2018-08-07T17:30:06Z")

</div>

I think with MySQL you need to wrap the unions in a subquery if you want to have a where clause:

```auto
SELECT * FROM
(SELECT '234' as Piece 
UNION
SELECT '1' as Piece 
) as p2
WHERE Char_length(p2.Piece) > 1

```

---

<div class="post-metadata">

**Author:** ![AJBerera](https://avatars.discourse-cdn.com/v4/letter/a/9fc29f/32.png) [@AJBerera](https://forum.inductiveautomation.com/u/AJBerera)\
**Post date:** [August 7, 2018, 5:33pm UTC](https://forum.inductiveautomation.com/t/mysql-query-union/19122/7 "2018-08-07T17:33:47Z")

</div>

Thanks, I did get this to work though:

```auto
SELECT piece_no AS Piece, wire_type AS Type, end_time AS Time FROM productiondata_enamel
UNION
SELECT IF ('{LabTests.PieceLine1}' AND '{LabTests.TypeLine1}','{LabTests.PieceLine1}', Null) AS Piece, IF ('{LabTests.PieceLine1}' AND '{LabTests.TypeLine1}','{LabTests.TypeLine1}', Null) AS Type,
 IF ('{LabTests.PieceLine1}' AND '{LabTests.TypeLine1}',CURRENT_TIMESTAMP, NULL) as Time
UNION
SELECT IF ('{LabTests.PieceLine2}' AND '{LabTests.TypeLine2}','{LabTests.PieceLine2}', Null) AS Piece, IF ('{LabTests.PieceLine2}' AND '{LabTests.TypeLine2}','{LabTests.TypeLine2}', Null) AS Type,
 IF ('{LabTests.PieceLine2}' AND '{LabTests.TypeLine2}',CURRENT_TIMESTAMP, NULL) as Time

```