homepage Welcome to WebmasterWorld Guest from 107.22.45.61
register, free tools, login, search, subscribe, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
Odd queries!
Matthew1980




msg:4379154
 2:27 pm on Oct 25, 2011 (gmt 0)

Hello All,

I rarely post on here, but I have no choice as I don't really know enough to even know if this is possible, and as it isn't to do with a website I hope as that's Ok. It's all mysql related though!

Consider:-

Query 1:-

SELECT `Column1`, `Column2` FROM `TableName` WHERE `Column3` = ' + USER_TEXT_HERE + ' ORDER BY `Column1`

Then `Column2` provides the key Id for this next query - this can be any number of records, so I use IN() after converting an array to comma separated values


SELECT `Column1`, `Column2`, `Column4` FROM `TableName1` WHERE `Column3` IN(QUERY_1_COLUMN_2) ORDER BY `Column1`

The same column then provides the next couple of queries (all the same format, but different tables).

Then While these queries are completed in a for loop. the data held in `Column1` from the first query is used to link up to the data from the subsequent queries to show histories from that initial enquiry!

Sorry If I have lost you by now, but my question is this - can I simplify this? It just seems a lot of loops and queries within loops when a temp table could possibly help (never done one though).

I can't change the structure of the db as it's in constant use and the powers that be say its to be left as is.

I'm even wondering if what I am after is even possible?

Cheers,
MRb

 

Demaestro




msg:4379163
 2:47 pm on Oct 25, 2011 (gmt 0)

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.

Matthew1980




msg:4379208
 4:26 pm on Oct 25, 2011 (gmt 0)

Hi there Demaestro,

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

Cheers,
MRb

Matthew1980




msg:4379285
 7:10 pm on Oct 25, 2011 (gmt 0)

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

Matthew1980




msg:4379341
 9:36 pm on Oct 25, 2011 (gmt 0)

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

Jstanfield




msg:4380339
 7:05 pm on Oct 27, 2011 (gmt 0)

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.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About
© Webmaster World 1996-2014 all rights reserved