How to do select with related tables

Question

How to do select with related tables

Navigation

#1 by (2 votes)

1

I have two tables (client) and (invoice), to register an invoice I am doing it this way:

SqlCommand query = new SqlCommand("insert into factura (identificacion, nombrecp, " + "fecha, iva, tipo, vtotal, descripcion, subtotal) " +
                                              "values ('" + idCliente.Text + "', '" + nombreCliente.Text +
                                              "', '" + fecha.Text + "', '" + IVA.Text + "', '" + tipoFactura.Text +
                                              "', '" + totalPagar.Text + "', '" + descripcion.Text +
                                              "', '" + Subtotal.Text + "')", conexion);

I have not found a way to make a query that by the invoice number tells me which client is associated, the value and the product that I buy. I searched the internet and what I find everything I find every time enmeshes me more. The table is related 1: M, I hope you can help me as soon as possible! : (

the code I'm using for the query is this:

select identificacionc, nombre
from cliente cli inner join factura fac
on fac.identificacion = fac.identificacion
where numero = 32;

I look at a page I tried to use it but I do not understand the whole query well and it does not show all the data I need.

sql-server asp.net

asked by Edward Sc 21.11.2018 в 04:35

source

1 answer

Doubts about Error in javaScript, Positioning stuck to the top inside a CSS div

score 2 · Answer 1

To get what you mention in the comments; given a number of invoice get customer data

We use a INNER JOIN

The use of Inner Join allows us to make a match of what exists in the table on the left with the table on the right and then show me all the records

Code

SELECT cliente.nombre, factura.identificacion, factura.nombrecp, factura.fecha
       factura.iva, factura.tipo, factura.vtotal, factura.descripcion, factura.subtotal
       factura.estado
FROM cliente
INNER JOIN factura ON cliente.identifcacionc = factura.identificacion
WHERE factura.numero = 32;

EXPLANATION

By doing a SELECT ideally I do this tabla.columna

I make a FROM of the table cliente

The INNER JOIN I do with the table invoices

Use ON to indicate that the JOIN is carried out in cliente.identificacionc = facturas.identificacion because one is the primary key and the other the foreign key that are joining both tables

At the end so that all the previous query only shows the matches of a specific record I use the WHERE and I add that it shows all donde invoice.number = 32

Try it first from your database manager and tell me anything