Nesting SQL

Ausway
Ausway used Ask the Experts™
on
Hi,

I am trying to run the following query and trying to including certain sellcodes. ie

sellcode.sellcode_code Like '8258%' and sellcode.sellcode_code Like '9012%', they are a part of sellcode.sellcodetype_code 'MANUAL'.

I am not too sure how to nest the query or if even nesting is the correct way to do what I am trying to achieve.

SELECT itemcolour.itemcolour_ref,
sellcode.sellcode_code, item.description
FROM RMS.dbo.item item,
itemcolour,
itemcoloursize,
sellcode
WHERE itemcolour.item_code = item.item_code
AND itemcoloursize.itemcoloursize_id = sellcode.itemcoloursize_id
AND itemcoloursize.itemcolour_id = itemcolour.itemcolour_id
AND ((sellcode.sellcodetype_code Not In ('MANUAL','ISBN')))
ORDER BY sellcode.sellcode_code

Thanks in Advance.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
TRY:
SELECT 
	itemcolour.[itemcolour_ref],
	sellcode.[sellcode_code], 
	sellcode2.[sellcode_code], 
	item.[description]
FROM 
	RMS.dbo.item item,
	itemcolour,
	itemcoloursize,
	sellcod,
	sellcode sellcode2 
WHERE 
	itemcolour.item_code = item.item_code
	AND itemcoloursize.itemcoloursize_id = sellcode.itemcoloursize_id
	AND itemcoloursize.itemcolour_id = itemcolour.itemcolour_id
	AND ((sellcode.sellcodetype_code Not In ('MANUAL','ISBN')))
	AND sellcode2.sellcode_code Like '8258%' and sellcode2.sellcode_code Like '9012%' and sellcode2.sellcodetype_code = 'MANUAL'
ORDER BY 
	sellcode.sellcode_code
	

Open in new window

Lee WadwellProject Architect

Commented:
Hi Ausway,

If I am understanding you correctly ... you want:
  AND sellcode.sellcodetype_code = 'MANUAL'
  AND (sellcode.sellcode_code Like '8258%' OR sellcode.sellcode_code Like '9012%')

but, this will not work while you have:
  AND ((sellcode.sellcodetype_code Not In ('MANUAL','ISBN')))
as this excludes sellcode.sellcodetype_code = 'MANUAL'.


lwadwell

Author

Commented:
Hi Iwadwell,

Is there a way of writing the query to achieve what I am trying to do?

Cheers.
Lee WadwellProject Architect
Commented:
Ausway,

without a doubt in my mind ... however, I am not exactly sure what you want to achieve.

The basic query I see is below ... but are there other rules you need to give you less or more rows returned?

lwadwell
SELECT itemcolour.itemcolour_ref, 
sellcode.sellcode_code, item.description
FROM RMS.dbo.item item, 
itemcolour, 
itemcoloursize, 
sellcode
WHERE itemcolour.item_code = item.item_code 
AND itemcoloursize.itemcoloursize_id = sellcode.itemcoloursize_id 
AND itemcoloursize.itemcolour_id = itemcolour.itemcolour_id 
AND sellcode.sellcodetype_code = 'MANUAL'
AND (sellcode.sellcode_code Like '8258%' OR sellcode.sellcode_code Like '9012%')
ORDER BY sellcode.sellcode_code

Open in new window

Author

Commented:
Moved on form query.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial