How do I get all the records from table b to display

[sue0912]

Hi,

I am having a problem with a query. I am not sure how to do this...

I have Table A and Table B. I need to match up itemnumbers from Table A to Table B with I have done with a join. Those records display fine, but I also want the item numbers from table B to display also...the ones that don't match in Table A

here is what I have so far....

SELECT webpricing09.itemnumber, webpricing2.itemnumber...etc....
FROM webpricing09 Left Join Webpricing2 ON webpricing09.sku = webpricing2.itemnumber
WHERE webpricing2.itemnumber IS NOT NULL

any suggestions would be very helpful
thanks
Sue

 

[RyanEK]

Hi, you need to use a Left Outer Join instead.

Ryan

 

[bborissov]

You need FULL JOIN for this:

SELECT webpricing09.itemnumber, webpricing2.itemnumber...etc....
FROM webpricing09
FULL JOIN Webpricing2
     ON webpricing09.sku = webpricing2.itemnumber

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[sue0912]

Thanks Guys,

I tried the Full Join and I ended up getting results from webpricing2 twice....

 

[sue0912]

ok. I tried it with the full outer join and I did not get all my records.


SELECT webpricing09.*, webpricing2.*
FROM webpricing09 FULL OUTER JOIN
webpricing2 ON webpricing09.SKU = webpricing2.itemnumber
WHERE (webpricing2.itemnumber IS NOT NULL)
ORDER BY webpricing09.SKU

Sue

 

[r937]

if you write an outer join to webpricing2 using the itemnumber column as the join column, and then add this --

WHERE webpricing2.itemnumber IS NOT NULL

you are effectively hamstringing the "outerness" of the outer join -- the unmatched rows will be filtered out, and results will be the same as those produced by the inner join

r937.com | rudy.ca

 

[sue0912]

You were right! Thank you.

One more question can you do multiple Full Outer Joins like you can with a left outer join?

Sue

 

[sue0912]

I guess I answered my own question, because I tried it. Just to see and try and learn.

It seemed to work.

Thanks for you help.

Sue

 

[sue0912]

ok...1 more question...and I have a basic understanding of joins but I have no idea how to accommplish this..

Here is my query...
SELECT webpricing09.*, webpricing2.*, Powerscreen08.itemNum AS PSItemNum, Powerscreen08.itemdescription AS PSDescription,
Powerscreen08.pricelist AS PSPriceList, Powerscreen08.discount30 AS PSDiscount, Terex2008.ItemNumber AS TerexItemNum,
Terex2008.ItemDescription AS TerexDescription, Terex2008.PriceList08 AS TerexPrice, Terex2008.Discount30 AS Terexdiscount
FROM webpricing09 FULL OUTER JOIN
webpricing2 ON webpricing09.SKU = webpricing2.itemnumber FULL OUTER JOIN
Powerscreen08 ON webpricing09.SKU = Powerscreen08.itemNum FULL OUTER JOIN
Terex2008 ON webpricing09.SKU = Terex2008.ItemNumber
ORDER BY webpricing09.SKU

now by boss wants me to display the data so I join PSItemNum, PSItemDescription, PSPriceList and PSdiscount along with TerexItemNumber, Terexdescription, TerexPriceList, and TerexDiscount
on webpricing2.itemnumber....

how do I do that...I tried doing an AND webpricing2.itemnumber = powerscreen08.itemnum off of the 2nd Full Outer Join.

Help please!

Thanks
Sue

 

[bborissov]

It would be easier for us to post some example data from all tables and what you want as a result.Do not forget to round the data with [ tt ] and [ /tt ] tags (w/o spaces)

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[r937]

first of all, you probably do not want to use FULL OUTER JOINs

think about it, you're joining tables like this:

webpricing09==webpricing2==Powerscreen08==Terex2008

the FULL OUTER JOINs will ensure that you get all rows of webpricing09, regardless of whether they match any rows in webpricing2, and all rows of webpricing2, regardless of whether they match any rows in Powerscreen08, and all rows of Powerscreen08, regardless of whether they match any rows in Terex2008, and so on

are you sure you want that?????

r937.com | rudy.ca

 

[sue0912]

No. My boss wants ALL whether they match or not.

 

[sue0912]

ok...here is an example

in my current query it outputs the following data....

Header Field
Div Product Code itemnumber1 itemnumber Item Description Vendor vendorcosteach PSItemNum PSDescription PSPriceList PSDiscount TerexItemNum TerexDescription TerexPrice Terexdiscount

Data
PS Manuf 12270276 12270276 SIDE CONVEYOR DISC ROLLER GUARD SLET $52.00
PS Manuf 12270278 12270278 TELESCOPIC SIDE CONV. BEATER ROLLER GUA SLET $38.00
PS Manuf 12270279 12270279 TELESCOPIC SIDE CONVEYOR BEATER ROLLER SLET $38.00

The fields PSitemnum through Terexdiscount are null....

I want that data to be displayed also

does that help

 

[sue0912]

ok...I don't know maybe I did not do this the right way or not....considering I was asked to do a query a bit out of my knowledge I think I did the right thing....

I ended up creating a temp table from my query then just did an update on the fields from the Powerscreen08 and terex2008 table.


Thanks for the help guys. I appreciate it!

Sue

 

[bborissov]

I wanted an example from your base tables:
webpricing09
webpricing2
Powerscreen08
Terex2008

and example of what you want as a final result.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[ashab02]

you ould use a union query where by you take your firt query then followed by Union all and then right a second query with the left outer join where the param is null
Does this make any sense?