Link to home
Start Free TrialLog in
Avatar of mig1980
mig1980

asked on

SQL Query question

Good day. So I have two tables in a database and I am trying to join them together. I am trying to tie in 2 columns from the first table and one column from the second table

1) Want all of the records from the first table and associate the column from the second table to it.

Currently I have the following:

select distinct i.reg, pp.idnum, pp.status
from a057 pp left join i
on pp.idnum=i.idnum

where  pp.status in ('1', '21', '22')
order by i.reg, pp.idnum

Open in new window


I get about 10000 more records than I should (than are in the a057 table). I only want the amount of records that are in the a057 table with the corresponding i.reg column associated with the pp.idnum column
SOLUTION
Avatar of appari
appari
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Is idnum the primary key of a057?  If not, what is the primary key?
Avatar of mig1980
mig1980

ASKER

Yes there are multiple records in the table i
Avatar of mig1980

ASKER

idnum is the primary key for a057 but table i has multiple primary keys.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of mig1980

ASKER

I will need to add another table to be able to determine the record I need. How can add another table without skewing what's already there.
post the other table structure and indicate the columns to be used in join conditions.
Avatar of awking00
Some sample data and the expected results would be most helpful.
Avatar of mig1980

ASKER

The data and columns are confidential. Cannot be posted so I have to recreate everything I post here.

The new query would look something like this, but it still about 1000 records under what it should be (from what I want out of the first table a057)


select distinct i.reg, pp.idnum, pp.status
from a057 pp left join i
on pp.idnum=i.idnum left join
sm on sm.id_num=i.id_num and sm.part=i.part and sm.reg=i.reg
where  pp.status in ('01', '21', '22') and sm.fiscalyear='1011'
order by i.reg, pp.idnum

Open in new window



As far data in each of the columns, it is all varchar.

pp.Status holds from 01-27, i.reg holds 01-37, pp.idnum (these are unique for the pp table 11 digit number), id_num 7 alphanumeric characters that can have multiple records, the part field is 1-5 and narrows down which id_num I am looking for but there can also be multiple records with both elements combined, reg field narrows down id_num and part to one record.

Let me know if there are any other questions.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of mig1980

ASKER

Thank you all for your help. I was able to get the output I needed with your help.