SQL Query help

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

Select allOpen in new window

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)

Select allOpen 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.

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
;	

Select allOpen in new window

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.

please post exactly what you are executing (as a code block please) - seriously this is the fastest way to solution

Select allOpen 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) ;

Select allOpen in new window

Please go easy on me seriously...I am a network admin, I don't normally deal with sql queries!!

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);

Select allOpen in new window

Ok. thanks :) I ran it but I am still getting duplicate data in the item_id column. Mmmhhh..Any other ideas would be appreciated.

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.

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

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.

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

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

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

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

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

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.

>>1k duplicate items for each item_id
this is because of the cross join!

please believe me on this.

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

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

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

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.

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

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
;

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

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

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'

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'

Select allOpen in new window

Tried to run it but getting an error near where...

Ok. It ran successfully but returned no results.

yes, use the second one - the first one has errors

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 :)

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

congratulations

it does work better without cross joins :)