[RESOLVED] PHP + MSSQL query problem

Z3RatuL

Hey guys,

Recently I got a project where I have to display some results of products taken from a mssql database.

What is the difficult thing for me is that this company's table structure is a little odd. Instead of keeping their products into one table and using one to many or one to one relationships they have 7 tables called products1 to products7 where they store their products.

Now, what I have to display, is the product's code, name, price and quantinty which is fairly easy but the thing is I want to do it using one query. Why I'm saying that?

Each table's structure is identical; each table has a unique id, product's code, name, price, vat, and quantinty columns; and for me to use a single query there has to be a field where all the tables will have a common value, say web_active to yes. I can't do that cause as you may realize each column has it's own value and I can't change the table structure of the company's database it was said to me from the start. What I did was to make 7 identical queries, one for each table.

Now my question is if there's a way to make a single query for all the tables so I can display the data correctly?

Roger_Ramjet

This is a job for a UNION QUERY

If the tables are all identical in layout then that makes things really easy. If you want to know which table a row came from then you add tablename AS tbl, to your selects: substituting the relevent table name of course, tablename is not some magic mysql var.

Look to the part about ordering the results in the manual: you can order them within each table or across all tables.

Z3RatuL

Is this doable with MSSQL? Cause I'm using MS SQL Server and not MySQL for this project. Anyway, I tried using it and I got an error saying Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "ap1.code" could not be bound.:

"SELECT ap1.code as id1, ap1.name as desc1 FROM apogr1 ap1 UNION SELECT ap2.code as id2, ap2.name as desc2 FROM apogr2 ap2 WHERE ap1.code LIKE '%".$search_string%."' OR ap2.code LIKE '%".$search_string%."'";

Roger_Ramjet

Yes union queries are doable in mssql.

What this error means is that the Where clause ON THE SECOND SELECT QUERY names a column which cannot be found, because it is not in any table named in the from clause.
Your mistake is to think that a union query operates like a single select query with the where clause at the end. A union query is not some other way to join tables - a union query is quite simply that, the union of self-standing select queries.

$sql = "SELECT ap1.code as id1, ap1.name as desc1 FROM apogr1 ap1 WHERE ap1.code LIKE '%".$search_string . "%' 
     UNION
     SELECT ap2.code, ap2.name FROM apogr2 ap2 WHERE ap2.code LIKE '%".$search_string . "%'";

Also note that the column names returned by the query are those used in the first query. They cannot be changed from select to select or how could the query return a unified resultset. So it is unneccessary to alias columns in any but the first select.

Z3RatuL

Thank you for your help it really worked like a charm... You rule 😃