SQL Help

[LinusP]

Hi,

I was wondering if anyone could help me regarding some SQL coding. I currently have a database with two tables which are not linked. I want to run a query on table 1 one but pull out information from table 2 based on criteria value in table 1.

So in excel you would add a new column and put in a vlookup, does anyone know how you do this in SQL?

[ebasson61]

Hi, you would normally expect one table to have a reference to another. For example:

Table 1:
Name
Address1
Address2

Table 2:
Name
Date_of_birth
spouse_name

Then you can do things like this:

Select date_of_birth from table_1 t1, table2 t2
where t1.name = 'John Smith'
and t1.name = t2.name;

[LinusP]

Thats the problem as they are not referenced to each other, I was hoping for a quick way of doing it...

[panache]

Hi,

I was wondering if anyone could help me regarding some SQL coding. I currently have a database with two tables which are not linked. I want to run a query on table 1 one but pull out information from table 2 based on criteria value in table 1.

So in excel you would add a new column and put in a vlookup, does anyone know how you do this in SQL?

Not 100% without seeing your database, but from what you describe here.....

SELECT Table2.*
FROM Table1 INNER JOIN Table2
ON Table1.JoinColumn = Table2.JoinColumn
WHERE Table1.FilterColumn1 = "Criteria"

[LinusP]

Thanks for your help, I started the database again/normalised/created references and that worked.

But now I want to pull out all of the data from table 2 based on the name in table 1 and what they bought last time. Hopefully this will explain what I want to do..

Table1
John Smith
Jo Smith
Josh Smith
.....

Table2
John Smith | 16/03/14 | Pencil | 2
John Smith | 14/03/14 | Pencil | 5
John Smith | 12/03/14 | Pen | 20
Jo Smith | 15/03/14 | Pen | 2
......
...

So I want to search John Smith and pull out what he bought and what he bought last time but on the same row.

[LinusP]

Does anyone know if this is this even possible? I basically want a computed column but pull out data from another related table...

[hhh]

LinusP,

I will try and help but I do not quite follow what your end result is..?

what he bought and what he bought last time but on the same row.

Getting values in same row is possible however generally not the best practice as far as I am aware. I believe you would need to have nested select statements, something like the following:

http://stackoverflow.com/questions/8005 ... to-one-row

If I have missed the point completely, and you are after something a bunch simpler, you can either SUM numeric values such as quantity OR look at grouping the items that are of interest to you.

Table 2
Name | Date | Item | Quantity
John Smith | 16/03/14 | Pencil | 2
John Smith | 14/03/14 | Pencil | 5
John Smith | 12/03/14 | Pen | 20
Jo Smith | 15/03/14 | Pen | 2

If you are looking to retrieve a count of how many Pencils John Smith purchased such as:

SUM
http://www.w3schools.com/sql/sql_func_sum.asp

End result being:
John Smith | Pencil | 7

You probably want something like:

SELECT Table2.Name, Table2.Item, SUM(Table2.Quantity)
FROM Table1 INNER JOIN Table2
ON Table1.Name = Table2.Name
WHERE Table1.Name LIKE "%John Smith%"

GROUP BY
http://www.w3schools.com/sql/sql_groupby.asp

[LinusP]

Thanks for your help hhh, I think the sum and group function will do what I want to do so will have a play around today.

Cheers