Multiple row selects with single query

[hinch]

Either I'm being thick or I just can't find a way of doing this in a single query.

At the moment I have an array list of ID's something along the lines of

$array=1,2,3,4,5,676,17 etc etc

Now at the moment I have a basic:

for each array element select * from table echo out, next

But this is already inside a while loop for a master table output (the array being generated from a single column of the master table anyway.

What I'm trying to/want to do is basically combine it into a single query. So something more along the lines of

select * from table where ID=$array

and it will select every row in the db matching a member of the array against their ID.

I'm fairly sure its possible I think I'm just having one of my special days so failing with google.

[brazenskies]

you can use IN

Select *from table where id IN (1,2,3,4,5,6,7)

That returns all rows with ID 1-7

I think that's what you mean?

[Fumigator]

Without seeing the code it sounds like you're performing a SELECT on your master table, getting the ID, and then performing another SELECT on another table using that ID. Is that right?

Assuming it is, you can JOIN the two tables together using one query.


SELECT master.data, other.data
FROM master
JOIN data
ON master.id = data.master_id

[hinch]

not quite fumigator

master table is

ID, name, serialnos
INT, Varchar, Varchar(array)

so a row in master table looks something like

1, laptop, 3,6,10,22,14

serialnos looks up against table

ID, SerialNo
Int, Varchar

tables are linked serialnos table1 to ID table2

if that explains it any better.

IN may work though tis abit late for me to try it now but will give it a raz in the morning.

[guelphdad]

get rid of the array, normalize your data instead.

[hinch]

get rid of the array, normalize your data instead.

care to explain how i can normalise out further than i already have ?

[Fumigator]

Your column serialnos contains multiple values. That is no normalized.

Normalized would be a table that contains one row per serialnos value and points back to the master table via foreign key. You may already have the data arranged that way, as you do have a serialnos table, and even mention that it's linked to your master table with an ID. So why have you stored the serialnos in the master table in an un-normalized way?

[hinch]

i haven't as of yet that was simply the way i had in mind as i was trying to avoid looping through 2 db tables and doing one of them as a loop within a loop so my theory was storing an array of the id's of the items from the 2nd table in the primary table then doing a single select within the main loop

as it turns out handling the array just over complicated everything so i went back to the reverse relationship by now adding an additional column in the 2nd table which references back to the primary tables itemid. still means i'm doing a loop within a loop but meh :( maybe i'll just create a sp or some views for it in the future.

[Fumigator]

That's where you'd use a JOIN as I mentioned in post #3 which would allow you to use just one loop.

[hinch]

no it wouldn't as there's multiple sub entries to a single primary entry using a join would mean you couldn't iterate around the 2nd select entries you could perhaps use a subselect instead i guess but a join wouldn't return the 2nd table as a separate object which is effectively what i need it as.

[Fumigator]

A join would iterate through all rows in the 2nd table. You get the data from the 1st table every time around, so you set up a simple control break where you only pay attention to the data from the 1st table when it changes from the previous row.

[hinch]

far simplier ways of doing it than like that tbh there's a time and a place for a join and this isn't it.
I asked about array matching since in mssqlserver its possible to match an array and explode as a sub select in query returning 2 data objects and didn't know if it was possible in mysql.

I've restructured the database now anyway to a standard one to many setup not quite as efficient as i was hoping in code terms but since we're only talking a few hundred records each shot its not going to have too much impact on the system