October 25, 2006 at 5:16 am
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
October 25, 2006 at 5:50 am
Hi,
Use the bellow query, If the data exists then surly it will return your required result.
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 bikes.product_id = parts.product_id
If the part table contains the optional information then used the left outer join rather then inner join...
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
LEFT OUTER JOIN parts parts ON bikes.product_id = parts.product_id
Hope this will slove your problem
cheers
October 25, 2006 at 6:06 am
Hello.
Thankyou for the suggestion.
However the parts table is totally unrelated to the bikes table so I cannot inner join from bikes.product_id to parts.product_id as there are no matching values.
Both tables are related to the products table.
So products.product_id = bikes.product_id
and products.product_id = parts.product_id
and I need data returned from the query for both these tables?
Thanks again.
Dave
October 25, 2006 at 6:10 am
Ah, so products contains a super set of all products say? and bikes are just those products that are a bike, and parts are just those products that are part?
Thus there are no product id's in bikes that are also in parts?
Assuming this it sounds like what you want are LEFT JOINs or a UNION perhaps?
October 25, 2006 at 6:17 am
"Thus there are no product id's in bikes that are also in parts?"
yes. that is the case.
I have tried left join now. Looks good! Will let you know how I get on.
Thanks!!!
October 25, 2006 at 6:55 am
Hi again.
I can get my data with left join but that also returns 2 blank rows??
Here is some sample data:
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 come across my next problem also - which is to get the information from the BIKE_TYPE, BIKE_MANUFACTURER, PART_TYPE and PART_MANUFACTURER tables in the same query.
Thanks very much your help is greatly appreciated.
Yours,
Dave
October 25, 2006 at 8:17 am
You know, I am not an expert, but I can foresee some major problems for you in the future.
You have three tables. The products table is using the same column to link to the other two tables. Now, supposing a Bike ever gets the same ID number as a Part. Your Products table will contain the value X, and it will be matched against a Bike X and a Part X. This is going to lead to some confusion somewhere.
Why not have a single table for "Products", with a field that is "Type". Set Type to 1 and you have a Bike, Set it to 2 and you have a Part.
This would solve some of the problems you are currently having, and while it would doubtless give you new problems (I keep being told they are opportunities for improvement), it should avoid that matching bike and part id in the future.
All Best
Conway
October 25, 2006 at 8:22 am
Depends on how precisely the data look like, but from what you posted this seems to be a good candidate for UNION. If some columns that you will be displaying are different for different tables (parts, bikes), then you can't use UNION.
There is one thing to be remembered - once you start LEFT JOINs, any other tables that you link further to these (leftjoined) tables must also use LEFT JOIN.
SELECT p.product_id, p.special_offer_id, ...., prod_details.type, prod_details.manufacturer_name
FROM products p
LEFT JOIN
(SELECT b.product_id, bt.type as type, bm.manu_name as manufacturer_name
FROM bikes b
LEFT JOIN bike_type bt ON bt.type_id = b.bike_type_id
LEFT JOIN bike_manufacturer bm ON bm.manu_id = b.bike_manu_id
UNION ALL
SELECT pt.product_id, ptt.type as type, ptm.manu_name as manufacturer_name
FROM parts pt
LEFT JOIN part_type ptt ON ptt.type_id = pt.part_type_id
LEFT JOIN part_manufacturer pm ON pm.manu_id = pt.part_manu_id) as prod_details
ON prod_details.product_id = p.product_id
Another possibility would be to join all tables in one query (not using derived table like above), and then in the SELECT clause COALESCE to populate the columns from each respective table:
SELECT p.product_id, p.special_offer_id, ...., COALESCE(bt.type,ptt.type, ''), COALESCE(bm.manu_name, pm.manu_name,'')
FROM products p
LEFT JOIN bikes b ON b.product_id = p.product_id
LEFT JOIN bike_type bt ON bt.type_id = b.bike_type_id
LEFT JOIN bike_manufacturer bm ON bm.manu_id = b.bike_manu_id
LEFT JOIN parts pt ON pt.product_id = p.product_id
LEFT JOIN part_type ptt ON ptt.type_id = pt.part_type_id
LEFT JOIN part_manufacturer pm ON pm.manu_id = pt.part_manu_id
But... with such complex queries, you have to test whether rows don't multiply in the result. I don't know anything about your data, so take this SQL just as a hint, not a ready solution.
Also, I wholeheartedly agree with Conway, that this calls for trouble and is too complicated. I suppose that the front end ensures that product_id is unique and can't be used for both a bike and a part... but anyway... product is product, and manufacturer is manufacturer. Why separate tables for bike manufacturers and part manufacturers? What if a company produces both bikes and parts?
October 26, 2006 at 6:56 am
You say the PARTS table has nothing to do with the BIKES tables. Yet in your sample data both have product_ID=20 which you link to PRODUCTS. I fail to see what's going on.
October 26, 2006 at 8:32 am
You might want to look at
http://qa.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=317151
where there is a very similar problem.
I have coined it the dual one-to-many link problem. It might better be called the dual zero-to-many link problem. It essentially links table t1 to table t2 and t1 to t3. The link betwen t1 and t2 can produce 0, one or many matches. The link between t1 and t3 also can produce 0, one ore many matches.
October 26, 2006 at 8:57 am
Taking your data (to which I have added an extra bike record - to make things interesting) you want a query that produces a result like this:
productid partdes bikedes
----------- ------------------------------ ------------------------------
12 NULL NULL
13 NULL NULL
15 NULL NULL
18 NULL NULL
19 part 19 des bike 19 des
20 part 20 des bike 20 des
35 part 35 des NULL
36 NULL bike 36 des
See query and data below. Here there is a 1 to zero or one link. Things get tricky when yuo have a 1 to zero or one or many link. Hope this helps.
Query:
select productid,max(partdes) partdes,max(bikedes) bikedes
from
(
select productid,null partdes,null bikedes
from products
union all
select productid,partdes,null
from parts
union all
select productid,null,bikedes
from bikes
) u
group by productid
order by productid
Data:
drop table products
create table products
(
productid int,
productdes varchar(30)
)
insert into products values(12,'product 12 des')
insert into products values(13,'product 13 des')
insert into products values(15,'product 15 des')
insert into products values(18,'product 18 des')
insert into products values(19,'product 19 des')
insert into products values(20,'product 20 des')
insert into products values(35,'product 35 des')
insert into products values(36,'product 36 des')
drop table parts
create table parts
(
productid int,
partdes varchar(30)
)
insert into parts values(19,'part 19 des')
insert into parts values(20,'part 20 des')
insert into parts values(35,'part 35 des')
drop table bikes
create table bikes
(
productid int,
bikedes varchar(30)
)
insert into bikes values(19,'bike 19 des')
insert into bikes values(20,'bike 20 des')
insert into bikes values(36,'bike 36 des')
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply