Posts by tesuji

Hello dmmckelv,

When Edgar Frank Codd invited relational databases his primary idea was to found it on
logic algebra and set theory. The rows of relational tables (aka relations) are sets. Therefore, there aren't duplicate rows nor any particular order of rows. From this point of view, "I am looking for something that would give me the speed field value for the row before" is rather irrelevant.

However, to put any meaningful signification on it, your table must already have a column
which allows such an ordering, for example date, timestamp or auto-increment numbers.
If you don't have such special column (or set of columns) within a table, it is impossible to determine the predecessor of a specific row because the today predecessor could be the successor row tomorrow (e.g. due to inserting and deleting rows in the meantime).

To cut a long story short, what's that special column of your table to get particularly ordered rows? You may also post the creating statement of your table.

-----
tesu

Hi Finau,

If both following statements are true

1. Each table has a column named region_id of compatible data type
2. At least one of those tables has primary key region_id AND all other
tables containing region_id that column is attributed to be foreign key.

then you may carry out this sql statement

select * from  tbl_residential_sale as rs join tbl_residential_rent as rr
         join  tbl_commercial_sale as cs join tbl_commercial_rent as cr
         join  tbl_commercial_lease as cl join tbl_land_sale as ls
         join  tbl_land_lease as ll

to get a first result. Afterwards you can add some further predicates, like cr.start_date IS NOT NULL AND cr.expire_date >= NOW()..., to where-clause for reducing result set.

I really doubt a little that each table has column region_id. You may post the creating statements of your seven tables.

Btw, your select statement seriously lacks of join constraints which must be added to where-clause, as for example: where rs.joining-column = rr.joining-colum etc. If you omit such constraints you will get a undesirable cross products (cartesian products).

-----
tesu

Thanks for the quick response!

I am missing one thing though, I do not see Author Name at all in your example?

I did not change your table tbl_Author which already has Author-Name. Therefore I left out your 3rd table !

Hello,

in tbl_poems exists a transitive dependency poem-ID -> Author-ID -> Book-ID. Therefore, this table does not satisfy 3NF. fk Author-ID should be removed and inserted into tbl_Book. The result is:

tbl_Poems:
- poem-ID - pk
- Poem-Title
- Poem
- Book-ID - fk

tbl_Book:
- Book-ID - pk
- Book-Name
- Author-ID - fk

your table structure can easily be put on one form because you have two one-to-many relationships (aka master detail tables). So one author may have many books related to. You can select an existing author or enter a new one in the 1st part of your form. In a 2nd part you can then select an existing book of that author or you may enter a new book where the author_ID would then be inserted into the tbl_book. Once selected a book you will see many poems of that book or in 3rd part of you form you may enter new poems. If you store new poem, current book_ID would be inserted in tbl_poem. Graphical element to hold that data can be combo box, grid element etc.

Ok, details depend on the programming language, database, web server you will use to carry out your poem project.

krs,
tesu

So you want to decipher a 160 bit binary HSA1 string? Impossible! (except for hiring some Roadrunner doing brute-force decryption.)

Hi

There is a function LAST_INSERT_ID() what returns the last-created auto-increment value. So first insert parent data what creates a new auto-increment ID. Then insert row in child table where LAST_INSERT_ID() function is put in the values list of the insert statement, for example:

-- first insert parent row
insert into my_contacts (first_name) values ('GFXtm')
-- this generates a new value for auto-increment contactID

-- Now insert child row
insert into interests (interest, contactID) values ('databasing',  LAST_INSERT_ID())
-- this will assign the just generated auto-increment to foreign key contactID

First time, there is no interference between the auto-increment values of both tables: In Mysql all values of the values list will be inserted first. Finally the new auto-increment of table interests will be generated which then become last-inserted ID. That also means that you cannot use above insert statement, if you want to insert multiple children to same parent. For this case you must store the LAST_INSERT_ID() value from parent table into a variable.

krs,
tesu

Hi jakesee

For better handling you need one root only. This can easily be done by defining a master root where all other roots can be formally connected to. Only this master root will not have a parent. Additionally, in my tree table each node has a level number what simplifies traversing the tree.

There is a remarkable paper on

http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

which is based on Joe Celko's book: Trees and Hierarchies in SQL for Smarties.

The examples in that paper are mostly based on the nested set model, there is also a short introduction to the adjacency list model and its limitations. Your example is based on that adjacency list model.

I will think over how to traverse a complete tree only by one SQL statement (Actually, I am doing such traversing with C++ program by way of recursive functions because our category tree has some hundreds nodes with extremely various depths). Possibly the new WITH clause of SQL 2003 what has a recursive part may help here. I personally would prefer the nested set model but inserting new nodes in an already existing chain of nodes isn't that easy.

krs,
tesu

Hi rich_m

>>> My team( all freshers) are developing a project that will involve many Modules.

You must be "sophos ke moros" to carry out such database design successfully, which is every project's most crucial design phase. So wait a year or two until you will have past database design course.

>>> I want to know if it is a good practice to have different databases for a single project!!!

Bad practise, don't do that!!!

>>> I know that this could lead to a bad design, if you make the wrong decisions of placing the respective tables.

No, it couldn't lead to but it is terribly bad design.

>>> Each database of a module will contain only the module specific tables (that do not need communication to the other tables of other modules), commonly used tables will be placed in a common database.

Eh, to make chaos complete, you also plan a "centralised, common" database. Will everybody who programs modules also be allowed to design his module-focused tables himself? That would really speed-up into chaos.

>>> I want to know the demerits of such a design, cause junking up a database with a lot
of tables, seems to be a very complicated system to understand, I mean the documentation
part.

After having collected a billion penalty marks you are going to redesign that datamodel
for the third time. Btw, no matter how complicate a datamodel is, its …

hi,

you may google Joe Celko trees

krs,
tesu

hello kvdd,

there must be at least one space between END and IF in ENDIF: END IF;

If you omit the else part, you will get NULL , if condition is not satisfied.

I have checked your picture and it only shows negative values in 3rd column. Therefore the condition is never met and the select returns NULL only.

krs,
tesu

Hello,

1. how come that in

>>> select temp.start, temp.end, temp.gene_name, min(temp.distance) as
>>> minimum_distance from (select start_bindsite, end_bindsite, gene_name,
>>> abs(gene_start-start_bindsite) as distance from genome, binding_sites group by
>>> gene_name, start_bindsite

group-by clause contains 2 columns only whereas you select 3 columns? this should produce a true error, if not, something is wrong with your database.

2. Because of
>>> from genome, binding_sites
you are doing inner join. Therefore you need a where clause with at least one join constraint. Because this is missing an undesirable cross join would be computed.
(I advice you a more modern join construct: from genome g INNER JOIN binding_sites b ON g.column = b.column. Here predicate g.column = b.column is the join constraint.)

krs,
tesu

Hi varun077,

Most of your questions including your several performance and maintenance issues will simply get solved if you (re-)design a true relational datamodel based on correct relationships (cardinalities) and effective normalization, as for example:

1. Foreign keys: will appear automatically if relationships are correctly taken into account and implemented in database. They are a must for ensuring referential integrity. Why shall one painfully reinvent that at Java programming level or even simulate that by triggers if database has already the most effective referential-integrity feature? It is true myth that foreign keys would provoke serious performance issues. As for triggers: Simulating foreign keys by means of triggers is like breaking flies on wheels. In both cases you probably need indexed columns to gain reasonable performance depending on the size of tables. Additionally for triggers, interpreted program code must be executed on server, how to coordinate triggers? Did you ever worked with databases where some hundreds triggers were firing every which way?

2a. Never put different data/information described by similar attributes into one table what would lead to poor normalization what also would entail typical anomalies. Is there really a common primary key? And how to write effective-SQL select statements within such context?

2b. Never drop foreign keys, which implements one-to-many relationships, from a table and replace them by additional two-column tables containing two foreign keys from both associate tables to joining them together: You would then have many-to-many relationships only. For example, such constellation would allow …

Hello guptaalok12,

When Edgar Frank Codd invited relational databases his primary idea was to found it on logic algebra and set theory. The Rows of relational tables (aka relations) are sets. Therefore, there aren't duplicate rows nor any particular order of rows. From this point of view, asking "How To get the 5th row from a table?" is irrelevant. Yet, to put any meaningful signification on it, your table must already have a column which allow such an ordering, for example: Birthday in a person's record. Now it would be possible ordering all persons' records by birthday to find: "Who is the fifth oldest person?". If you don't have such a special column (or set of columns) within a table, it is impossible to determine the 5th row because the 5th row you get today will be the 8th row tomorrow (e.g. due to inserting and deleting rows in the meantime).

On sql server (>2000) you can get the 5th row from our person's birthday example by two nested top clauses:

SELECT TOP 1 name, birthday 
FROM 
(   SELECT TOP 5 name, birthday FROM persons 
    ORDER BY birthday ASC
) suse
ORDER BY birthday DESC

krs,
tesu

first do you first update, then:

update whattable set price = 5 where price IS NULL

From now onwards, all goods without price before will now cost 5 yourPriceUnit. Is this really correct?

krs,
tesu

Hello elliaw,

SELECT * FROM product x, offer y
WHERE x.pid=y.pid AND x.country = 'US' AND x.category like 'auto|accessories' AND y.condition = 'new'
AND y.price=( select min(z.price) from pdt_offer z
where z.pid=y.pid AND z.quality='new' AND z.stock>0)
AND y.stock >0
GROUP BY x.pid

Actually, this query is wrong! It should not work! Because of * (the sign of slackers) more than one column will be selected. Therefore, GROUP BY clause must contain more than as x.pid. If you don't get an error message, something is wrong with your data base system.

>>> 3. I need to get the image, min(price) and title for each new product.

It is a very good idea to follow kvdd's advice !

So select these three columns only and put them into GROUP BY clause. But attention: I really doubt heavily that that mysql would be able to manage an image (what type, a blob type?) in that place!

krs,
tesu

hello,

You want to select some data from three tables. This will be done by two inner joins. If you don't tell which columns of the involved tables should be joined together the result will be cartesian product (cross join), that is all columns of 1st table will be joined with all columns of 2nd table and all columns of that result will be joined with all columns of 3rd table what usually is an undesirable result. Similar results will be given by LIKE and NOT LIKE comparison operators what might be theoretically correct operators for joining constraints but rather unlikely.

If your select statement contains columns from various tables you must:

1. Figure out the correct joining constraints for WHERE or the newer ON clause

2. Supply further predicates for the WHERE clause to reduce result set to the
rows you really expect.

SELECT Records.[Officer ID],
Records.[Plate Number],
Records.[Violation Commited],
Violations.[Violation Code],
Records.[Street Name],
Records.[City of Apprehension],
Records.[Vehicle Category],
Records.[Vehicle Type],
Records.[Vehicle Brand],
Records.[Date/Time Apprehended]
FROM Records,Violations,DriverInfo
WHERE 
-- two constraints for inner joins
Records.[Plate Number] = DriverInfo.[Plate Number] AND
Records.[Violation Commited] = Violations.Violations
AND
-- your specific predicates following here

Instead of doing joins via where clause there is a more modern joining syntax:

SELECT Records.[Officer ID],
Records.[Plate Number],
Records.[Violation Commited],
Violations.[Violation Code],
Records.[Street Name],
Records.[City of Apprehension],
Records.[Vehicle Category],
Records.[Vehicle Type],
Records.[Vehicle Brand],
Records.[Date/Time Apprehended]
FROM 
Records inner join DriverInfo ON Records.[Plate Number] = DriverInfo.[Plate Number]
inner join Violations ON …

no, not that a good idea, consider:

left join (there are no null entries, because each left value has its mate in right column)
1 4
2 5
4 6

right join (if right value has no mate in left column, null will be inserted left hand side)
1 4
null 5
null 6

possibly, select looks like:
select b.donor, b.receptor from inthebelowtable a right join inthebelowtable b
on a.receptor = b.receptor where a.donor IS NOT null

yeah, aaaaaand the ardently demanding question is what does that only mean:

>> merge function is not working at all ?

Any error message, divide by zero, memory fault, endless loops, computer on strike, or something else, you see?

Hello bajanstar

you may try this statement:

select receptor as 
  'ultimate donors, really? Shouldn''t it be ultimate receptors?' 
      from inthebelowtable where receptor NOT IN 
          (select donor from inthebelowtable)

krs,
tesu

You may study programming languages like FORTH or Postscript how they do computing using stacks.

tesu

Dear kvdd

Your IF - statement is again WRONG. Pls, look at syntax in post #6 again.

>> 2) returns:
2008-05-19 09:00:00
09:00:00
21:00:00
-12:00:00
.. and here more of course

Yeah, here is more ! Did you ever see a + sign in front of the time difference?

Of course, so what about item #1, did you ever check whether time part of hour_end really satisfies condition TIME( hour_end ) > '21:00:00' ?

krs,
tesu

Ah, you again

Did you proceed in understanding EERM?

I call people who put a magical, universal "id" column with an auto-increment on all their tables "id-iots" ! (Joe Celko)

hi kvdd,

if statement is correct, also your case statement seams to be correct. You can put an else part to it to assign a value instead of getting NULL if if condition evaluates false. Two reasons why you might not get the expected results:

1. no hour_end value satisfies the condition TIME( hour_end ) > '21:00:00' (possibly already checked)

2. incompatible time formats, check the result from this select statement:
select hour_end, TIME( hour_end ), '21:00:00', TIMEDIFF( TIME( hour_end) , '21:00:00' )
from 'hours' .

You may choose sql strict mode to get all error messages.

krs,
tesu

SELECT IF TIME(hour_end) > '21:00:00' THEN  timediff(time(hour_end),'21:00') AS diff ENDIF FROM `hours`

What do you think of correct syntax?

SELECT IF ... THEN ... ELSE ... ENDIF   AS diff

krs,
tesu

Hi qdaddyo

you may try this statement:

with 

modi (tn, moda) as
  (select ticket_number, min(modified_date) from tictac 
     where assignee <> 'NONE' group by ticket_number) ,

retu (tn, reda) as
  (select ticket_number, max(return_date) from tictac 
     group by ticket_number)

select m.tn, m.moda, r.reda, datediff(day, m.moda, r.reda) as "days" 
   from modi m join retu r on m.tn = r.tn;

Not just tested, so tell me whether it's working on sql server. Instead of min()/max() top 1 together with appropriate sorting should also work.

krs,
tesu

Hi Mad Pat

In the past all database engines of mysql didn't support any referential integrity by foreign keys. Today, only innodb is able to handle foreign keys correctly (of a kind). Yet my isam db allows definition of foreign keys BUT is not able to keep referential integrity! (Though this be madness, yet there is method in it! Therefore, you must always be using innodb engine to ensure referential integrity.)

No, a simple status field every time tested when selecting product data preselected by primary key of categories would never never slow down performance. Let me compare it with a very large application SAP R/3, oracle db, about 800 users on-line, I am involved with: Filling a combo-box with about 35000 rows of material data via a many-to-many linking table by same way I already described in prior post will last less than 5 seconds. Any idea, how long it will last if there is an additional mere status field in where clause?

Ok, less than 5,01 seconds or so, so lets turn back to your conception of linking table sales_items which implements a many-to-many relationships between two associate tables. To connect sales_details with product_details a third table sales_items had been introduced what has been given its own primary key named ID to assure "unique" rows. To do this efficiently almost all relational database systems (rdbms) automatically add an unique index on that primary key (except postgresql, what explicitly requires an unique constraint).

We are …

Hello saisankar_p

Just another solution:

select s.SiteID, SiteName, s.StateID, PostCode, Phone1, StateName, 
1 as canDelete 
from tbl_site s, tbl_state a, tbl_user_site u, tbl_trial_site t
where s.StateID = a.StateID 
AND (s.SiteID = u.SiteID OR s.SiteID = t.SiteID)
UNION
select s.SiteID, SiteName, s.StateID, PostCode, Phone1, StateName, 
0 as canDelete 
from tbl_site s, tbl_state a, tbl_user_site u, tbl_trial_site t
where s.StateID = a.StateID 
AND (NOT(s.SiteID = u.SiteID OR s.SiteID = t.SiteID))
order by 1  -- ordered by s.siteID
-- order clause of union requires that columns be specified by their 
-- positions from in the result set.

If you only want to know which rows could be deleted, then use first part only:

select s.SiteID, SiteName, s.StateID, PostCode, Phone1, StateName, 
1 as canDelete 
from tbl_site s, tbl_state a, tbl_user_site u, tbl_trial_site t
where s.StateID = a.StateID 
AND (s.SiteID = u.SiteID OR s.SiteID = t.SiteID)

tesu

Hello saisankar_p

Because of not having completely read your perfectly written post from begin to end, I presumed canDelete would already be part of one of your tables. I have to apologize to you for that bad mistake. Finally I have understand that you will compute canDelete depending whether tables tbl_trial_site or tbl_user_site have entries for the associate SiteID.

I have got this solution (it is easy to find, if one considers your complete question :) :

select s.SiteID, SiteName, a.StateID, PostCode, Phone1, '-' as '', StateName,
 CASE
   WHEN (select t.SiteID from tbl_trial_site t where t.SiteID = s.SiteID) 
             IS NOT NULL then 1
   WHEN (select t.SiteID from tbl_user_site t where t.SiteID = s.SiteID)
             IS NOT NULL then 1
   ELSE 0
 END AS canDelete
from tbl_site s join tbl_state a

/* result
SiteID  SiteName  StateID  PostCode  Phone1     StateName   canDelete
-----------------------------------------------------------------------------------
1          Site1        1      2134  124 123 -  ADC                1
2          Site2        3      2323  124 234 -  QTY                0
3          Site3        5      7666  124 645 -  LDG                0
4          Site4        2      2888  124 894 -  WA                 1
*/

This select statement works well on Sybase database what is the "first mother" of MS sql server.
Pls tell me whether it also works on MS sql server (There is a further solution dealing with it-then-else clause.)

tesu

Hi Mad Pat

I am back again. It's really great that you are using open-office db. You know it's hsqldb, that famous pure Java database system what follows official SQL standard nearly completely. You might also know that contrary to open-office's embedded solution this Java db can also run as server (I myself prefer its competitor Derby from Apache).

> Benefit of double primary key?

Well, there is no other way for mapping a many-to-many relationship into relational data model correctly. Also database system then assures integrity of data, so that must not be programmed in application programs explicitly.

Necessary (but not sufficient) for correct normalizations. Therefore, no abnormalities when inserting, updating or deleting rows.

Virtually all kinds of data grouping usually needed in application programs can be completely formulated at SQL level.

Functioning relational data models do economize programming expenses considerably. And don't forget rise of performance due to indexes automatically and correctly defined by database if primary and foreign keys are properly set up. Usually this cannot be achieved by surrogate keys.

Test it! As usual, design data model without composite primary keys or foreign keys, even do so for linking tables which implements many-to-many relationships. Best: replace all of them by surrogate keys having auto-increment attribute. Then try to get the total sales of your customers grouped by customer's name by a simple sql-select statement (no php, java, c++ code) like that one:

select c."name" as Customer, sum(quantity*s.salesprice) as …

Hi,

sales
- ID, integer, auto increment, primary key
- customer (customer_details.ID)
- sale date
- total price, single
- items_ref, sales_items.groupSaleID, integer, unique

sales_items
- product, product_details.ID
- groupSaleID, integer
- amount, small int
- price 'in case of discounts

sales and salesitems must be improved: Primary key of salesitems is and only is: (productID, salesID) and nothing else! productID is the primary key of products (you named it ID there), salesID is primary key of sales (you named it ID there!)

I ll give you an example how to create the many-to-many-relationship table sales_items:

create table sales_items
(
   productID integer not null, salesID integer not null, 
   amount integer not null, price decimal (10,2) not null,
   primary key (productID, salesID);
   -- and most important:
   foreign key (productID) references products,
   foreign key (salesID) references sales
);

Because of foreign-key attributes, productID must be primary key of products, there you may name it ID or bla and vice versa. Also salesID must be primary key of sales, there you may name it ID or blabla or something else.

If your database engine is not able to handle foreign keys (for example mysql isamdb does not like them) then change engines, for example innodb can handle them.
You should never omit primary keys and foreign keys.

btw, whats your target database?

-----
tesu

Hello mad pat

You need a further table, name it salesitems, where its primary key must be formed from salesID and productID because of many-to-many relationship. Both are also foreign keys. Here can you also place quantity, item price, total price etc. Ok, write the create-table statements for all four tables down, submit them and we will further discuss your assignment.

krs,
tesu

Hi rupak

Stay your ground! Knowing as less as possible from that database protects one from becoming infested with badly base database concepts. Try to learn db2, firebird, oracle, postgresql, ms sql server etc, they all are true relational data base systems.

krs,
tesu

Hi

possibly you want that second array of chars behaves like first on, as in:

int main() {
  unsigned char apayload[] = {'H','i',' ','T','h','e','r','e', '\0'};
  unsigned char received_payload[]={'H','i',' ','T','h','e','r','e','a', 'b','\0'};
  cout << apayload << "  "<< received_payload << endl;
  received_payload[8]='\0';
  cout << apayload << "  " << received_payload << endl;
  return 0;
}
/* result
Hi There  Hi Thereab
Hi There  Hi There
*/

You may consider what I added to the initialization of both arrays. Also consider this solution will produce memory leaks.

krs,
tesu

Hello

If your tables are properly designed simple inner joins will solve your problem:

select * tbl_site join tbl_state join tbl_trial_site join tbl_user_site.

Ok, this result set will contain duplicate columns. To discard them you must replace the asterisk
by the list of column names you want to get.

Unfortunately, almost all tables aren't properly designed (because usually amateurs do table design never thinking of how to create functioning sql select statements) therefore some join constraints must be added to the above select statement.

btw, why did you put tbl_ to each object name? Isn't it kind of pleonasm 'cause everybody knows such objects can only be tables.

krs,
tesu

Hi dakoris73

A constraint is kind of restriction usually to the range of values of a variable.
Given the following create-table statement we can detect several constraints:

create table raint(id integer UNIQUE, NOT NULL,
day char(10) CHECK(day IN ('Monday', 'Tuesday', 'wednesday', . . .) );

UNIQUE
This constraint assures that column id cannot contain duplicate values.

NOT NULL
This constraint requires that every insert-into statement of table raint
must also insert a value in column id not equal to NULL (NULL "value" indicates
that a column does not have a value stored)

CHECK()
This is a rather new and some more powerful constraint introduced with the SQL 1999 standard. Above is a mere simple example of a possible application.

Much more powerful constraints can be programmed by using the trigger concept
for what SQL standard has its own programming language (PSM).

I hope my annotations will create some clarification.

krs,
tesu

Hello

That is true, because the IN list is kind of set, all records will be fetched that contain one of the list's element. So you can say the records are "ored" together in result set.

I assume that you can't determine whether a project, for example Terminals, entered by a user would be found in market Aviation only. If not, one has consider almost all permutations to be made of a set, for example given user entries (offices, single-family, retail, hospitality) would then dwindle into (offices, single-family, retail, hospitality), (single-family, offices, retail, hospitality), (retail, single-family,offices , hospitality) ...

So what to do? To get to create effective sql selects, I see two ways to proceed: 1. Re-structuring Markets table temporarily by means of temporary views, stored procedures (transact SQL programs), or applying new WITH clause.

2. Doing some c++, java, or c# programming using ado, odbc, jdbc db-interfaces.

Maybe the latter way is also the more appropriate one, if the some-day selected data are further processed by c++/java etc programs written by yourself. Can you tell me what you want to do with that data?

I will give some thoughts to these problems.

-----
tesu

Hello

depending on how many "grams" will be processed, that is eliminating duplicates, comparing two sets of "grams", hashing method is best method, it is of O(1). And compared with other searching methods it is really easy to program.

Guess how many "grams" of each set need to be compared to eliminate duplicates.

-----
tesu

can you give us the exact error message?

does error also happen if you execute mysql_query($query) ?

does the row you want to delete still exists?

What database engine, what about commit?

-----
tesu

that s true ! try it without _

-----
tesu

Hello

In subselect you select b.Airtime and then you compare it with a.Airtime but between
them there are still large differences.

try these changes:

AND a.Airtime -- you want to get airtime with differences greater than 30 sec
IN (SELECT a.Airtime
FROM stationschedule b
WHERE TIME_TO_SEC( TIMEDIFF( RIGHT( a.Airtime, 9 ) , RIGHT( b.Airtime, 8 ) ) ) > 30

AND a.ShowID = b.ShowID -- necessary, to avoid comparing apples and oranges.
)

You may first test this subselect, especially result of TIME_TO_SEC():
SELECT a.Airtime
FROM stationschedule b
WHERE TIME_TO_SEC( TIMEDIFF( RIGHT( a.Airtime, 9 ) , RIGHT( b.Airtime, 8 ) ) ) > 30

AND a.ShowID = b.ShowID -- necessary, to avoid comparing apples and oranges.

----
tesu

[B]SELECT[/B] Vio.ViolationList.ViolationCode,Records.[Violation Commited],Vio.ViolationList.FineAmnt,DriverInfo.[Plate Number]
,DriverInfo.[License Number],DriverInfo.[Reg'd Last Name],DriverInfo.[Reg'd First Name],DriverInfo.[Reg'd Middle Name],DriverInfo.[Reg'd Address],
DriverInfo.[Address' City Code],DriverInfo.[Reg'd B-Date],DriverInfo.[Conduction Number],
DriverInfo.[Vehicle Category],DriverInfo.[Vehicle Type],DriverInfo.[Vehicle Brand]
,Records.[Street Name],Records.[Date]
[B]FROM[/B] Vio.ViolationList,DriverInfo,Records
[B]WHERE[/B] Records.PlateNo like DriverInfo.[Plate Number]
and Records.[Violation Commited] like Vio.ViolationList.ViolationD

In above from clause you are joining the tables ViolationList, DriverInfo and Records together. These are inner joins where each row of one table will be combined with each row of second table etc. To prevent such cartesian products you must add join constraints to where clause, for example say you have tableA with columns (a,b) what you want to join with tableB having columns (c, d, e). you want to join them on column a from tableA and column d from tableB, so you have to write:

select a, b, c, d from tableA, tableB where a = d AND ...your further predicates

Join constraint is a = b, which means that only rows will be jointed with same values in both tables.

If you join various tables you must carefully define the joining constraints to avoiding bad cartesian products.

There is a more modern inner join syntax:

select .... from tableA inner join tableB inner join tableC etc

But this requires that primary and foreign keys are properly defined. If not, you have to add the ON clause to each inner join.

krs,
tesu

hi,

you should consider my posting #3:

You can freely change the IN list

WHERE Mrkt2 IN ('architecture', 'offices', 'hospitality')

for example

WHERE Maritime IN ('Cruise Terminals', 'ports')

So you need 8 constructions like:
UNION
SELECT PrjID FROM markets WHERE Mrktxx IN (List of items)

This can easily be generated at runtime.

tesu

To cut a long story short: there is no way for retrieving a user's password, neither
from SQL-Server login nor from Windows-user login except you have installed keyboard recorder to spying out the password keyed in.

where to get them from? what language, environment? need hacker's snippets?

Hi

Do below statements not work on mysql?

DELETE FROM accounts WHERE usrn = 'caughtusername';
COMMIT;

As nav33n already suggested.

krs,
tesu

Hi daniyal

you may cast a glance on http://www.daniweb.com/forums/thread132147.html to get your inspiration from on how to solve your problem :icon_wink:

krs,
tesu

Hi again

I have got this idea of selecting the appropriate data from table Markets:

select distinct PrjID, PrjName from projects 
 where PrjID IN
   (
     select PrjID from markets where Mrkt1 IN ('architecture', 'offices', 'hospitality')
     union 
     select PrjID from markets where Mrkt2 IN ('architecture', 'offices', 'hospitality')
     union
     select PrjID from markets where Mrkt3 IN ('architecture', 'offices', 'hospitality')
)

I haven't test it so far. But I believe it must be this sort of select statement which can also be dynamically generated during runtime.

Try it an tell me your result.

krs,
tesu

hi

If you were able to redesign table Markets like that:

(PrjID, MarkedID, Market), where at least all three attributes be part of markets' primary key, the query you are looking for and generating where clause at runtime would really be simple:

select distinct p.PrjID, PrjName from projects p inner join Markets m
where Market IN ('architecture', 'offices', 'hospitality', ... )

(This select requires that all primary and foreign keys are properly defined. If not, you must extend join Markets m by ON p.PrjID = m.PrjID)

With new table Markets

PrjID MrktId Market
001 001 offices
001 001 single-family
001 002 hospitality
001 003 retail
001 003 Something
002 004 offices
003 005 hospitality

and Projects the result set would be

PrjID PrjName
001 myProject

Unfortunately, current table Market is rather poorly designed. Because of the repeating group Mrkt1, Mrkt2 etc it even violates first normal form. I am afraid, it isn't in your power to simply redesign Markets.

There is a further advantage of new table Markets: no matter how many markets you want to insert you never need to change the structure (schema) of that table.

Btw, how many Mrkt columns has your current table Markets?

krs,
tesu

amongst other things, it depends on precision...

Hi,

So you have a table consisting of 6 columns: Column Name Data Type Length
Turnover where the latter should be of decimal data type???

What do you mean by "that the max value could be limited to 9999,99" ? Should output format be restricted to 9999.99 (decimal(8,2)) or the value of turnover itself must not exceed 9999.99 what would be a matter of constraints defined in create table statement?

krs,
tesu