Home » SQL & PL/SQL » SQL & PL/SQL » Query help (Oracle 11g)
Query help [message #280384] Tue, 13 November 2007 06:39 Go to next message
MickD
Messages: 19
Registered: October 2006
Junior Member
Hi,

I'm having a few problems getting the information i need,

I have 3 tables
Shop_ord
Sales_part
Sales_part_cross_reference

i need to be able to get all information from the shop_ord table relating to a particular order number, but also get information from both the sales_part and sales_part_cross_reference tables if information exists in these two tables, below is the code I've written so far, but I'm having no joy..

select 
so.part_no,
spcr.customer_no,
so.eng_chg_level,
so.order_no,
sp.weight_net Weight_net,
sp.proposed_parcel_qty Qty,
sp.weight_net * sp.proposed_parcel_qty Net_Weight,
ifsapp.customer_info_api.Get_Name(spcr.customer_no) Name,
spcr.customer_part_no,
spcr.catalog_desc



from


shop_ord so left outer join sales_part sp   on
so.part_no=sp.part_no 
left outer join 
sales_part_cross_reference spcr on
so.part_no=spcr.catalog_no

where

so.order_no = '31800' and
spcr.customer_no = '10027'


as you can see in this example I'm querying for order number 31800 and customer number 10027, but in this case there is no record in spcr table for the part\customer combination, When i run the query at the moment I get no records at all, but what i would like is to get all the info from the shop_ord table, but if there is no info in the other two tables, just leave it blank.

I hope I've made myself clear what i need to achieve, if not please let me know.

Regards,

Mick.
Re: Query help [message #280386 is a reply to message #280384] Tue, 13 November 2007 06:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use outer join.

By the way, '31800' is a string and not a number.
Using this you prevent from index use.
Always compare 2 objects of the same type.

Regards
Michel


Re: Query help [message #280387 is a reply to message #280384] Tue, 13 November 2007 06:48 Go to previous messageGo to next message
MickD
Messages: 19
Registered: October 2006
Junior Member
Hi Michel,

Thanks for you quick response.

I thought i had used outer joins in the from statement? Do i need to do it a different way?

Mick.
Re: Query help [message #280389 is a reply to message #280384] Tue, 13 November 2007 06:54 Go to previous messageGo to next message
MickD
Messages: 19
Registered: October 2006
Junior Member
I've attached a document with all the colum names so you can see what there is in each table.

Mick.
  • Attachment: tables.xls
    (Size: 22.00KB, Downloaded 175 times)
Re: Query help [message #280393 is a reply to message #280389] Tue, 13 November 2007 07:00 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Mick,
many of us (me included) cannot or will not download XLS files (or word documents, zip files, ...) due to the possible harm they can do. Can you post:
- simplified create table statements (leave out irrelevant columns)
- insert statements with sample data
- expected/desired output based on that sample data.

MHE
Re: Query help [message #280399 is a reply to message #280387] Tue, 13 November 2007 07:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

thought i had used outer joins in the from statement?

Sorry, didn't see it, I'm used to read ANSI syntax.

Waiting for what Maarten asked.

Regards
Michel
Re: Query help [message #280404 is a reply to message #280399] Tue, 13 November 2007 07:27 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
What you have now:
SELECT .. .
FROM   shop_ord so
LEFT   OUTER JOIN sales_part sp ON so.part_no = sp.part_no
LEFT   OUTER JOIN sales_part_cross_reference spcr ON so.part_no =
                                                     spcr.catalog_no
WHERE  so.order_no = '31800'
AND    spcr.customer_no = '10027'


Comes down to: outer join spcr BUT spcr cust no should have a certain value. Thus eliminating the whole effect of the outerjoin (because any null value for records from spcr can per definition not contain customer_no 10027, right?)

So, if I recall this ANSI syntax correctly, this should work:
SELECT .. .
FROM   shop_ord so
LEFT   OUTER JOIN sales_part sp ON so.part_no = sp.part_no
LEFT   OUTER JOIN sales_part_cross_reference spcr ON so.part_no =
                                                     spcr.catalog_no
                                              AND    spcr.customer_no =
                                                     '10027'
WHERE  so.order_no = '31800'
Re: Query help [message #280407 is a reply to message #280393] Tue, 13 November 2007 07:45 Go to previous messageGo to next message
MickD
Messages: 19
Registered: October 2006
Junior Member
Maaher wrote on Tue, 13 November 2007 13:00

Mick,
many of us (me included) cannot or will not download XLS files (or word documents, zip files, ...) due to the possible harm they can do. Can you post:
- simplified create table statements (leave out irrelevant columns)
- insert statements with sample data
- expected/desired output based on that sample data.

MHE



Sorry for the delay,
OK, I'll try to do what you need, it may take some time though ( sql is not my strong point unfortunately).
Re: Query help [message #280409 is a reply to message #280404] Tue, 13 November 2007 07:51 Go to previous messageGo to next message
MickD
Messages: 19
Registered: October 2006
Junior Member
Hi Skooman,

Thanks for your reply,

I should have added this before, but what i am writing this query for is actually a report/label I'll be using bartender to actually format the report and parameters to restrict the results, ie the order_co and customer_no, so in the actual query i should need to bring back all results...

I hope this makes sense?

SELECT .. .
FROM   shop_ord so
LEFT   OUTER JOIN sales_part sp ON so.part_no = sp.part_no
LEFT   OUTER JOIN sales_part_cross_reference spcr ON so.part_no =
                                                     spcr.catalog_no
                                              [COLOR=red]AND    spcr.customer_no =
                                                     '10027'[/COLOR]
WHERE  so.order_no = '31800'


So how will i write the query in theis case?

Thanks,
Mick.
Re: Query help [message #280437 is a reply to message #280409] Tue, 13 November 2007 10:10 Go to previous message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
The "..." in the code is just short for all those columns you selected earlier. So, alter the join clause in your first statement according to my example and let us know what the result is!

If you provide us with create table and insert scripts, we'll be able to test the script (couldn't do that now, that's why I wasn't a 100% sure on the syntax)
Previous Topic: need to show lot no. along with supplier name
Next Topic: problem with Function
Goto Forum:
  


Current Time: Sun Dec 11 02:29:08 CST 2016

Total time taken to generate the page: 0.09097 seconds