This one has me scratching my head a bit. I would appreciate any answers you may have. So! Here is my quandry.
I need assistance with creating or fixing this query that I have.
I have 4 Tables, and I need to combine information from all them.
SELECT DISTINCT
Mowers.*, Merchandise.SalePrice
FROM Mowers INNER JOIN
Merchandise ON Mowers.Title = Merchandise.Title
So This kind of does what I want it to but it only gets all Mower items that have a sale price. What I need it to do is pull all the Items from Mowers and if there is a sale price, to add that also. Like APPEND or something.
To complicate it more. I need to add a RidingMower table to this also. Merchandise table holds all the items that are on sale. So what I want to be able to do here, is pull all the Mowers items, all the RidingMower items, and if they have a sale price, add that to the return.
Table Names are:
Mowers
RidingMowers
Merchandise
if possible, I also need to include the Manufacturer table. I was successful in a different page with a simpler query adding the Manufacturer name to the Item.
SELECT Mowers.Title, Mowers.ThumbImage, Mowers.Price, Manufacturer.Manufacturer
FROM Manufacturer
INNER JOIN Mowers
ON Manufacturer.id = Mowers.idMfg
So This kind of does what I want it to but it only gets all Mower items that have a sale price. What I need it to do is pull all the Items from Mowers and if there is a sale price, to add that also. Like APPEND or something.
humm... you just need left join there
like this
SELECT DISTINCT
Mowers.*, Merchandise.SalePrice
FROM Mowers
LEFT JOIN RidingMowers
on Mowers.<MappingField> = RidingMowers.<MappingField>
LEFT JOIN Merchandise
ON Mowers.Title = Merchandise.Title
replace <MappingField> with name of column which is common in both tables
Left join gives you all records from table mentioned on left side of "LEFT JOIN" keyword with matching records from right side table
and if there is no matching record on right side then columns of right side table are displayed as null
hope this helps...
Cheers!
KK
Please mark as Answer if post helps in resolving your issue
My Site
Oh man, you're the first one to ever answer any of my posts on here THANK YOU kedarrkukarni! I will try this and see what results I get.
But would I not have to include RidingMowers.* fromt he SELECT statement?
SELECT DISTINCT
Mowers.*,RidingMowers.*, Merchandise.SalePrice
FROM Mowers
LEFT JOIN RidingMowers
on Mowers.<MappingField> = RidingMowers.<MappingField>
LEFT JOIN Merchandise
ON Mowers.Title = Merchandise.Title
SQL doesn't like the <mappingfield> context. It says incorrect syntax near on Mowers.<
hummm... I posted <MappingField> to indicate that you have to replace this with actual column name which is common between two tables Mowers and RidingMowers
it could be some id field or Title field as you used in another join...
I had explained it in my first post
kedarrkulkarni
replace <MappingField> with name of column which is common in both tables
Anyways, try this
SELECT DISTINCT
Mowers.*, Merchandise.SalePrice
FROM Mowers
LEFT JOIN RidingMowers
on Mowers.Title = RidingMowers.Title
LEFT JOIN Merchandise
ON Mowers.Title = Merchandise.Title
hope this helps...
Cheers!
KK
Please mark as Answer if post helps in resolving your issue
My Site
Marked as answer by Chen Yu - MSFT on Dec 17, 2012 01:58 AM
bangtheory
Member
52 Points
58 Posts
How Do I Get All Data From 4 Tables
Dec 05, 2012 05:16 PM|LINK
Hello
This one has me scratching my head a bit. I would appreciate any answers you may have. So! Here is my quandry.
I need assistance with creating or fixing this query that I have.
I have 4 Tables, and I need to combine information from all them.
SELECT DISTINCT Mowers.*, Merchandise.SalePrice FROM Mowers INNER JOIN Merchandise ON Mowers.Title = Merchandise.TitleSo This kind of does what I want it to but it only gets all Mower items that have a sale price. What I need it to do is pull all the Items from Mowers and if there is a sale price, to add that also. Like APPEND or something.
To complicate it more. I need to add a RidingMower table to this also. Merchandise table holds all the items that are on sale. So what I want to be able to do here, is pull all the Mowers items, all the RidingMower items, and if they have a sale price, add that to the return.
Table Names are:
Mowers
RidingMowers
Merchandise
if possible, I also need to include the Manufacturer table. I was successful in a different page with a simpler query adding the Manufacturer name to the Item.
SELECT Mowers.Title, Mowers.ThumbImage, Mowers.Price, Manufacturer.Manufacturer FROM Manufacturer INNER JOIN Mowers ON Manufacturer.id = Mowers.idMfgI could REALLY use some help on this.
kedarrkulkar...
All-Star
34421 Points
5534 Posts
Re: How Do I Get All Data From 4 Tables
Dec 05, 2012 05:23 PM|LINK
humm... you just need left join there
like this
replace <MappingField> with name of column which is common in both tables
Left join gives you all records from table mentioned on left side of "LEFT JOIN" keyword with matching records from right side table
and if there is no matching record on right side then columns of right side table are displayed as null
hope this helps...
KK
Please mark as Answer if post helps in resolving your issue
My Site
bangtheory
Member
52 Points
58 Posts
Re: How Do I Get All Data From 4 Tables
Dec 05, 2012 05:58 PM|LINK
Oh man, you're the first one to ever answer any of my posts on here THANK YOU kedarrkukarni! I will try this and see what results I get.
But would I not have to include RidingMowers.* fromt he SELECT statement?
Like this?
bangtheory
Member
52 Points
58 Posts
Re: How Do I Get All Data From 4 Tables
Dec 05, 2012 08:34 PM|LINK
SQL doesn't like the <mappingfield> context. It says incorrect syntax near on Mowers.<
pratiksolank...
Member
271 Points
76 Posts
Re: How Do I Get All Data From 4 Tables
Dec 05, 2012 10:50 PM|LINK
You have given suffix "<>" sign around your field which SQL does not understand. Remove "<" and ">" sign from your field.
kedarrkulkar...
All-Star
34421 Points
5534 Posts
Re: How Do I Get All Data From 4 Tables
Dec 06, 2012 02:39 AM|LINK
hummm... I posted <MappingField> to indicate that you have to replace this with actual column name which is common between two tables Mowers and RidingMowers
it could be some id field or Title field as you used in another join...
I had explained it in my first post
Anyways, try this
hope this helps...
KK
Please mark as Answer if post helps in resolving your issue
My Site