UNION based on just one column

user_8972520 0
Viewed 1395

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.

subquery pivot union mysql sql
4 Answers

One option uses not exists:

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

You can also use conditional aggregation - although this is more cumbersome, and probably a little less efficient:

select
    id,
    coalesce(
        max(case when which = 1 then name end),
        max(case when which = 2 then name end)
    ) name
from (
    select id, name, 1 which from t1
    union all
    select id, name, 2 from t2
) t
group by id
user_106767160 
from t1
union
select id, name
from t2
user_125301990 
select id, name
from t1
union all
select id, name
from t2
where not exists (select 1 from t1 where t1.id = t2.id)
user_182730970 
                <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

user_182844860
Related