Link to home
Create AccountLog in
Avatar of Brian
BrianFlag for United States of America

asked on

Help with INNER JOIN

Hello Experts,

I need help retrieving some data. Please see my tables below with the table scheme and what I need.

I need to retrieve the following values psp_id and psp_name from the PricingSpecialPackages table based on price_id value from the Pricing table. Is this possible and if so how? I know I have a relation between the Pricing and PricingSpecialPackages tables with the phot_id value but not sure how to retrieve what I need by somehow using that photo_id using the price_id value.

Photography
  - phot_id  int
  - phot_name  varchar

Pricing
  - price_id  int
  - phot_id  varchar

PricingSpecialPackages
  - psp_id  int
  - phot_id  int
  - psp_name  varchar
Avatar of bigbigpig
bigbigpig

select Pricing.price_id, PricingSpecialPackages.psp_id, PricingSpecialPackages.psp_name from Pricing left join PricingSpecialPackages on Pricing.phot_id=PricingSpecialPackages.phot_id where Pricing.price_id='yourvalue'
You could sub in an INNER JOIN instead of LEFT JOIN, it'll return a record if there is a match in both tables.  The LEFT JOIN will still return a row of Pricing.price_id even if there are no matches in PricingSpecialPackages.
Avatar of Brian

ASKER

Hi bigbigpig,

Can you provide me the syntax that you think is best in code view?
Sure here's the left join, which will return a record even if there is no match in the PricingSpecialPackages table.  If you are only interested in matches in both tables the change the word LEFT to INNER:

select Pricing.price_id, PricingSpecialPackages.psp_id, PricingSpecialPackages.psp_name from Pricing left join PricingSpecialPackages on Pricing.phot_id=PricingSpecialPackages.phot_id where Pricing.price_id='yourvalue'

Open in new window


You'll obviously need to put in your own value in the "where" clause... Pricing.price_id='...'
Avatar of Brian

ASKER

that did not work. I had the same issue before and someone had to do the following for it to. See below what I had to do before that someone else helped me with.

ALTER PROCEDURE [dbo].[RetrievePricePackageLinks]

(
@price_id int
)

AS

SELECT p.price_id, p.price_pkgname
FROM dbo.Pricing AS p
WHERE phot_id in (
  select phot_id from dbo.Pricing where price_id = @price_id
)

Open in new window

Stick the query I gave you in Management Studio and let me know what error it reports.  I might have just misspelled something.
Avatar of Brian

ASKER

ok, i see the problem. On page1 I'm passing the value psp_id to page2 which i then need to retrieve the data I mentioned based on the psp_id value and not the price_id value. But i still need the same result. So I need the same results but instead of the price_id parameter I need the psp_id.
So like this?  Just changed the where clause so it finds on psp_id instead of price_id.  Although I don't see the point of the JOIN since you're not using any data from the Pricing table anymore.

select Pricing.price_id, PricingSpecialPackages.psp_id, PricingSpecialPackages.psp_name from Pricing left join PricingSpecialPackages on Pricing.phot_id=PricingSpecialPackages.phot_id where PricingSpecialPackages.psp_id='yourvalue'

Open in new window

Avatar of arnold
You have photo_id defined in different types int in the photo table and as a varchar in the pricing table which will complicate matters.
While you can compare, you would have to make sure to cast the pricing.phot_id as int in the join query.
Avatar of Brian

ASKER

@arnold,

photo_id has a field type of int. That was a typo, sorry. I updated the tables fields below to their correct values.

As I mentioned above I need to retrieve data from the PricingSpecialPackages table based on the price_id value from the Pricing table. Is this possible and if so how?

Photography
  - phot_id  int
  - phot_name  varchar

Pricing
  - price_id  int
  - phot_id  int

PricingSpecialPackages
  - psp_id  int
  - phot_id  int
  - psp_name  varchar
ASKER CERTIFIED SOLUTION
Avatar of bigbigpig
bigbigpig

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Brian

ASKER

@bigbigpig,

I wanted to let you know that what you just supplied in your last post was exactly what I needed. Thank you. However, I need the same type of thing with a few others. I'm going to create another post though called "Multiple Table Inner Join" if you could assist. Let me know when you get this so I can close this post and award you the points.
Sounds good - thanks.  I'll look for your other post and am happy to help!