Solved

SQL Query help

Posted on 2013-05-18
36
272 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
  • 16
  • 15
  • 5
36 Comments
 
LVL 48

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 48

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 48

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
 
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 48

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 48

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 48

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 48

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 48

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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 48

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 48

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 48

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 48

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 48

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 48

Expert Comment

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

Accepted Solution

by:
PortletPaul earned 500 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 48

Expert Comment

by:PortletPaul
ID: 39182875
congratulations

it does work better without cross joins :)
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
Many companies are making the switch from Microsoft to Google Apps (https://www.google.com/work/apps/business/). Use this article to learn more about what Google Apps has to offer and to help if you’re planning on migrating to Google Apps. It is …
Viewers will learn the different options available in the Backstage view in Excel 2013.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…

746 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now