Combining Sql Statements

  • (2 Pages)
  • +
  • 1
  • 2

18 Replies - 7279 Views - Last Post: 04 April 2003 - 02:32 AM Rate Topic: -----

#16 klewlis  Icon User is offline

  • cur tu me vexas?

Reputation: 8
  • View blog
  • Posts: 1,723
  • Joined: 09-November 01

Re: Combining Sql Statements

Posted 19 March 2003 - 04:12 PM

well i actually have control over this server, so that's not the issue. i don't think 4.1 is available for windows, that's all. (and this one has to be done on a windows server for other reasons.... :)
Was This Post Helpful? 0
  • +
  • -

#17 malkiri  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 3
  • View blog
  • Posts: 364
  • Joined: 29-March 01

Re: Combining Sql Statements

Posted 28 March 2003 - 06:21 PM

I just scrolled through the posts briefly, but I didn't see this mentioned. An simple option is to use the UNION keyword. I'm not sure if MySQL supports this, but I'm fairly confident it's standard. It would go something like this:

SELECT id FROM table WHERE flevel = '2' AND (other criteria)
UNION
SELECT id FROM table WHERE parent = 'level2id'
UNION
SELECT id FROM table WHERE parent = 'level3id'
UNION
SELECT id FROM table WHERE parent = 'level4id' AND (other criteria)



The result set will take the aliases from the first SELECT statement. All UNIONed SELECTS must have the same number of variables selected, and probably matching types, as well.

-Malkiri
Was This Post Helpful? 0
  • +
  • -

#18 cyberscribe  Icon User is offline

  • humble.genius
  • member icon

Reputation: 10
  • View blog
  • Posts: 1,062
  • Joined: 05-May 02

Re: Combining Sql Statements

Posted 04 April 2003 - 02:30 AM

Hi Klew,

UNION is available in MySQL 4.0.0+.

In databases with a longer standing pedigree (like Informix) you could use something called TEMP TABLES to create a table on the fly, put the data you want there (instead of into a PHP array that costs memory) and then manipulate that data against other data. MySQL doesn't handle the TEMP part of all that (cleaning up the table when the query is done) -- but if you are willing to create your tables beforehand and clean them up when you're done you can use the SELECT ... INTO syntax of MySQL to accomplish a similar result.

Sometimes using TEMP TABLEs is a bit of a kludge (band aid) that comes in to play when you haven't designed your table structures with the queries you will be using later in mind. Often, however, TEMP TABLES get used in large, complex data systems more often than you'd imagine because you just can't structure every set of tables to accomodate every type of bizarre relationship that management (or whoever) can cook up to want a report about.

Hopefully this will help you reduce the number of queries in SQL and the overhead in PHP of manipulating array data.

Cheers,
CS
Was This Post Helpful? 0
  • +
  • -

#19 cyberscribe  Icon User is offline

  • humble.genius
  • member icon

Reputation: 10
  • View blog
  • Posts: 1,062
  • Joined: 05-May 02

Re: Combining Sql Statements

Posted 04 April 2003 - 02:32 AM

Correction: that's INSERT ... SELECT syntax, not SELECT ... INTO (although some databases support a select into a table MySQL only seems to allow outfiles).

http://www.mysql.com...ERT_SELECT.html
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2