[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 240
  • Last Modified:

SQl query

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 !

0
SmartestVEGA
Asked:
SmartestVEGA
  • 9
  • 6
  • 3
2 Solutions
 
aflockhartCommented:
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.
0
 
SmartestVEGAAuthor Commented:
i did distinct but its giving 62 rows not 82 :-(
0
 
SmartestVEGAAuthor 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

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
SmartestVEGAAuthor Commented:
What i need is..

The module version for each moduleno with "%desc%"
0
 
SmartestVEGAAuthor 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

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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

0
 
aflockhartCommented:
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 ?
0
 
aflockhartCommented:
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

0
 
SmartestVEGAAuthor 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
0
 
aflockhartCommented:
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%'
0
 
aflockhartCommented:
See above: try LEFT JOIN ...   you'll probably find 20 items with no version number.
0
 
SmartestVEGAAuthor 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
0
 
SmartestVEGAAuthor 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
0
 
SmartestVEGAAuthor Commented:
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'id'
0
 
aflockhartCommented:
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
0
 
SmartestVEGAAuthor 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 :-)
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>This worked with NULL :-)

as aflockhart said:

> you'll probably find 20 items with no version number.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 9
  • 6
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now