Combining Queries (1 Viewer)

[crownedzero]

I'm still tinkering through a project I've tasked myself with and I've come into a small issue with running a query. The SQL state below removes all ItemNumbers from discontinued stock that have 0 available quantity in any of its sizes.

[COLOR=#000000]INSERT INTO tblBSLR ( ItemNumber )
SELECT tblInventory.ItemNumber, *
FROM tblInventory
WHERE (((tblInventory.ItemNumber) In (select ItemNumber from tblInventory
where Type = 'F' or (Type = 'D' and Available > 0))));
[/COLOR]

I also need this query to append data that does not meet one of several ItemNumbers i.e. DP9*, DI*, PA9* etc. as these represent our faulty/defective items.

Is this best left as two seperate queries? If not how can I combine two seperate statements?

 

[crownedzero]

Trying to combine to queries using the union statement. I think for the most part that's working; however, now I am running into an issue using LIKE IN in a NOT IN subquery. Any thoughts?

[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]SELECT tbl.tblfield, *
FROM tbl
WHERE tbl.tblfield In (select tbl.tblfield from tbl[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]where tbl.tblfield1 = 'F' or (tbl.tblfield1 = 'D' and tbl.tblfield2 > 0))[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][/SIZE][/FONT] 
[FONT=Times New Roman][SIZE=3]UNION[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][/SIZE][/FONT] 
[FONT=Times New Roman][SIZE=3]SELECT tbl.tblfield
FROM tbl
WHERE tbl.tblfield NOT IN
(SELECT tbl.tblfield from tbl
WHERE tbl.tblfield LIKE IN ("DP9%", "DP7%", "DI9%"));

 

[pbaldy]

I'm not sure you can use Like with In. You might try

WHERE tbl.tblfield LIKE "DP9%" OR tbl.tblfield LIKE "DP7%" OR tbl.tblfield LIKE "DI9%"

You probably want * for a wildcard, and UNION queries require the same number of fields in each section.

 

[crownedzero]

I have a lengthy list of criteria was hoping to cirumvent a long OR statement using a LIKE/IN.


As far as the UNION you referring to the fields addressed in the statement or in the table? I'll have to check up on this.

 

[pbaldy]

Fields returned by the respective SELECT statements.

 

[crownedzero]

Both queries are applied to the same table, so this shouldn't be a problem, correct?

 

[pbaldy]

It was in your example, because you had

SELECT tbl.tblfield, *

and

SELECT tbl.tblfield

The first will return the specified field plus all the fields in the table, the second is only returning the single specified field.

 

[crownedzero]

I see, I need all fields in the table returned on both queries. That's my inexperience showing =)