How to i use 2 tables on a select mysql syntax?

Question

0.00/5 (No votes)

See more:

Hello,

I have this piece of code, where it shows my sql query string, but how can i setup the query to show the data from 2 tables?(entradas,saidas)

C#

string SQL = "SELECT FROM entradas,saidas(entradas.(id_veiculo,id_empresa,nome_condutor,empresa_visitante,empresa_visitar,visitado,ncartao,data,hora,obs,sector), saidas.(DATA,HORA,OBS)) WHERE id_empresa = @tabempresa; ";

using (var cn = new MySqlConnection("server=localhost;user id=root;password=12345;persistsecurityinfo=True;database=portaria;allowuservariables=True"))
{
    using (var cmd = new MySqlCommand(SQL, cn))
    {
        cmd.Parameters.AddWithValue("@tabempresa", tabControl1.SelectedIndex);
        MySqlDataAdapter sda = new MySqlDataAdapter();
        DataTable dt = new DataTable();
        sda.SelectCommand = cmd;
        sda.Fill(dt);
        cn.Open();

        try
        {
            DataView dv = new DataView(dt);
            Grid1.DataSource = dv.Table;
            Grid1.AutoGenerateColumns = true;
            cmd.ExecuteNonQuery();
        }
        catch (MySqlException ex)
        {
            // Do some logging or something.
            MessageBox.Show("There was an error accessing your data. DETAIL: " + ex.ToString());
        }
        cn.Close();
    }
}

What I have tried:

tried to use normal select syntax to show up both tables on one datagrid.

Posted 1-Mar-16 1:03am

Scribling Doodle

Updated 1-Mar-16 1:10am

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Herman<T>.Instance · Accepted Answer · 2016-03-01T01:10:00

Solution 1

Change query to:

SQL

SELECT * FROM entradas a LEFT OUTER JOIN saidas b
ON a.data = b.data AND a.hora=b.hora and a.sector = b.sector
WHERE id_empresa = @tabempresa;

NAMED:

SQL

SELECT a.id_veiculo, a.id_empresa, a.nome_condutor, a.empresa_visitante, a.empresa_visitar, a.visitado, a.ncartao, a.data, a.hora, a.obs,a.sector, b.data as Data1, b.hora as hora1 FROM entradas a LEFT OUTER JOIN saidas b
ON a.data = b.data AND a.hora=b.hora and a.sector = b.sector
WHERE id_empresa = @tabempresa;

Posted 1-Mar-16 1:10am

Herman<T>.Instance

Updated 1-Mar-16 2:20am

v3

Comments

Scribling Doodle 1-Mar-16 7:12am

Do i need to use "inner join" or "full join" to work? I'm not much into sql join structure...

Herman<T>.Instance 1-Mar-16 7:14am

what is in entradas? What is the Key that both tables connect?
I did change the query based on the column names you gave.

Scribling Doodle 1-Mar-16 7:15am

The sql query doesn't work, what could be the problem here? I want to filter data from 2 tables, one that has the car join values and the exit values, in "DATA and HORA" i want it to be joined like car joined at 13.3.2016 / 22:15 and left at 14.3.2016 / 22:15

Herman<T>.Instance 1-Mar-16 7:16am

query is changed now

Scribling Doodle 1-Mar-16 7:21am

https://gyazo.com/fce36c6b8c311d33e70ba1721b84bfe8 Here you can see all the tables i have, the 2 that have the columns are the ones i want to use to show data in my datagrid. Hope it helps :)

Scribling Doodle 1-Mar-16 7:37am

Yeah it works now, but i have a problem... I want the table to show like on this printscreen, how am i gonna do it? https://gyazo.com/56c1afc8aa9436a58a833c133dc3e44b

Herman<T>.Instance 1-Mar-16 8:20am

added NAMED part in solution

Scribling Doodle 1-Mar-16 8:40am

Thank you so much dude! God bless ya!

Scribling Doodle 1-Mar-16 8:44am

One more question, is it possible to show only values where the date is less or equal to the "current date"? For example, i have 10 values between 10-10-2014 and "current date" and i want to show the values on a daily basis. With that in mind i will only have in this case the values from 29-02-2016 and 01-03-2016.

EDIT: This NAMED query seem to create an exception timeout problem, is any way to fix it?

Herman<T>.Instance 2-Mar-16 4:30am

What is the name of the date field?
How many records are involved in the query? How is your indexplan set up?

Scribling Doodle 2-Mar-16 7:04am

i have 2M+ records on my database, is that a problem? I have no indexes because i dont know how to use them. The date field is called "data" as you wrote, it belongs to a.data

Herman<T>.Instance 2-Mar-16 9:00am

2 million rows without Indexes is costly....CREATE INDEX ! Search for that

Herman<T>.Instance 2-Mar-16 9:02am

SELECT a.id_veiculo, a.id_empresa, a.nome_condutor, a.empresa_visitante, a.empresa_visitar, a.visitado, a.ncartao, a.data, a.hora, a.obs,a.sector, b.data as Data1, b.hora as hora1 FROM entradas a LEFT OUTER JOIN saidas b
ON a.data = b.data AND a.hora=b.hora and a.sector = b.sector
WHERE id_empresa = @tabempresa;
ORDER BY a.data

Is that what you mean?

Scribling Doodle 2-Mar-16 11:55am

Yeah, thank you so much dude! I've figured a way faster then indexes, created a table to store all data and be accessed when they want to check older data, and this new one is to store the daily data, which is directly connected to the old one, making it easier to insert data, but slower to search, but its fine for now ;)