Solved ! Help with subselect/Joins

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Solved ! Help with subselect/Joins

Author

Topic

wishmaker
Starting Member

7 Posts

Posted - 2011-12-15 : 06:52:12

I have 3 tables I need to extract data from:

d1 contains donor & donor_no
d4 contains donor & contact_id & prime
c Contains donor_no & contact_id & contact_detail

I've used (in) to select records from d1 as this is the only file contains the 2 refs I need to lookup from the 2 other tables: d4.donor & c.donor_no

Finally my question: can you help me to get a file that contains:
d1.donor, d1.donor_no, c.contact_details
where d1.donor in (to many to list) and d4.prime = 'Y'
and d4.contact_id = c.contact_id

Thank you in advance
John

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-15 : 06:56:26

[code]
select d1.donor, d1.donor_no, c.contact_details
from d1
inner join c
on c.donor_no = d1.donor_no
inner join d4
on d4.donor = d1.donor
and d4.contact_id = c.contact_id
where d4.prime = 'Y'
and d1.donor in (select values from listtable)
[/code]

listtable should be created based on values you want to include in list

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

wishmaker
Starting Member

7 Posts

Posted - 2011-12-15 : 08:05:17

Thank you!

But I messed up. Sorry, it should have been c.contact_id = 3 and not d4.contact_id = c.contact_id

d1.donor, d1.donor_no, c.contact_details
where d1.donor in (to many to list) and d4.prime = 'Y'
and c.contact_id = 3

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-15 : 09:09:42

[code]
select d1.donor, d1.donor_no, c.contact_details
from d1
inner join c
on c.donor_no = d1.donor_no
inner join d4
on d4.donor = d1.donor

where d4.prime = 'Y'
and c.contact_id=3
and d1.donor in (select values from listtable)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

wishmaker
Starting Member

7 Posts

Posted - 2011-12-15 : 09:38:52

It seems to be ignoring d4.prime = 'Y'

I'm getting all the results from the d1.donor_no with there c.contact_detail and c.contact_id = 3

Thanks

Sample of my code

select d1.donor, d1.donor_no, c.contact_detail
from don0001 d1
left join contnumb c
on c.donor_no = d1.donor_no
left join don0004 d4
on d4.donor = d1.donor
where d4.prime = 'Y' 
and  c.contact_id = 3
and d1.donor_no in ('61359','41834','90810')

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-15 : 10:07:40

why have you used left instead of inner join? that made the difference i guess?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

wishmaker
Starting Member

7 Posts

Posted - 2011-12-15 : 10:15:22

quote:
Originally posted by visakh16

why have you used left instead of inner join? that made the difference i guess?

I was trying different options to get the results but no matter what join I used the results are the same.

I'm not sure if this will help but here some background for the tables
D1 = Company records
c = Contact details (Each comtact can many entries but only one at 3)
d4 = company contact (Each company can many entries but only one prime)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-15 : 10:36:10

[code]
select d1.donor, d1.donor_no, c.contact_detail
from don0001 d1
inner join contnumb c
on c.donor_no = d1.donor_no
and c.contact_id = 3
inner join don0004 d4
on d4.donor = d1.donor
and d4.prime = 'Y'
where d1.donor_no in ('61359','41834','90810')
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

wishmaker
Starting Member

7 Posts

Posted - 2011-12-15 : 11:09:01

quote:

Originally posted by visakh16


select d1.donor, d1.donor_no, c.contact_detail
from don0001 d1
inner join contnumb c
on c.donor_no = d1.donor_no
and c.contact_id = 3
inner join don0004 d4
on d4.donor = d1.donor
and d4.prime = 'Y' 
where d1.donor_no in ('61359','41834','90810')

--------------------------------------------------------

This still has the same result, where it lists every contact (c.contact_id) = 3 even though only one of them is prime. Example below

donor donor_no contact_detail
LING 31023 gerald.kosky@lingdesign.co.uk
CLOTHES 37170 rfeiner@clothesaid.co.uk
CLOTHES 37170 msymons@clothesaid.co.uk
CLOTHES 37170 MLomotey@clothesaid.co.uk
CLOTHES 37170 SSmith@clothesaid.co.uk
CLOTHES 37170 jadam@clothesaid.co.uk
SHP LTD 46289 simon.x@shpforcharity.co.uk
SHP LTD 46289 simon.x@shpforcharity.co.uk
FLIP 49691 tim.x@flipsidegroup.com
FLIP 49691 tim.x@flipsidegroup.com
FLIP 49691 mark.x@flipsidegroup.com
BLUE 50550 nxxxx@bluerevolution.com
BLUE 50550 nxxxx@bluerevolution.com
AON 61359 sally.xxx@aon.co.uk
AON 61359 rochelle.xxx@aonconsulting.co.uk
AON 61359 katherine.xxx@aon.co.uk

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-15 : 11:37:02

can you give sample data for these from different tables?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

wishmaker
Starting Member

7 Posts

Posted - 2011-12-15 : 12:44:27

quote:
Originally posted by visakh16

can you give sample data for these from different tables?
------------------------------------------------------------------------

Solved IT missing validation on another field.

Select d1.donor_no, d1.donor , c.Contact_detail
from don0001 d1
inner join contnumb c 
on d1.donor_no = c.donor_no and c.contact_id = 3 
inner join don0004 d4 
on d4.donor = d1.donor and d4.contact_no = c.which_contact  
where d4.prime = 'Y' and d1.donor_no in ('61359','41834','90810')

Thank you again visakh16

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-15 : 12:49:47

np

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources