SQL noob query help. I always forget how to join correctly.

[dalearyous]

i come in and out of SQL and seem to forget it very quickly. anyway, example:

lets say i have a table with 2 columns: task code and description like so:

taskcode description
1 travel
2 meals
3 internet

my second table has taskcode and budget like so:

taskcode budget
1 500
2 100
3 50

i am doing a selec tstatement on the second table and would like my result to be:
taskcode description budget

my first attempt:

select table2.taskcode, table1.description, table2.budget
from table1, table2
where table1.taskcode=table2.taskcode

i get the result i want but there are duplicates obviously. i know i need to group by or aggregate but can't quite get it to work. thoughts?

 

[PTNL]

It sounds like you're describing a cross join, but your SQL statement is an older syntax of an INNER JOIN. I'm just not seeing the problem you're describing with the duplicate data.

Here's my output using your sample data, schema, and query:

1  travel  500
2  meals  100
3  internet  50

Is there something else going on?

 

[ambientZ]

which sql database are you using?

 

[dandragonrage]

don't put your joins in the where clause; use the on clause. It can affect how efficiently the query gets optimized (depending on which DB) and is typically more readable. Though it won't matter too much if your tables are small like that...

 

[dalearyous]

so what is happening in SQL builder is this:

table1 INNER JOIN
table2 ON table1.taskcode = table2.taskcode

the results pulls far more results because table 2 has more detailed line items than table 1. the only reason i am joining is to grab the budget for each taskcode and description from table 1 ONLY. table 2 has far more entires.

*edit*
i could use select distinct but i don't like doing that

 

[PTNL]

I think I know what you are trying to describe, but the problem still seems unclear. Give us some sample data that shows the problem, and what you want the result output from the SQL statement to look like. (If there are any data rollups or logical rules, include extra details on that as well.)

 

[dandragonrage]

so what is happening in SQL builder is this:

table1 INNER JOIN
table2 ON table1.taskcode = table2.taskcode

the results pulls far more results because table 2 has more detailed line items than table 1. the only reason i am joining is to grab the budget for each taskcode and description from table 1 ONLY. table 2 has far more entires.

Is the budget broken down by line item in table 2 such that it all needs to be added together? Like:

select sum(table2.budget) as budget, table1.taskcode
from table1
inner join table2 on (table1.taskcode = table2.taskcode)
group by table2.taskcode [might need to group by table1.taskcode or both]

 

[dalearyous]

so its basically an issue, in my actual problem, where the only table the entityID is related to the entity_description is a table that for what i am doing, is useless (called table2). other than the fact that it shows the description for each entityID.

my other table (table1) that i am using has entityID and then all the other useful columns. so i just want my final result to have the description along with the entityID.

if you do:
select *
from table1

it returns 159 rows WITHOUT the entity description. i want my final query to return 159 rows WITH the entity description for each entityID.

if you do:
select *
from table2

i get over 12,00 rows.

*edit*
@danddragonrage - no its a simple select statement.