Joint tables except the empty records (null)

Question

I have two tables (1-N relationship).

First (ID,name,surname),

Second (ID,Job(s),role,society).

In my app I want to merge table1 and table2 (based on the id that binds the two tables) BUT i want to hide the columns that COULD be empty.

Example: join (in this case, i don't want to show 'ruolo/grado')

How I wrote the code for this:

CREATE PROCEDURE spEstraiPbyId
    @Id int 
    as
    begin
    SELECT * from Persone  
    join Lavori on Persone.Id = @Id and Lavori.IdPersona=@Id
    end

PS: I have already seen several similar questions on the internet but no answer was able to satisfy my request or I did not understand correctly. I hope you can help me willingly.

As with a normal `SELECT` query, you don't need to do a `SELECT *` and can instead specify specific columns. Is that what you're after? — Broots Waymb, Jun 27 '19 at 12:54
@BrootsWaymb Doing so I have to write a single query for each ID (Sometimes there is a null _here_ other times there is a null _there_). — , Jun 27 '19 at 12:57
So you need to exclude entire column if at least one row has NULL in the column? Or exclude it if the col is declared nullable no matter what real data are like? — Serg, Jun 27 '19 at 12:57
@VDWWD As I said the problem is that there are different cases.. When in my app I lounch the query idk if a column is null or not.. I need a sort of 'except' i think(?) — , Jun 27 '19 at 13:04
@Serg The first option. Only 'residenza' in the 1st and 'ruolo' + 'società' in the 2nd are NULLable. — , Jun 27 '19 at 13:06
Then the client side is the proper place to do it. Check the resultset (DataTable , etc) and drop columns with null[s] — Serg, Jun 27 '19 at 13:09

score 0 · Answer 1 · answered Jun 27 '19 at 12:59

0

If I understand it correctly, you want to do something like this: http://sqlfiddle.com/#!18/04141/3

SELECT * from Persone  
join Lavori on Persone.Id = Lavori.IdPersona where Lavori.Job is not null

First, use the on joining the keys, and then filter with a where those that are not null :)

answered Jun 27 '19 at 12:59

Kaitiff

416
3
9

I tried but doing so, if the job is null, I cant see any record (no results). Instead I want to see the records excepts the nulls one. – Jun 27 '19 at 13:19

score 0 · Accepted Answer · answered Jun 28 '19 at 09:35

Thank you all. Best tip was written by @Serg because to do this i worked on client side, with DataReader and Datatable objs:

 DataTable tbl = new DataTable()    
SqlCommand cmd = new SqlCommand("spEstraiPById", cnn); //See at the end for spEstraiPById
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("Id",txtNickname.Text);
                    using (SqlDataReader rdr = cmd.ExecuteReader())
                    { //Add columns to the table
                        tbl.Columns.Add("ID");
                        tbl.Columns.Add("Nome");
                        tbl.Columns.Add("Cognome");
                        tbl.Columns.Add("Residenza");
                        tbl.Columns.Add("Professione");
                        tbl.Columns.Add("Ruolo");
                        tbl.Columns.Add("Società");

                        while (rdr.Read())
                        { //from DB to Table
                            DataRow drw = tbl.NewRow();
                            drw["ID"] = rdr["Id"];
                            drw["Nome"] = rdr["Nome"];
                            drw["Cognome"] = rdr["Cognome"];
                            drw["Residenza"] = rdr["Residenza"];
                            drw["Professione"] = rdr["Professione"];
                            drw["Ruolo"] = rdr["Ruolo/Grado"];
                            drw["Società"] = rdr["Società"];
                            tbl.Rows.Add(drw);
                        }
                        foreach (DataRow row in tbl.Rows) //Deleting empty records
                        {
                            for (int col = tbl.Columns.Count - 1; col >= 0; col--)
                            {
                                if (row.IsNull(col))
                                {
                                    tbl.Columns.RemoveAt(col);
                                }
                            }
                            // No need to continue if we removed all the columns
                            if (tbl.Columns.Count == 0)
                                break;
                        }
                    }
                        gw1.DataSource = tbl;
                        gw1.DataBind();
                        cnn.Close();
//=Stored Procedure 
CREATE PROCEDURE spEstraiPbyId
    @Id int 
    as
    begin
    SELECT * from Persone 
    join Lavori on Persone.Id = @Id and Lavori.IdPersona=@Id
    end

Joint tables except the empty records (null)

2 Answers2