We help IT Professionals succeed at work.

SQl query

Medium Priority
256 Views
Last Modified: 2013-12-07
I have a problem with my below query

select * from ModuleMasterList where Moduledescription like '%desc%'  --> results 82 rows

select a.Moduledescription,a.moduleid,b.moduleversion
FROM db_SM_Category.dbo.modulemasterlist a INNER JOIN

      db_HB_SM_LD.dbo.tblArticleModuleVersion b ON a.moduleid = b.moduleno

WHERE a.Moduledescription LIKE '%desc%u%'  --------------> results 1000 rows


What i need is ...


there results of the first query along with b.moduleversion from the table db_HB_SM_LD.dbo.tblArticleModuleVersion



So

select a.Moduledescription,a.moduleid,b.moduleversion
FROM db_SM_Category.dbo.modulemasterlist a INNER JOIN

      db_HB_SM_LD.dbo.tblArticleModuleVersion b ON a.moduleid = b.moduleno

WHERE a.Moduledescription LIKE '%desc%u%'

Should only return 82 rows !

Comment
Watch Question

CERTIFIED EXPERT

Commented:
Sounds like your data contains multiple entries in table B to match each single entry in table A.

How to hande this depends on what you need to do with the data.  You might get away with using SELECT DISTINCT instead of just SELECT - but it depends on your data and what you want to do with it.

Author

Commented:
i did distinct but its giving 62 rows not 82 :-(

Author

Commented:
This returns 62
select distinct a.moduleid,a.Moduledescription,b.moduleversion 
FROM db_SM_Category.dbo.modulemasterlist a INNER JOIN
      db_HB_SM_LD.dbo.tblArticleModuleVersion b ON a.moduleid = b.moduleno
and a.Moduledescription LIKE '%desc%u%'

Open in new window

Author

Commented:
What i need is..

The module version for each moduleno with "%desc%"

Author

Commented:
i tried this also , its giving 1000 rows
select b.moduleversion,a.moduleid,a.Moduledescription,b.moduleversion 
FROM db_SM_Category.dbo.modulemasterlist a, db_HB_SM_LD.dbo.tblArticleModuleVersion b 
      where a.moduleid = b.moduleno and a.Moduledescription LIKE '%desc%u%'

Open in new window

Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
if your join returns more rows than the original query, that means that per "moduleid = moduleno" condition, you have several rows in the joined moduleversion table for a single row in the masterlist table.

you will need to identify a rule what value/row from the moduleversion you want return.

a "simple" version woudl be this:
select a.Moduledescription,a.moduleid, max(b.moduleversion ) max_module_version
FROM db_SM_Category.dbo.modulemasterlist a INNER JOIN 
      db_HB_SM_LD.dbo.tblArticleModuleVersion b ON a.moduleid = b.moduleno 
WHERE a.Moduledescription LIKE '%desc%u%'

Open in new window

Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
sorry:
select a.Moduledescription,a.moduleid, max(b.moduleversion ) max_module_version
FROM db_SM_Category.dbo.modulemasterlist a INNER JOIN 
      db_HB_SM_LD.dbo.tblArticleModuleVersion b ON a.moduleid = b.moduleno 
WHERE a.Moduledescription LIKE '%desc%u%'
group by a.Moduledescription,a.moduleid

Open in new window

CERTIFIED EXPERT

Commented:
In your first query, "SELECT * FROM ModuleMasterList  ..."  you get 82 rows, but it sounds like not all of these rows have distinct values for the fields Moduledescription and moduleid.

Include your primary key ( e.g. an ID field or something similar, if there is one) for table A in the second query - so that every row from table A returns a unique combination of ID,Moduledescription and moduleid.

Whether this does what you want will depend on what is in your tables.  When you get 1000 rows back, you must be getting multiple rows that refer to the same ModuleID - do these rows ALL have the same moduleversion ?
CERTIFIED EXPERT

Commented:
Also, do you possibly have some rows in table A that have no matching data in table B ?

If so, you'd need to use a LEFT JOIN instead of an INNER JOIN ...  the code below is based on your original. Ill do another one that includes an extra ID field.  

And you might need to use LEFT JOIN with angell111's suggestion as well.



select distinct a.moduleid,a.Moduledescription,b.moduleversion 
FROM db_SM_Category.dbo.modulemasterlist a LEFT JOIN
      db_HB_SM_LD.dbo.tblArticleModuleVersion b ON a.moduleid = b.moduleno
and a.Moduledescription LIKE '%desc%u%'
 

Open in new window

Author

Commented:
Your query

select a.Moduledescription,a.moduleid, max(b.moduleversion ) max_module_version
FROM db_SM_Category.dbo.modulemasterlist a INNER JOIN
      db_HB_SM_LD.dbo.tblArticleModuleVersion b ON a.moduleid = b.moduleno
WHERE a.Moduledescription LIKE '%desc%u%'
group by a.Moduledescription,a.moduleid


is resulting again 62 rows
CERTIFIED EXPERT

Commented:
Here's one with an extra ID field added - you would need to replace the fieldname "a.ID" if your table has a different name for its key field:

select distinct a.id, a.moduleid,a.Moduledescription,b.moduleversion
FROM db_SM_Category.dbo.modulemasterlist a LEFT JOIN
      db_HB_SM_LD.dbo.tblArticleModuleVersion b ON a.moduleid = b.moduleno
and a.Moduledescription LIKE '%desc%u%'
CERTIFIED EXPERT

Commented:
See above: try LEFT JOIN ...   you'll probably find 20 items with no version number.

Author

Commented:
Moreover i checked this:

select moduleno,moduleversion from db_HB_SM_LD.dbo.tblArticleModuleVersion where moduleno = '0390809999'

Its resulting  100 rows

but all the moduleno and moduleversion are same!! in all rows

Author

Commented:
select distinct a.moduleid,a.Moduledescription,b.moduleversion
FROM db_SM_Category.dbo.modulemasterlist a LEFT JOIN
      db_HB_SM_LD.dbo.tblArticleModuleVersion b ON a.moduleid = b.moduleno
and a.Moduledescription LIKE '%desc%u%'


query resulted version number as NULL with 1000 more rows

Author

Commented:
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'id'
CERTIFIED EXPERT
Commented:
Sorry ... should have been more specific ... try angell111's  MAX query with a left join:

select a.Moduledescription,a.moduleid, max(b.moduleversion ) max_module_version
FROM db_SM_Category.dbo.modulemasterlist a LEFT JOIN
      db_HB_SM_LD.dbo.tblArticleModuleVersion b ON a.moduleid = b.moduleno
WHERE a.Moduledescription LIKE '%desc%u%'
group by a.Moduledescription,a.moduleid

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
select a.Moduledescription,a.moduleid, max(b.moduleversion ) max_module_version
FROM db_SM_Category.dbo.modulemasterlist a LEFT JOIN
      db_HB_SM_LD.dbo.tblArticleModuleVersion b ON a.moduleid = b.moduleno
WHERE a.Moduledescription LIKE '%desc%u%'
group by a.Moduledescription,a.moduleid


This worked with NULL :-)
Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
>This worked with NULL :-)

as aflockhart said:

> you'll probably find 20 items with no version number.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.