inner join with same primary key

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 2000 Forums

Transact-SQL (2000)

inner join with same primary key

Author

Topic

weerdboil
Starting Member

4 Posts

Posted - 2006-10-25 : 06:50:27

Hello.

Hopefully someone can help with this simple query.

SELECT p.product_id, p.special_offer_id,
bikes.bike_id, bikes.bike_model, bikes.bike_description, bikes.bike_online, bikes.bike_cc, bikes.bike_price_pounds, bikes.bike_price_pence, bikes.bike_description,
parts.part_id, parts.part_name, parts.part_description, parts.part_price_pounds, parts.part_price_pence, parts.part_online
FROM products p
INNER JOIN bikes bikes ON p.product_id = bikes.product_id
INNER JOIN parts parts ON p.product_id = parts.product_id

no data is returned when both joins are used, but I know the data should be returned as I can see it correctly in the db and also if I comment out one of the joins the single remaining join returns the correct data.

Thanks alot.
Dave

mr_mist
Grunnio

1870 Posts

Posted - 2006-10-25 : 06:56:30

your join logic suggests that both bike and part have the same product_id. Is that right? It seems a little odd to me.

What are you trying to get out of this query?

-------
Moo. :)

mr_mist
Grunnio

1870 Posts

Posted - 2006-10-25 : 06:57:13

Note to add - if you actually want a list of ALL the parts, but EITHER bike details or part details, then you need to redesign the query.

-------
Moo. :)

weerdboil
Starting Member

4 Posts

Posted - 2006-10-25 : 07:25:46

hello.

there is a products table that is linked to the bikes and parts tables. I store generic product information (including a cross-sell reference) in the products table and then specific info about the products themselves in their respective product_type tables (bikes/parts).

I have a page in which I would like to view all the Products of all Types.

Does this make sense?

Thanks,
Dave

mr_mist
Grunnio

1870 Posts

Posted - 2006-10-25 : 07:58:27

Not really. Post some examples from your tables.

-------
Moo. :)

weerdboil
Starting Member

4 Posts

Posted - 2006-10-25 : 08:12:29

Here is some excerpts from my schema

PRODUCTS
product_id PK
product_type_id
product_special_offer
product_cross_sell

BIKES
bike_id PK
product_id FK
bike_model
etc
etc

PARTS
part_id PK
product_id FK
part_name
etc
etc

BIKES and PARTS are unrelated.
I need a view with all BIKES and PARTS listed but the query I have posted doesn't seem to like the double reference to the product.product_id field.

Thanks again.
Dave

mr_mist
Grunnio

1870 Posts

Posted - 2006-10-25 : 08:19:06

The query itself is fine, but it will only return rows where the product ID exists (and is the same) in BIKES, PARTS and PRODUCTS. What I expect that you actually want to do is list everything in PRODUCTS, and either get the information from BIKES or PARTS. But I'm not sure because you haven't provided sample data and results.

-------
Moo. :)

weerdboil
Starting Member

4 Posts

Posted - 2006-10-25 : 08:51:05

Hi.

"What I expect that you actually want to do is list everything in PRODUCTS, and either get the information from BIKES or PARTS."
that is correct.

sample data
PRODUCTS
productID generic fields.......
12
13
15
18
19
20
35
36

PARTS
part_id productID part_type_id part_manu_id desc fields.....
1 19 3 11
2 20 4 11
3 35 4 11

BIKES
bike_id productID bike_type_id bike_manu_id desc fields.....
7 12 8 2
9 20 3 2

I can get the data using LEFT join but that also returns 2 completely blank rows?? And then I come across my next problem - which is to get the information from the BIKE_TYPE, BIKE_MANUFACTURER, PART_TYPE and PART_MANUFACTURER tables.

One step at a time perhaps :)

Thanks for you help. Very much appreciated I can assure you.

Dave

mr_mist
Grunnio

1870 Posts

Posted - 2006-10-25 : 09:23:09

You could probably do this using left join and a CASE statement, but you might find it simpler just to do two completely seperate SELECT statements and UNION the two result sets together.

-------
Moo. :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources