I think this is what you want.

When you use IN, you only want to return 1 column in what is called a Sub-Select

SELECT `Column1`, `Column2`, `Column4` FROM `TableName1` WHERE `Column3` IN (SELECT `Column3` FROM `TableName` WHERE `Column3` = ' + USER_TEXT_HERE + ') ORDER BY `Column1`


Because you want to match Column3 with your IN clause you only want to return Column3 in the sub-select.

Hope this makes sense, post back if you have more questions.

Hi there Demaestro,

Thanks for the tips, I'll post back when I'm working on this tonight.

Cheers,
MRb

Hi all,

Apologies for the code dump, but this is the issue in its entirety.

First query (This already uses a sub-select)

SELECT
`serial`.`id_number`, `serial`.`serialnumber`, `serial`.`date` FROM `serial`
WHERE
`serial`.`typeofunit` =
(SELECT `ID_Number` FROM `TypeOfUnit` WHERE `Description` = '" & CStr(SearchParam.SelectedItem) & "' LIMIT 1)
ORDER BY
`serial`.`date`

Second query (PassedArray returns data like: 1234,2345,3456,2435) If there are no matches the program skips to the next query - only matches to the serialnumber id are displayed

SELECT
`newbuild`.`id_number`, `newbuild`.`date`
FROM
`newbuild`
WHERE
`newbuild`.`serialnumber` IN(" & String.Join(",", PassedArray) & ")
AND
`newbuild`.`current` = TRUE
ORDER BY
`newbuild`.`date` "

Third query (Takes the same PassedArray with the same format: 1234,2345,3456,2435) If there are no matches the program skips to the next query - only matches to the serialnumber id are displayed

SELECT
`rma`.`id_number`, `rma`.`date`
FROM
`rma`
WHERE
`rma`.`serialnumber`
IN(" & String.Join(",", PassedArray) & ")
ORDER BY
`rma`.`date`

Fourth query (Takes the same PassedArray with the same format: 1234,2345,3456,2435) If there are no matches the program skips to the next query - only matches to the serialnumber id are displayed

SELECT
`comments`.`id_number`, `comments`.`date`
FROM
`comments`
WHERE
`comments`.`serialnumber`
IN(" & String.Join(",", PassedArray) & ")
ORDER BY
`comments`.`date`

This makes a datagrid (VB6) get populated with rows that are relevant to the initial query type, and matches that ID_Number with corresponding ID_Numbers in the subsequent 3 queries and then displays them and attaches the serial number (`serial`.`serialnumber`) to relevant matches (from first query) to matches generated by the others.

My appologies if this is confusing, but I'm attempting to convert this application to VB.net and this is the only thing as I can't get done as VB.net doesn't seem to support row-at-a-time generation so that I can loop through like I would in PHP, instead you generate the results in one lump then display the lot. Not good.

Anyway, any pointers are welcome, I'm trying to see if I can just condesnse these queries a little.

Cheers for reading this anyway.
MRb

UPDATE:

Is this sort of query even possible?

SELECT
`newbuild`.`id_number`, `newbuild`.`date`
(SELECT `serial`.`serialnumber` FROM `serial` WHERE `ID_Number` IN(4022,4158,4366,4369))
AS
`newbuild`.`SelectedSerial`
FROM
`newbuild`
WHERE
`newbuild`.`serialnumber`
IN(4022,4158,4366,4369)
AND
`newbuild`.`current` = TRUE
ORDER BY
`newbuild`.`date`

I'm stumped. Coffee has run out, and I'm getting bored now!

Cheers,
MRb

You can make the database server heap it together via UNION provided all queries return the same number of columns with the same datatypes -- and in your examples they do. Just add a "type" column so you can tell the difference between a newbuild, rma, and comment.


SELECT
0 `type`, `newbuild`.`id_number`, `newbuild`.`date`
FROM
`newbuild`
WHERE
`newbuild`.`serialnumber` IN(" & String.Join(",", PassedArray) & ")
AND
`newbuild`.`current` = TRUE

UNION

SELECT
1 `type`, `rma`.`id_number`, `rma`.`date`
FROM
`rma`
WHERE
`rma`.`serialnumber`
IN(" & String.Join(",", PassedArray) & ")

UNION

SELECT
2 `type`, `comments`.`id_number`, `comments`.`date`
FROM
`comments`
WHERE
`comments`.`serialnumber`
IN(" & String.Join(",", PassedArray) & ")

ORDER BY type,date

When UNIONing queries together, you can only have one ORDER BY clause, and it must be at the end. I chose to make type numeric so that I could preserve your order (first newbuilds by date, then rmas by date, then comments by date)...


You'd end up with data that looks like this:
type,id_number,date
0,12345,2011-01-31
1,12345,2011-02-03
2,12345,2011-02-03
2,12345,2011-02-10

You can examine the contents of the type column and act accordingly. (i.e. Click here to view build, click here to view comment, click here to view rma). You can make that column invisible in your grid so users don't look at it and wonder.