• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 261
  • Last Modified:

Select against multi tables causing duplicates

Hi, I have a SELECT going against a couple tables, and I am getting duplicate records back.
Can someone help me re-write the SQL to get back distinct records?

thanks!

Table1: cdrm_successor_borrower - Columns: sb_id, sb_name
Table2: cdrm_pool_sb - Columns: sb_id, pool_id, isDefault


SELECT DISTINCT sb.sb_name, psb.isDefault, sb.sb_id FROM cdrm_successor_borrower sb, cdrm_pool_sb psb WHERE sb.sb_id in (SELECT sb_id FROM cdrm_pool_sb WHERE pool_id = '1000') ORDER BY sb.sb_id


I am trying to get back all the records from the "bridge" table (cdrm_pool_sb) where the pool_id = 1000, in this case!

thanks,
cdfllc
0
cdfllc
Asked:
cdfllc
  • 4
  • 4
1 Solution
 
NavicertsCommented:
SELECT sb.sb_name, psb.isDefault, sb.sb_id FROM cdrm_successor_borrower sb INNER JOIN cdrm_pool_sb psb ON sb.sb_id = psb.sb_id WHERE sb.sb_id = '1000' ORDER BY sb.sb_id
0
 
cdfllcAuthor Commented:
Navicerts , thanks for replying.
However, I wanted to get records from the cdrm_pool_sb table, based on the pool_id.

I tried the following, but it gave me all the records in the cdrm_pool_sb table...

SELECT sb.sb_name, psb.isDefault, sb.sb_id FROM rcdrm_successor_borower sb, cdrm_pool p INNER JOIN cdrm_pool_sb psb ON p.pool_id = psb.pool_id WHERE p.pool_id = '1000' ORDER BY sb.sb_id

can you maybe adjust this query to give me only the records from the cdrm_pool_sb where the pool_id=1000 ?

thanks!
0
 
cdfllcAuthor Commented:
Essentially, this is what I want to do:

SELECT sb.sb_name, sb.sb_id, psb.isDefault FROM cdrm_successor_borrower sb, cdrm_pool_sb psb WHERE sb.sb_id in (SELECT sb_id FROM cdrm_pool_sb WHERE pool_id = '1000')

But the above query gives duplicates... I assume because I am querying the two different tables...
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
NavicertsCommented:
the query i posted will give you results where there are matching pool_id's in cdrm_pool_sb and cdrm_pool.

those records will be limited to one where the pool_id (in either table) is = 1000


I thought this was what you were going for? if this is the case check your data to make sure your not expecting results that the data dosent support.

If this is not the case can you expand and maybe give a data example of what your desired results are?

-Navicerts
0
 
cdfllcAuthor Commented:
cdrm_pool_sb:
sb_id          pool_id         isDefault
------          ---------        -----------
1111           1000            0
2222           1000            1
3333           2000            0
4444           2000            1
5555           2000            0


cdrm_successor_borrower:
sb_id          name
------          ---------
1111           sbname1
2222           sbname2
3333           sbname3


So - I want to get this information from my query to put in a grid... these should be my results where the cdrm_pool_sb.pool_id =1000
sb_id          sb_name      isDefault
------          ---------        -----------
1111           sbname1            0
2222           sbname2            1


The part that is throwing me off is the fact that the sb_name, and the isDefault are coming from two different tables....
Hope that explains it well...
0
 
NavicertsCommented:
SELECT
             1.sb_id,
             2.sb_name,
             1.isDefault
FROM
             cdrm_pool_sb 1
INNER JOIN
             cdrm_successor_borrower 2
ON
             1.sb_id = 2.sb_id
WHERE
             1.pool_id = 1000
0
 
NavicertsCommented:
thats the same query i guess, it should be working fine, im still convinced the problem lies within the data.  you could always "cheat" and do this if sb_id AND sb_name AND isDefault are being duplicated..

SELECT
             1.sb_id,
             2.sb_name,
             1.isDefault
FROM
             cdrm_pool_sb 1
INNER JOIN
             cdrm_successor_borrower 2
ON
             1.sb_id = 2.sb_id
WHERE
             1.pool_id = 1000
GROUP BY
             1.sb_id,
             2.sb_name,
             1.isDefault
0
 
cdfllcAuthor Commented:
Yep, I got it... this is my final version...

SELECT sb.sb_name, sb.sb_id, psb.isDefault FROM cdrm_successor_borrower sb INNER JOIN cdrm_pool_sb psb ON psb.sb_id = sb.sb_id
WHERE psb.pool_id = 1000


Thanks for your help!

cdfllc
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now