| Author |
Topic |
|
ElenaPie
Starting Member
5 Posts |
 Posted - 2007-11-15 : 14:59:30
|
| I've been struggling with this problem, hope someone can help. I'm using SQL Server 2005 Express. I have a simple database with 7 tables (m64,m67,m69,m71,m87) all have an identical design(column name, type). All I want to do is pull out one recordset (if it's in that table) from each of the tables. This is what I have:"SELECT * FROM m64, m67, m69, m71, M87 WHERE P_N Like '" & Request.Querystring("P_N") & "'"If I only enter one table, I get a result. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-11-15 : 15:19:49
|
| You need to UNION them together. Check out UNION in SQL Server Books Online.Also, your database isn't designed properly if you have tables designed this way.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
 |
|
|
ElenaPie
Starting Member
5 Posts |
Posted - 2007-11-15 : 16:47:50
|
| The database design was a project in a Database Design class offered by a local college, I thought it was pretty good since I got an "A". The database contains 270 tables that contain parts reproducted for a specific year, make and model. In a few cases certain parts are used on multiple models. Each table has a Primary Key, identical columns and data types; each table also populates a website (m64 is short for 1964 Mustang). I have tried UNION, actually used UNION ALL because I want to see what parts are included in each table; but the only table that prints out is the last one in the list. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-11-15 : 16:54:47
|
| Post what you tried with UNION ALL.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
ElenaPie
Starting Member
5 Posts |
Posted - 2007-11-15 : 17:01:47
|
| SELECT * FROM m64 WHERE P_N Like '" & Request.Querystring("P_N") & "' UNION ALL SELECT * FROM m67 WHERE P_N Like '" & Request.Querystring("P_N") & "' UNION ALL SELECT * m71 WHERE P_N Like '" & Request.Querystring("P_N") & "' |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-11-15 : 17:07:07
|
| You are posting application code. Post what you would run in Management Studio or Query Analyzer as that is where you debug queries.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
ElenaPie
Starting Member
5 Posts |
Posted - 2007-11-15 : 17:16:33
|
| Never mind, I got it - code was missing "From" before M71. thanks for your help. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-11-15 : 17:36:18
|
quote:
Originally posted by ElenaPie
The database design was a project in a Database Design class offered by a local college, I thought it was pretty good since I got an "A". The database contains 270 tables that contain parts reproducted for a specific year, make and model. In a few cases certain parts are used on multiple models. Each table has a Primary Key, identical columns and data types; each table also populates a website (m64 is short for 1964 Mustang). I have tried UNION, actually used UNION ALL because I want to see what parts are included in each table; but the only table that prints out is the last one in the list.
Sounds like you could have put everything in one table, and just added model and year columns.The problems you are having writing the query are directly caused by this bad design.Sorry, but no "A" for you or your teacher.  CODO ERGO SUM |
|
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2007-11-15 : 17:46:01
|
| Certainly not an A in the security bit. There's a nice big, juicy SQL injection there. |
|
|
|
ElenaPie
Starting Member
5 Posts |
Posted - 2007-11-16 : 11:17:09
|
| This has to be the nastiest forum I've ever been to. Good Bye. |
|
|
|
Van
Constraint Violating Yak Guru
462 Posts |
Posted - 2007-11-16 : 11:23:12
|
| I think they are trying to give you constructive criticism. Don't take it personally but more as a way to improve your existing system. I'd hate to see some of MVJ and Lozt's db designs when they first started out in the dba field. As a matter of fact, I laugh when I run across some of my old stuff from 10 years ago. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-11-16 : 14:23:51
|
| If you come to the forum and react to criticism of your database design by claiming that you think it's good because it got you an A in a class, you should be prepared for feedback to the contrary.You can expect less pointed responses if you react by asking someone to explain why they think it is a bad design.CODO ERGO SUM |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-11-16 : 18:01:54
|
quote:
Originally posted by Van
I think they are trying to give you constructive criticism. Don't take it personally but more as a way to improve your existing system. I'd hate to see some of MVJ and Lozt's db designs when they first started out in the dba field. As a matter of fact, I laugh when I run across some of my old stuff from 10 years ago.
I developed well designed databases when I first started.  Nowadays, I mostly develop ugly hacks to fix even uglier designs developed by people who got an A in their design class. CODO ERGO SUM |
|
|
|
|
Should be a simple SELECT statement