'UNION based on just one column

I want to UNION two tables based on a single column.

Assume that, I have a table called t1:

Id |  Name  
------------
1  |    A   
2  |    B  
3  |    C

And a second table called t2:

Id |  Name  
------------
1  |    B   
3  |    B  
5  |    B

I want to UNION them like this:

SELECT * FROM T1
UNION
SELECT * FROM T2
BASED ON ID

And I expect a result like:

Id |  Name  
------------
1  |    A   
2  |    B  
3  |    C 
5  |    B

If IDs are equal then pick the row from the first table:

Actually, I am working with tables which have 20+ columns. These tables are for demonstration.

mysqlsqlpivotsubqueryunion


Solution 1:[1]

from t1
union
select id, name
from t2

Solution 2:[2]

select id, name
from t1
union all
select id, name
from t2
where not exists (select 1 from t1 where t1.id = t2.id)

Solution 3:[3]

                <h6 class="card-title"><span>Category</span></h6>
                <select class="form-select" name="cat" id="sel1" onchange="giveSelection(this.value)" required />
                <option></option>
                <?php 
                    $query1 = sqlsrv_query ($conn, "SELECT * FROM dbo.category");
                    while ($row1 = sqlsrv_fetch_array($query1)){
                ?>
                <option value="<?php echo $row1['cat']; ?>"><span><?php echo $row1 ['catname'];?></span> </option>
              <?php } ?>
                </select>

                <h6 class="card-title"><span>Name</span></h6>
                <select class="form-select" name="name" id="sel2" required />
                <option></option>
                <?php 
                    $query2 = sqlsrv_query ($conn, "exec sample");



                    while ($row2 = sqlsrv_fetch_array($query2)){
                ?>
                <option data-option="<?php echo $row2['cat']; ?>"><span><?php echo $row2 ['name'];?></span> </option>
              <?php } ?>
                </select>

                <?php 
                    $query3 = sqlsrv_query ($conn, "exec sample");



                    while ($row3 = sqlsrv_fetch_array($query3)){
                ?>


                <h6 class="card-title"><span>Selling Price</span></h6>
                <input type="text" class="form-control" name ="price" value="" disabled/ >

                      <?php } ?>

                <h6 class="card-title"><span>Grams</span></h6>
                <input type="text" name="kilo" class="form-control" placeholder="---required for menu---">

                <h6 class="card-title"><span>Quantity</span></h6>
                <input type="text" name="quantity" class="form-control" placeholder="---required for grocery---">

                <h6 class="card-title"><span>Price</span></h6>
                <input type="text" name="price" class="form-control" disabled />
                <br>

            <div align="right">
              <button type="reset" class="btn btn-outline-danger btn-sm">&nbsp;&nbsp;&nbsp;&nbsp;Cancel&nbsp;&nbsp;&nbsp;&nbsp;</button>&nbsp;

              <button type="submit" name="submit0" class="btn btn-outline-primary btn-sm">&nbsp;&nbsp;&nbsp;&nbsp;Save&nbsp;&nbsp;&nbsp;&nbsp;</button>
            </div>
            </div>
        </form>

ON SELLING PRICE HOW CAN I DISPLAY JUST THE VALUE OF THE PRICE FROM MY STORE PROCEDURE WITH UNION ALL

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Shukri Yusof
Solution 2 anneceraaaaa
Solution 3 sicna

Related Questions