?
Solved

SQL Query help

Posted on 2013-05-18
36
Medium Priority
?
282 Views
Last Modified: 2013-05-20
Hello. I need assistance. I only know the very basics of SQL syntax so I am kind of at a loss on how to write out the query so I get the results I need.

Please see attachment.

Basically what I am trying to accomplish is to get the query to show results as they are in the screen shot but without duplications in the item_id. The results from the above query have multiple dups in the item_id column. My goal is to be able to see how many parts (item_id) in the last year have sold that also have a null value in the class_id4 column. I have tried adding a group by statement but it does not return the results like I had hoped. If someone could help by telling me exactly how the syntax should look, I would greatly appreciate it. I have been struggling with this one for a day now.
SQL-Query.docx
0
Comment
Question by:Llacy80
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 16
  • 15
  • 5
36 Comments
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39177896
what a pity your query isn't in text form - please don't put code into images - in fact the very quickest way to resolution is to put your existing query into a code block inside your question.

I see you are using 'select distinct', quite often peoples expectation of this syntax does not match the technical definition. 'Distinct' will evaluate the whole row (all columns!) and then decide is the row is different to all other rows.

As our code is embedded into an image it will take more time - back soon I hope.

Code blocks by the way look like this
look for CODE in the comment toolbar

Open in new window

0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39177901
ok, I "think" your issue is the date_last_modified

it's not possible to be precise as you don't display the 'duplications' as such... anyway  here goes, I think it may be something like this:
select
  item_id
, class_id4
, dateadd(dd, datediff(dd,0, date_last_modified), 0)
from <<whatever your tables/joins need to be>>
where class_id4 is null
and (date_last_modified >= dateadd(month, -12 , dateadd(dd, datediff(day,0, getDate()), 0) )
group by
  item_id
, class_id4
, dateadd(dd, datediff(dd,0, date_last_modified), 0)

Open in new window

But I notice you are using a CROSS JOIN - this looks to be very bad!(Sorry to be so blunt). PLEASE don't use CROSS JOIN, there simply has to be a better way.
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39177910
pleas have a look at this: http://sqlfiddle.com/#!3/b588c/1
there are 2 tables
each table has just 10 rows

if I CROSS JOIN those 2 tables, do I get 10 rows?
CREATE TABLE TableX
	([ColX] varchar(1))
;
	
INSERT INTO TableX
	([ColX])
VALUES
	('X'),	('X'),	('X'),	('X'),	('X'),	('X'),	('X'),	('X'),	('X'),	('X')
;

CREATE TABLE TableY
	([ColY] varchar(1))
;
	
INSERT INTO TableY
	([ColY])
VALUES
	('Y'),	('Y'),	('Y'),	('Y'),	('Y'),	('Y'),	('Y'),	('Y'),	('Y'),	('Y')
;

SELECT
  colx
, coly
FROM tablex
CROSS JOIN tabley
;	

Open in new window

0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 4

Author Comment

by:Llacy80
ID: 39177927
Thank you for your post. I do apologize for not posting the code...thought it only was about 2-3 lines so surely it was not that big of a deal.

However, the statement you posted above on your second comment does not work, it is giving me a group by error.

Incorrect syntax near the keyword Group.
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39177932
please post exactly what you are executing (as a code block please) - seriously this is the fastest way to solution
0
 
LVL 4

Author Comment

by:Llacy80
ID: 39177938

Open in new window

select
  item_id
, class_id4
, dateadd(dd, datediff(dd,0, date_last_modified), 0)
from invoice_hdr, inv_mast
where class_id4 is null
and (date_last_modified >= dateadd(month, -12 , dateadd(dd, datediff(day,0, getDate()), 0) )
GROUP BY  item_id, class_id4, dateadd(dd, datediff(dd,0, date_last_modified), 0) ;

Open in new window

0
 
LVL 4

Author Comment

by:Llacy80
ID: 39177939
Please go easy on me seriously...I am a network admin, I don't normally deal with sql queries!!
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39177960
one to many opening brackets ;)
SELECT item_id, class_id4, dateadd(dd, datediff(dd, 0, date_last_modified), 0)
FROM invoice_hdr, inv_mast
WHERE class_id4 IS NULL
	AND date_last_modified >= dateadd(month, - 12, dateadd(dd, datediff(day, 0, getDate()), 0))
GROUP BY item_id, class_id4, dateadd(dd, datediff(dd, 0, date_last_modified), 0);

Open in new window

0
 
LVL 4

Author Comment

by:Llacy80
ID: 39177967
Ok. thanks :) I ran it but I am still getting duplicate data in the item_id column. Mmmhhh..Any other ideas would be appreciated.
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39177971
please do not take this the wrong way, I did not create this domain name

This site is useful for formatting and some basic syntax checking of sql:
http://poorsql.com/

if we use the query you posted it looks like thisknowing how to solve it may be harder - but at least you know where the syntax error is detected.
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39177981
Now the bigger issue, and it really is a BIGGER issue!

SELECT ...
FROM invoice_hdr, inv_mast

when you list tables like this, it is exactly the same as this:

SELECT ...
FROM invoice_hdr
CROSS JOIN inv_mast

Now, let's assume there are 100,000 invoice_hdr records and 100,000 invoice_mast records

when a cross join is performed, what this does it to MULTIPLY the records,
so you will get 100,000 * 100,000 = 10,000,000,000 records!!

There will be a field in those tables that should be used for joining, it will be something like "invoiceID" or "invoiceNo"

Those 2 tables should be joined like this:

SELECT ...
FROM invoice_hdr
INNER JOIN inv_mast ON invoice_hdr.invoiceID = inv_mast.invoiceID

and with this type of join you now only have 100,000 records to scan.

please don't use CROSS JOIN
0
 
LVL 4

Author Comment

by:Llacy80
ID: 39177988
Thank you for the info...I should have said something before but I did not add the cross join in to the statement, it was automatically put there by the view I put together....

I guess I will just keep messing around with it to see if I can somehow get it to work. Thanks.
0
 

Expert Comment

by:SimonNFU
ID: 39180220
Hi Llacy80

Could clarify; you appear to have two tables in your query
dbo.invoice_hdr and dbo.inv_mast
but your where clause and select fields do not reference the dbo.invoice_hdr  if it is not needed remove it that will remove the cross join and also MAY fix your problem.

If not please copy and paste what is left of your query and I will run through it for you

Regards
Simon
0
 
LVL 4

Author Comment

by:Llacy80
ID: 39180846
Hi Simon,

Thanks for the response. I am still struggling with this and banging my head against the wall!  So any help is appreciated. Here is what I have.  

SELECT DISTINCT dbo.inv_mast.item_id, dbo.inv_mast.class_id4, dbo.invoice_hdr.order_date
FROM         dbo.invoice_hdr CROSS JOIN
                      dbo.inv_mast
WHERE     (dbo.inv_mast.class_id4 IS NULL) AND (dbo.invoice_hdr.order_date >= DATEADD(m, - 12, GETDATE()))
GROUP BY dbo.inv_mast.item_id, dbo.inv_mast.class_id4, dbo.invoice_hdr.order_date
0
 
LVL 4

Author Comment

by:Llacy80
ID: 39180858
Also, I have tried using the following syntax that the other poster gave me but it too is returning hundreds/thousands dups in the item_id field.

SELECT DISTINCT dbo.inv_mast.item_id, dbo.inv_mast.class_id4, dbo.invoice_hdr.order_date
FROM         dbo.invoice_hdr CROSS JOIN
                      dbo.inv_mast
WHERE     (dbo.inv_mast.class_id4 IS NULL) AND (dbo.invoice_hdr.order_date >= DATEADD(m, - 12, GETDATE()))
GROUP BY dbo.inv_mast.item_id, dbo.inv_mast.class_id4, dbo.invoice_hdr.order_date
0
 

Expert Comment

by:SimonNFU
ID: 39180862
HI;

Try this and let us know who you get on
SELECT DISTINCT
        dbo.inv_mast.item_id
       ,dbo.inv_mast.class_id4
       ,dbo.invoice_hdr.order_date
FROM    inv_mast
WHERE   (dbo.inv_mast.class_id4 IS NULL)
        AND (dbo.invoice_hdr.order_date >= DATEADD(m, -12, GETDATE()))
GROUP BY dbo.inv_mast.item_id
       ,dbo.inv_mast.class_id4
       ,dbo.invoice_hdr.order_date

Regards
Simon
0
 
LVL 4

Author Comment

by:Llacy80
ID: 39180916
Simon,

Hi. After I added invoice_hdr to the from statement, it ran but unforutnately it is still returning about 1k duplicate items for each item_id. Any other ideas? Thanks so much.

Lacy
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39180946
but: how will dbo.invoice_hdr.order_date be available if dbo.invoice_hdr is not joined?

@Llacy80, is there anyway you can get us a list of all fields in both tables?

You are having issues here because the 2 tables are not joined properly - please believe me on this.
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39180951
>>1k duplicate items for each item_id
this is because of the cross join!
please believe me on this.
0
 

Expert Comment

by:SimonNFU
ID: 39180962
Hi;

That's fine; which field is used in both tables.  I am guessing some sort of header id which is in both tables

Regards
Simon
0
 
LVL 4

Author Comment

by:Llacy80
ID: 39180971
I do believe you, :) However even when I don't do the cross join I get 1-2k dup items for each item_id. THis is just driving me insane..I just need to be able to show my boss which parts have sold in the last year that have null value in the class_id4 field...Something I thought would be so simple has turned in to big ole headache.

Well here goes.... The list of all fields in both tables...

 dbo.inv_mast.inv_mast_uid, dbo.inv_mast.item_id, dbo.inv_mast.item_desc, dbo.inv_mast.delete_flag, dbo.inv_mast.weight, dbo.inv_mast.net_weight,
                      dbo.inv_mast.date_created, dbo.inv_mast.date_last_modified, dbo.inv_mast.last_maintained_by, dbo.inv_mast.inactive, dbo.inv_mast.cube,
                      dbo.inv_mast.date_inactive, dbo.inv_mast.date_reactive, dbo.inv_mast.catch_weight_indicator, dbo.inv_mast.purchasing_weight, dbo.inv_mast.class_id1,
                      dbo.inv_mast.class_id2, dbo.inv_mast.class_id3, dbo.inv_mast.class_id4, dbo.inv_mast.class_id5, dbo.inv_mast.upc_or_ean, dbo.inv_mast.upc_or_ean_id,
                      dbo.inv_mast.serialized, dbo.inv_mast.product_type, dbo.inv_mast.hose, dbo.inv_mast.fitting, dbo.inv_mast.d_length, dbo.inv_mast.catalog_item,
                      dbo.inv_mast.short_code, dbo.inv_mast.track_lots, dbo.inv_mast.requisition, dbo.inv_mast.price1, dbo.inv_mast.price2, dbo.inv_mast.price3, dbo.inv_mast.price4,
                      dbo.inv_mast.price5, dbo.inv_mast.price6, dbo.inv_mast.price7, dbo.inv_mast.price8, dbo.inv_mast.price9, dbo.inv_mast.price10,
                      dbo.inv_mast.default_product_group, dbo.inv_mast.default_sales_discount_group, dbo.inv_mast.default_purchase_disc_group, dbo.inv_mast.sales_pricing_unit,
                      dbo.inv_mast.sales_pricing_unit_size, dbo.inv_mast.purchase_pricing_unit, dbo.inv_mast.purchase_pricing_unit_size, dbo.inv_mast.extended_desc,
                      dbo.inv_mast.commission_class_id, dbo.inv_mast.other_charge_item, dbo.inv_mast.invoice_type, dbo.inv_mast.pick_ticket_type, dbo.inv_mast.default_selling_unit,
                      dbo.inv_mast.update_via_pricing_service, dbo.inv_mast.default_purchasing_unit, dbo.inv_mast.hi, dbo.inv_mast.ti, dbo.inv_mast.convert_quantities,
                      dbo.inv_mast.haz_mat_flag, dbo.inv_mast.class_code, dbo.inv_mast.item_terms_discount_pct, dbo.inv_mast.tpcx_status, dbo.inv_mast.default_transfer_unit,
                      dbo.inv_mast.keywords, dbo.inv_mast.fulltext_timestamp, dbo.inv_mast.vendor_consigned, dbo.inv_mast.created_by, dbo.inv_mast.disposition,
                      dbo.inv_mast.source_type_cd, dbo.inv_mast.use_tags_flag, dbo.inv_mast.base_unit, dbo.inv_mast.tag_hold_class_uid, dbo.inv_mast.shippable_unit_flag,
                      dbo.inv_mast.auto_allocation_flag, dbo.inv_mast.parker_product_cd, dbo.inv_mast.parker_division_cd, dbo.inv_mast.iva_taxable_flag, dbo.inv_mast.restricted_flag,
                      dbo.inv_mast.currency_id, dbo.inv_mast.service_terms_discount_pct, dbo.inv_mast.service_commission_class_id, dbo.inv_mast.override_specific_costing,
                      dbo.inv_mast.sold_outside_us_flag, dbo.inv_mast.configurable_flag, dbo.inv_mast.last_pricing_service_date, dbo.inv_mast.commodity_code,
                      dbo.inv_mast.default_price_family_uid, dbo.inv_mast.spa_item_flag, dbo.inv_mast.avail_for_sch_delivery_flag, dbo.inv_mast.item_type_cd,
                      dbo.inv_mast.cust_parent_inv_mast_uid, dbo.inv_mast.unspsc_code, dbo.inv_mast.dci_code, dbo.inv_mast.epa_cert_req_flag, dbo.inv_mast.aia_enabled_flag,
                      dbo.inv_mast.aia_remnant_qty, dbo.inv_mast.manufacturer_program_type_uid, dbo.inv_mast.manufacturer_program_type_pct, dbo.inv_mast.length,
                      dbo.inv_mast.width, dbo.inv_mast.height, dbo.inv_mast.use_oc_tax_rules_flag, dbo.inv_mast.tally_flag, dbo.inv_mast.oc_print_on_pick_ticket_flag,
                      dbo.inv_mast.oc_print_on_invoice_flag, dbo.inv_mast.use_revisions_flag, dbo.inv_mast.single_use_or_reusable, dbo.inv_mast.unitconv_override_oe_flag,
                      dbo.inv_mast.unitconv_override_purc_flag, dbo.inv_mast.item_sales_tax_class, dbo.inv_mast.lifo_pool_item_class, dbo.inv_mast.country_of_origin_req_flag,
                      dbo.inv_mast.nmfc_hdr_uid, dbo.inv_mast.catch_lot_weight_flag, dbo.inv_mast.ucc128_pack_type_cd, dbo.inv_mast.ucc128_standard_pack_size,
                      dbo.inv_mast.redemption_item_flag, dbo.inv_mast.redemption_value, dbo.inv_mast.bo_fill_canadian_purchase_flag, dbo.inv_mast.country_of_origin_code,
                      dbo.inv_mast.apply_state_fuel_surcharge_flag, dbo.inv_mast.type_of_sale, dbo.invoice_hdr.invoice_no, dbo.invoice_hdr.order_no, dbo.invoice_hdr.order_date,
                      dbo.invoice_hdr.invoice_date, dbo.invoice_hdr.customer_id, dbo.invoice_hdr.bill2_name, dbo.invoice_hdr.bill2_contact, dbo.invoice_hdr.bill2_address1,
                      dbo.invoice_hdr.bill2_address2, dbo.invoice_hdr.bill2_city, dbo.invoice_hdr.bill2_state, dbo.invoice_hdr.bill2_postal_code, dbo.invoice_hdr.ship2_name,
                      dbo.invoice_hdr.ship2_contact, dbo.invoice_hdr.ship2_address1, dbo.invoice_hdr.ship2_address2, dbo.invoice_hdr.ship2_city, dbo.invoice_hdr.ship2_state,
                      dbo.invoice_hdr.ship2_postal_code, dbo.invoice_hdr.carrier_name, dbo.invoice_hdr.fob, dbo.invoice_hdr.terms_desc, dbo.invoice_hdr.po_no,
                      dbo.invoice_hdr.salesrep_id, dbo.invoice_hdr.salesrep_name, dbo.invoice_hdr.brokerage, dbo.invoice_hdr.freight, dbo.invoice_hdr.ar_account_no,
                      dbo.invoice_hdr.gl_freight_account_no, dbo.invoice_hdr.gl_brokerage_account_no, dbo.invoice_hdr.brokerage_amt, dbo.invoice_hdr.period,
                      dbo.invoice_hdr.year_for_period, dbo.invoice_hdr.store_no, dbo.invoice_hdr.invoice_type AS Expr1, dbo.invoice_hdr.ship_to_id, dbo.invoice_hdr.ship_date,
                      dbo.invoice_hdr.total_amount, dbo.invoice_hdr.amount_paid, dbo.invoice_hdr.terms_taken, dbo.invoice_hdr.allowed, dbo.invoice_hdr.paid_in_full_flag,
                      dbo.invoice_hdr.paid_by_check_no, dbo.invoice_hdr.date_paid, dbo.invoice_hdr.print_flag, dbo.invoice_hdr.print_date, dbo.invoice_hdr.company_no,
                      dbo.invoice_hdr.customer_id_number, dbo.invoice_hdr.date_created AS Expr2, dbo.invoice_hdr.date_last_modified AS Expr3,
                      dbo.invoice_hdr.last_maintained_by AS Expr4, dbo.invoice_hdr.printed, dbo.invoice_hdr.printed_date, dbo.invoice_hdr.corp_address_id,
                      dbo.invoice_hdr.shipping_cost, dbo.invoice_hdr.bill2_country, dbo.invoice_hdr.ship2_country, dbo.invoice_hdr.invoice_reference_no,
                      dbo.invoice_hdr.invoice_adjustment_type, dbo.invoice_hdr.invoice_desc, dbo.invoice_hdr.memo_amount, dbo.invoice_hdr.bad_debt_amount,
                      dbo.invoice_hdr.invoice_class, dbo.invoice_hdr.period_fully_paid, dbo.invoice_hdr.year_fully_paid, dbo.invoice_hdr.approved, dbo.invoice_hdr.fc_thru_date,
                      dbo.invoice_hdr.cumulative_fc, dbo.invoice_hdr.net_due_date, dbo.invoice_hdr.terms_due_date, dbo.invoice_hdr.terms_id, dbo.invoice_hdr.branch_id,
                      dbo.invoice_hdr.disputed_flag, dbo.invoice_hdr.statement_period, dbo.invoice_hdr.statement_year, dbo.invoice_hdr.other_charge_amount,
                      dbo.invoice_hdr.tax_amount, dbo.invoice_hdr.original_document_type, dbo.invoice_hdr.consolidated, dbo.invoice_hdr.sold_to_ah_uid,
                      dbo.invoice_hdr.sold_to_customer_id, dbo.invoice_hdr.ship_to_phone, dbo.invoice_hdr.invoice_batch_uid, dbo.invoice_hdr.freight_code_uid,
                      dbo.invoice_hdr.shipping_route_uid, dbo.invoice_hdr.transmission_method, dbo.invoice_hdr.terms_amount, dbo.invoice_hdr.sales_location_id,
                      dbo.invoice_hdr.source_type_cd AS Expr5, dbo.invoice_hdr.ship2_email_address, dbo.invoice_hdr.currency_line_uid, dbo.invoice_hdr.created_by AS Expr6,
                      dbo.invoice_hdr.tax_terms_amt, dbo.invoice_hdr.tax_terms_taken, dbo.invoice_hdr.carrier_insurance_amt, dbo.invoice_hdr.inv_no_display,
                      dbo.invoice_hdr.iva_exemption_number, dbo.invoice_hdr.iva_taxable_flag AS Expr7, dbo.invoice_hdr.tax_amount_paid, dbo.invoice_hdr.job_id,
                      dbo.invoice_hdr.invoice_paid_period, dbo.invoice_hdr.invoice_period, dbo.invoice_hdr.credit_memo_for_terms_flag, dbo.invoice_hdr.record_type_cd,
                      dbo.invoice_hdr.commission_cost, dbo.invoice_hdr.receiver_name, dbo.invoice_hdr.carton_count, dbo.invoice_hdr.strategic_freight_in,
                      dbo.invoice_hdr.strategic_freight_out, dbo.invoice_hdr.affiliated_training_center, dbo.invoice_hdr.external_reference_no, dbo.invoice_hdr.total_freightcharge_weight,
                      dbo.invoice_hdr.cardlock_cons_invoice_flag, dbo.invoice_hdr.remove_from_open_def_rev_window, dbo.invoice_hdr.record_type_reference_no,
                      dbo.invoice_hdr.rebill_invoice_reason_uid, dbo.invoice_hdr.edi_order_printed_flag, dbo.invoice_hdr.downpayment_applied,
                      dbo.invoice_hdr.reverse_redemption_flag
0
 

Expert Comment

by:SimonNFU
ID: 39181019
Hi;

I cannot see a direct relationship between the two tables (unless I am missing something) and this is what is causing your cross join

Do you have a database diagram / schema you can look at to see how the two tables may be related and if perhaps another table or two need to be added to the mix.

You are correct it should be quite simple but I do believe you have a missing piece to you puzzle, possibly invoice_det (detail)

Regards
Simon
0
 
LVL 4

Author Comment

by:Llacy80
ID: 39181036
You are correct, there are no similiar fields between the two tables. I guess I could create a new view and then try adding another table that might link them all up.
0
 

Expert Comment

by:SimonNFU
ID: 39181046
Hi;

Yes; I would have expected a relationship to exist probably a one to many and it is that link the will help you end you duplication issue

Regards
Simon
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39181058
select a,b
from tableX, tableY

IS a "cross join", it is precisely the same as writing

select a,b
from tableX
cross join tableY

please try this:

select count(*) from dbo.inv_mast
;

select count(*) from dbo.invoice_hdr
;

select count(*)
from dbo.inv_mast as M
inner join dbo.invoice_hdr as H on M.inv_mast_uid = H.invoice_no
;
0
 
LVL 4

Author Comment

by:Llacy80
ID: 39181069
Ok. I ran what you told me to and it returned the following

1 no column name 17361

1 no column name 51205

1 no column name 0
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39181078
>>I guess I could create a new view and then try adding another table that might link them all up.
goodness! NO, please don't

100,000 * 100,000 * something else = much worse
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39181113
well at least it's only 888,970,005 :(

ok, so that didn't work. try this, it should narrow down the possibilities

select
  table_name      
, column_name      
, ordinal_position      
, column_default      
, is_nullable      
, data_type      
, character_maximum_length
from information_schema.columns as m
inner join (
            select
              data_type
            , character_maximum_length
            from information_schema.columns
            where table_name = 'invoice_hdr'
            and column_name = 'invoice_no'
           ) as h where m.data_type = h.data_type and m.character_maximum_length = h.character_maximum_length
where m.table_name = 'inv_mast'
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39181130
sorry - too much haste, this one:
select
  m.table_name      
, m.column_name      
, m.ordinal_position      
, m.column_default      
, m.is_nullable      
, m.data_type      
, m.character_maximum_length
from information_schema.columns as m
inner join (
            select
              data_type
            , character_maximum_length
            from information_schema.columns
            where table_name = 'invoice_hdr'
            and column_name = 'invoice_no'
           ) as h on m.data_type = h.data_type and m.character_maximum_length = h.character_maximum_length
where m.table_name = 'inv_mast'

Open in new window

0
 
LVL 4

Author Comment

by:Llacy80
ID: 39181139
Tried to run it but getting an error near where...
0
 
LVL 4

Author Comment

by:Llacy80
ID: 39181151
Ok. It ran successfully but returned no results.
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39181163
yes, use the second one - the first one has errors
0
 
LVL 49

Accepted Solution

by:
PortletPaul earned 1500 total points
ID: 39181196
mmmmmmmmmmmmmmmmmm, drat
is there anyone on-site who knows this database well?
there simply has to be a way to get these tables to join
it might need another table
but

do not use a 'cross join' and remember that:

from dbo.inv_mast, dbo.invoice_hdr -- by itself this IS a cross join

afraid I cannot see anything in those field lists that's tells use what the join should be.
0
 
LVL 4

Author Comment

by:Llacy80
ID: 39181223
Ok well thank you for your help. I do appreciate it. I think I just need to get more familiar with these tables and the database and then hopefully from there I can come up with a query to produce the results I am looking for. I will also stray from cross joins :)
0
 
LVL 4

Author Comment

by:Llacy80
ID: 39181629
FYI. Got it to work with the following statement.


SELECT     IM.item_id, IM.class_id4, MAX(IH.invoice_date) AS last_date_pruchased
FROM         dbo.inv_mast AS IM INNER JOIN
                      dbo.invoice_line AS IL ON IM.inv_mast_uid = IL.inv_mast_uid INNER JOIN
                      dbo.invoice_hdr AS IH ON IH.invoice_no = IL.invoice_no
WHERE     (IH.invoice_date >= DATEADD(year, - 1, GETDATE())) AND (IM.class_id4 IS NULL)
GROUP BY IM.item_id, IM.class_id4
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39182875
congratulations

it does work better without cross joins :)
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

User Beware!  This is a rather permanent solution to removing your email from an exchange server.  The only way to truly go back is to have your exchange administrator restore your mailbox from backups.  This is usually the option of last resort.  A…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Viewers will learn the different options available in the Backstage view in Excel 2013.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question