Unsupported Screen Size: The viewport size is too small for the theme to render properly.

Forum Replies Created

Viewing 14 posts - 1 through 14 (of 14 total)
  • Author
    Posts
  • in reply to: #25491
    flixer
    Member
    peterlaursen wrote on Dec 9 2007, 06:43 PM:
    You will not need to forward ports for creating connection in outgoing directions.

    Maybe you have some networking hardware that has problems with Vista, maybe the ISP has or maybe it is a telecom company/transmission issue.

    Do you have an option to try from another location (family or friend) in another town/part of town.

    I think I remember at least once before we had a report about very slow connection from one specific location that was not reproducable from other places.

    I know it is all guesswork. I have no better idea than experimenting with all possible parameters. It would then be possible to exclude possibilities one by one. But it would be nice to know if an INSERT statement from command line client is equally slow!

    Hi peter,

    I've just realised my dad is running XP, so will try sqlyog on his system sometime and get back to you.

    in reply to: #25489
    flixer
    Member
    peterlaursen wrote on Dec 9 2007, 04:17 PM:
    You cannot compare a client running on localhost (like phpMA) with a remote client (like SQLyog).

    I think nobody really know the details about this but there are some issues with TCP implementation on Vista and there IS some issue with the MySQL client code on Vista as well (and that may be a 'incarnation' of the first issue!)

    We also have posted this bug report to MySQL (now almost 3 months ago): http://bugs.mysql.com/bug.php?id=31109

    I have heard that Vista ServicePack1 to be released in about 3 month should contain almost new code in this respect. anyway we have not urselves experience this .. but your ISP may use some networking equipment that is more 'vulnerable' to this. I also think that most ISPs will not test traffic to MySQL unless the have complaints (they have the idea that users need to use a mail program and a browser only!)

    No wonder – what SQLyog sends is the same. On the 'server side' is it impossible to decide if the query was entered in the editor or generated using the GUI

    I remember this post. I cannot tell if it is related.

    But it seems like your webhost has some routing problems with requests/responses sent from/to a MySQL client running on Vista! And I think they should look into it. I suggest you install a MySQL server on your local. because that will also install the command line client. If you experience the same delay with a INSERT statement from the command line client (what I am 95% sure that you will) if definitely is a networking issue that should be solved by the ISP.

    What you need to do from command line client is only something like this

    Code:
    C:Program FilesMySQLMySQL Server 5.0bin>mysql -u root -p -h 127.0.0.1
    Enter password: ********
    Welcome to the MySQL monitor. Commands end with; or g.
    Your MySQL connection id is 5
    Server version: 5.0.45-community-nt MySQL Community Edition (GPL)

    Type 'help;' or 'h' for help. Type 'c' to clear the buffer.

    mysql> Insert into test.dummy (id) values (99);
    Query OK, 1 row affected (0.04 sec)

    command line client tutorial:

    http://www.informit.com/articles/article.a…65&seqNum=2

    Thanks for the feedback. You mention its most likely a problem with my ISPs routing, but I had the same problem with my previous ISP, could it be a routing problem my end? – I am behind a router, should I have forwarded the port I use to connect to the server in my router?

    I havent done this before, because surely if it was blocked by my router surely I wouldnt be able to connect to the server at all?

    I was aware of SP1 for vista, and im hoping it will fix other bugs and hopefully this one too.

    in reply to: #25487
    flixer
    Member
    peterlaursen wrote on Dec 2 2007, 04:15 PM:
    I am using Vista 95% of the time myself and cannot recognize this. But frustrating when it occurs of course!

    Anyway:

    If the program is busy it is not responding! So my best guess is that it takes that much time to finish what it is doing (saving).

    Please first

    1) Ensure that Vista is fully up-to-date

    2) Try SQLyog 6.14

    .. and see if problems persist!

    If problems persist please tell:

    * Is it a remote server?

    * How do you connect (direct, SSH or HTTP)?

    * What type of Internet connection do you have?

    * Can you reproduce on a local server?

    * If it is a local server please try some INSERT statement from command line client. Much faster or not?

    * if it is a remote server and direct connection is possible, please try some other client (like command line, Query Browser)

    * Is it same problem from DATA tab and RESULT tab?

    * You are perfectly sure that it is not a coincidence that something happens on the server or with the connection at the same time as you change the OS? (you are welcome to try your copy from a XP and a Vista machine at the same time from the same location to test this!)

    * You are sure it is not a firewall issue (try turn off all of that kind!)?

    Vista has known issues with TCP implementation (that should mostly affect 'shared connections' and dial-up connections, as I understand), so this could be part of the explanation.

    Hi again,

    Sorry for the delay in updating you on the situation. Vista is fully up to date, and I have just tried version 6.14 and I still experience the same delays when saving changes.

    To answer all your questions, here is some more info:

    – The server is hosted by my webhost, so yes its remote.

    – I presume its a direct connection

    – I have an 8 megabit ADSL connection (8mb download / 1.3mb upload), but this problem also occured on my old 1mbit connection)

    – I havent yet had chance to install a local webserver, but I may try this in future

    – I have tried several insert statements using phpmyadmin which is accessed via my webhost control panel and the queries executed instantly

    – I also get the delay in the results tab of sqlyog on a certain table, and also tried using an insert statement in the query browser of sqlyog and still got the delay

    -I have tried turning off the vista firewall, and i still experience the delay

    I'm pretty sure I never had this issue with XP. However, since changing to Vista the IP of the server at my webhost has changed, infact I even posted a thread on here about the problem i was having after changing the IP in sqlyog. I eventually solved this problem by creating a new sql account just for sqlyog, this account has full permissions, so surely this isnt an issue?

    Any further help much appreciated.

    in reply to: Can't Open Database #25165
    flixer
    Member

    WERHOOOOOOOO – just created a new user in my control panel, added it to the access list for my database and then added the new username/password to sqlyog and I can now open the database!!!!! 😀

    No idea why the original user account wouldnt work, it has full priviledges, anyhow, i'll just use this new account for sqlyog 😀

    in reply to: Can't Open Database #25164
    flixer
    Member

    I have spoken to the webhost techies again. Unfortunately because I can administrate my database perfectly fine with the control panel/phpmyadmin there is nothing else they can do for me. Im rather disappointed with there response because they have always being extremely helpful in the past.

    I will perhaps try creating a new saved connection with SQLyog and see if it makes any difference, but it looks like i'll have to go back to crappy phpmyadmin 🙁

    in reply to: Can't Open Database #25162
    flixer
    Member

    Hmmm. I am using the same user account that I use via the control panel/phpmyadmin. Yes peterlaursen it is a shared hosting package, but its always been so, and SQLyog has always worked perfectly.

    This is quite annoying, I will have to log into my control panel and phpmyadmin and take another look at my privilege settings again, but the fact is I have not touched any of this, the only thing that has changed is the server IP. Looks like I will have to contact my webhost again and see if they can help me any further.

    Any further suggestions appreciated.

    in reply to: Deleting Rows #22523
    flixer
    Member
    peterlaursen wrote on Sep 24 2006, 07:34 PM:
    you cannot!

    when you delete you execute SQL like “DELETE FROM .. WHERE … “

    How would you write a WHERE-condition that distinguishes between identical rows? You cannot, and SQLyog cannot either!

    That is one good reason to have a PRIMARY KEY in the table!

    http://webyog.com/faq/28_70_en.html

    Simply add a autoincrement INTEGER column to the table and define that column as the PK – and there will NEVER be identical rows!

    Thankyou, i now understand the importance of a Primary Key!

    in reply to: Run Out Of Virtual Memory #22521
    flixer
    Member
    peterlaursen wrote on Sep 24 2006, 06:38 PM:
    I asked for the 'create statement for the tables' . the log does not tell anything here.

    'create statement for the table' in on the OBJECTS tab – or execute 'show create table tablename' …

    What makes me suspicious is that you have THREE tables, but you only use TWO of them in the where?

    what about

    Code:
    where region1titles.DVDTitle = ukdvdtitles.DVDTitle And region1titles.DVDTitle = ratings.DVDTitle And ProductionYear > 1900

    .. but to discuss the query in detail I need to understand your tables first!

    Hi Peter,

    I just ran the query u suggested and this time i didnt have the memory issue, however it did take 473440ms to execute!

    But i am getting some duplicate results, so i think i need to add a GROUP BY or AVG statement.

    Thanks again for your valuable assistance, and i hope you dont mind me asking you so many questions.

    in reply to: Run Out Of Virtual Memory #22519
    flixer
    Member
    peterlaursen wrote on Sep 24 2006, 06:13 PM:
    MySQL client ran out of memory

    (7681 ms taken)

    it should normally take much more time than 7-8 sec's to fill that memory.

    You are sure that your JOIN-query dos not return a 'astronomic' set of data?

    How many rows would you expect to be returned here?

    Try attach the 'Create statement' for the tables.

    It would only be between 17,000 to 20,000 rows. Several columns i had to edit because “DVD Title” and “Production Year” wouldnt work in the query, but after altering the columns to “DVDTitle” and “ProductionYear” the query ran (but then came across this memory issue).

    Below is the whole history log for the past 1hr, 15minutes.

    /*[17:14:26][ 370 ms]*/ show variables like '%character%'

    /*[17:14:26][ 70 ms]*/ Set character_set_connection=latin1

    /*[17:14:26][ 0 ms]*/ Set character_set_results=latin1

    /*[17:14:26][ 0 ms]*/ Set character_set_client=latin1

    /*[17:14:26][ 0 ms]*/ set sql_mode=''

    /*[17:14:26][ 20 ms]*/ show databases

    /*[17:14:29][ 0 ms]*/ use `uk-dvd`

    /*[17:14:29][ 231 ms]*/ select `TABLE_NAME` from `INFORMATION_SCHEMA`.`TABLES` where `TABLE_SCHEMA` = 'uk-dvd' and `TABLE_TYPE` = 'BASE TABLE'

    /*[17:17:57][ 110 ms]*/ create table `uk-dvd`.`Region1Titles` ( `DVD Title` varchar (60) NOT NULL , `Production Year` date NOT NULL , `Genre` varchar (25) NOT NULL )

    /*[17:17:59][ 10 ms]*/ show databases

    /*[17:17:59][ 40 ms]*/ select `TABLE_NAME` from `INFORMATION_SCHEMA`.`TABLES` where `TABLE_SCHEMA` = 'uk-dvd' and `TABLE_TYPE` = 'BASE TABLE'

    /*[17:18:04][ 60 ms]*/ select `TABLE_NAME` from `INFORMATION_SCHEMA`.`TABLES` where `TABLE_SCHEMA` = 'uk-dvd' and `TABLE_TYPE` = 'BASE TABLE'

    /*[17:18:04][ 20 ms]*/ show full fields from `uk-dvd`.`region1titles`

    /*[17:18:48][1081 ms]*/ load data local infile 'C:/………….allregion1.csv' into table `uk-dvd`.`region1titles` fields escaped by '\' terminated by ',' enclosed by '”' lines terminated by 'rn' ( `DVD Title`, `Production Year`, `Genre` )

    /*[17:18:56][ 0 ms]*/ show full fields from `uk-dvd`.`region1titles`

    /*[17:18:56][ 0 ms]*/ show keys from `uk-dvd`.`region1titles`

    /*[17:18:56][ 10 ms]*/ select * from `uk-dvd`.`region1titles` limit 0, 1000

    /*[17:19:03][ 0 ms]*/ show full fields from `uk-dvd`.`ukdvdtitles`

    /*[17:19:03][ 0 ms]*/ show keys from `uk-dvd`.`ukdvdtitles`

    /*[17:19:03][ 10 ms]*/ select * from `uk-dvd`.`ukdvdtitles` limit 0, 1000

    /*[17:19:04][ 0 ms]*/ show full fields from `uk-dvd`.`region1titles`

    /*[17:19:04][ 0 ms]*/ show keys from `uk-dvd`.`region1titles`

    /*[17:19:04][ 0 ms]*/ select * from `uk-dvd`.`region1titles` limit 0, 1000

    /*[17:19:11][ 60 ms]*/ truncate table `uk-dvd`.`region1titles`

    /*[17:19:16][ 0 ms]*/ show full fields from `uk-dvd`.`region1titles`

    /*[17:19:16][ 10 ms]*/ show keys from `uk-dvd`.`region1titles`

    /*[17:19:16][ 0 ms]*/ select * from `uk-dvd`.`region1titles` limit 0, 1000

    /*[17:19:19][ 0 ms]*/ show full fields from `uk-dvd`.`region1titles`

    /*[17:19:19][ 0 ms]*/ show keys from `uk-dvd`.`region1titles`

    /*[17:19:19][ 0 ms]*/ show table status from `uk-dvd`

    /*[17:19:37][ 140 ms]*/ alter table `uk-dvd`.`region1titles` change `Production Year` `Production Year` numeric NOT NULL

    /*[17:19:38][ 0 ms]*/ show databases

    /*[17:19:38][ 0 ms]*/ select `TABLE_NAME` from `INFORMATION_SCHEMA`.`TABLES` where `TABLE_SCHEMA` = 'uk-dvd' and `TABLE_TYPE` = 'BASE TABLE'

    /*[17:19:44][ 20 ms]*/ select `TABLE_NAME` from `INFORMATION_SCHEMA`.`TABLES` where `TABLE_SCHEMA` = 'uk-dvd' and `TABLE_TYPE` = 'BASE TABLE'

    /*[17:19:44][ 0 ms]*/ show full fields from `uk-dvd`.`region1titles`

    /*[17:19:55][1022 ms]*/ load data local infile 'C:/……allregion1.csv' into table `uk-dvd`.`region1titles` fields escaped by '\' terminated by ',' enclosed by '”' lines terminated by 'rn' ( `DVD Title`, `Production Year`, `Genre` )

    /*[17:19:59][ 0 ms]*/ show full fields from `uk-dvd`.`region1titles`

    /*[17:19:59][ 0 ms]*/ show keys from `uk-dvd`.`region1titles`

    /*[17:19:59][ 10 ms]*/ select * from `uk-dvd`.`region1titles` limit 0, 1000

    /*[17:21:12][ 0 ms]*/ show full fields from `uk-dvd`.`averagematchedratings`

    /*[17:21:12][ 40 ms]*/ show keys from `uk-dvd`.`averagematchedratings`

    /*[17:21:12][ 0 ms]*/ select * from `uk-dvd`.`averagematchedratings` limit 0, 1000

    /*[17:21:25][ 0 ms]*/ show full fields from `uk-dvd`.`averagematchedratings`

    /*[17:21:25][ 20 ms]*/ show keys from `uk-dvd`.`averagematchedratings`

    /*[17:21:25][ 40 ms]*/ show table status from `uk-dvd`

    /*[17:21:39][ 60 ms]*/ rename table `uk-dvd`.`averagematchedratings` to `uk-dvd`.`avgmatchedratings`

    /*[17:22:53][ 0 ms]*/ show full fields from `uk-dvd`.`region1titles`

    /*[17:22:53][ 0 ms]*/ show keys from `uk-dvd`.`region1titles`

    /*[17:22:53][ 0 ms]*/ select * from `uk-dvd`.`region1titles` limit 0, 1000

    /*[17:23:29][ 20 ms]*/ select DVD Title, Production Year, Genre, Rating from region1titles, avgmatchedratings where region1titles.DVD Title = avgmatchedratings.Title

    /*[17:23:53][ 0 ms]*/ select DVD Title, Production Year, Genre, Rating from region1titles, avgmatchedratings where region1titles.”DVD Title” = avgmatchedratings.Title

    /*[17:24:05][ 0 ms]*/ select DVD Title, Production Year, Genre, Rating from region1titles, avgmatchedratings where “region1titles.DVD Title” = avgmatchedratings.Title

    /*[17:24:19][ 0 ms]*/ select DVD Title, Production Year, Genre, Rating from region1titles, avgmatchedratings where region1titles.DVD Title = avgmatchedratings.Title

    /*[17:24:32][ 10 ms]*/ show full fields from `uk-dvd`.`region1titles`

    /*[17:24:32][ 0 ms]*/ show keys from `uk-dvd`.`region1titles`

    /*[17:24:32][ 40 ms]*/ show table status from `uk-dvd`

    /*[17:24:39][ 0 ms]*/ show databases

    /*[17:24:39][ 0 ms]*/ select `TABLE_NAME` from `INFORMATION_SCHEMA`.`TABLES` where `TABLE_SCHEMA` = 'uk-dvd' and `TABLE_TYPE` = 'BASE TABLE'

    /*[17:24:41][ 0 ms]*/ show full fields from `uk-dvd`.`region1titles`

    /*[17:24:41][ 0 ms]*/ show keys from `uk-dvd`.`region1titles`

    /*[17:24:41][ 40 ms]*/ show table status from `uk-dvd`

    /*[17:24:49][1912 ms]*/ alter table `uk-dvd`.`region1titles` change `DVD Title` `DVD-Title` varchar (60) NOT NULL COLLATE latin1_swedish_ci

    /*[17:24:50][ 0 ms]*/ show databases

    /*[17:24:50][ 90 ms]*/ select `TABLE_NAME` from `INFORMATION_SCHEMA`.`TABLES` where `TABLE_SCHEMA` = 'uk-dvd' and `TABLE_TYPE` = 'BASE TABLE'

    /*[17:25:01][ 10 ms]*/ select DVD-Title, Production Year, Genre, Rating from region1titles, avgmatchedratings where region1titles.DVD-Title = avgmatchedratings.Title

    /*[17:25:27][ 0 ms]*/ show full fields from `uk-dvd`.`region1titles`

    /*[17:25:27][ 30 ms]*/ show keys from `uk-dvd`.`region1titles`

    /*[17:25:27][ 0 ms]*/ select * from `uk-dvd`.`region1titles` limit 0, 1000

    /*[17:25:40][ 0 ms]*/ show full fields from `uk-dvd`.`region1titles`

    /*[17:25:41][ 30 ms]*/ show keys from `uk-dvd`.`region1titles`

    /*[17:25:41][ 80 ms]*/ show table status from `uk-dvd`

    /*[17:25:47][1943 ms]*/ alter table `uk-dvd`.`region1titles` change `DVD-Title` `DVDTitle` varchar (60) NOT NULL COLLATE latin1_swedish_ci

    /*[17:25:48][ 0 ms]*/ show databases

    /*[17:25:48][ 90 ms]*/ select `TABLE_NAME` from `INFORMATION_SCHEMA`.`TABLES` where `TABLE_SCHEMA` = 'uk-dvd' and `TABLE_TYPE` = 'BASE TABLE'

    /*[17:25:58][ 0 ms]*/ select DVDTitle, Production Year, Genre, Rating from region1titles, avgmatchedratings where region1titles.DVDTitle = avgmatchedratings.Title

    /*[17:26:07][ 0 ms]*/ show full fields from `uk-dvd`.`region1titles`

    /*[17:26:07][ 20 ms]*/ show keys from `uk-dvd`.`region1titles`

    /*[17:26:07][ 70 ms]*/ show table status from `uk-dvd`

    /*[17:26:19][2234 ms]*/ alter table `uk-dvd`.`region1titles` change `Production Year` `ProductionYear` decimal (10,0) NOT NULL

    /*[17:26:20][ 0 ms]*/ show databases

    /*[17:26:20][ 100 ms]*/ select `TABLE_NAME` from `INFORMATION_SCHEMA`.`TABLES` where `TABLE_SCHEMA` = 'uk-dvd' and `TABLE_TYPE` = 'BASE TABLE'

    /*[17:26:30][2373 ms]*/ select DVDTitle, ProductionYear, Genre, Rating from region1titles, avgmatchedratings where region1titles.DVDTitle = avgmatchedratings.Title

    /*[17:31:01][ 10 ms]*/ show full fields from `uk-dvd`.`avgmatchedratings`

    /*[17:31:01][ 0 ms]*/ show keys from `uk-dvd`.`avgmatchedratings`

    /*[17:31:01][ 0 ms]*/ select * from `uk-dvd`.`avgmatchedratings` limit 0, 1000

    /*[17:34:40][ 0 ms]*/ select DVDTitle, ProductionYear, Genre, Rating from region1titles, avgmatchedratings where region1titles.DVDTitle = avgmatchedratings.Title And ProductionYear = notnull

    /*[17:35:40][3235 ms]*/ select DVDTitle, ProductionYear, Genre, Rating from region1titles, avgmatchedratings where region1titles.DVDTitle = avgmatchedratings.Title And ProductionYear > 1900

    /*[17:38:30][ 0 ms]*/ describe `uk-dvd`.`ukdvdtitles`

    /*[17:38:30][ 40 ms]*/ show index from `uk-dvd`.`ukdvdtitles`

    /*[17:39:04][ 0 ms]*/ show full fields from `uk-dvd`.`ukdvdtitles`

    /*[17:39:04][ 30 ms]*/ show keys from `uk-dvd`.`ukdvdtitles`

    /*[17:39:04][ 0 ms]*/ select * from `uk-dvd`.`ukdvdtitles` limit 0, 1000

    /*[17:39:29][ 0 ms]*/ show full fields from `uk-dvd`.`ukdvdtitles`

    /*[17:39:29][ 40 ms]*/ show keys from `uk-dvd`.`ukdvdtitles`

    /*[17:39:29][ 0 ms]*/ select * from `uk-dvd`.`ukdvdtitles`

    /*[17:39:36][ 0 ms]*/ select DVDTitle, ProductionYear, Genre, Rating from region1titles, ukdvdtitles where region1titles.DVDTitle = ukdvdtitles.DVDTitle And ProductionYear > 1900

    /*[17:40:40][ 0 ms]*/ select region1titles.DVDTitle, ProductionYear, Genre, Rating from region1titles, ukdvdtitles where region1titles.DVDTitle = ukdvdtitles.DVDTitle And ProductionYear > 1900

    /*[17:41:28][7681 ms]*/ select region1titles.DVDTitle, ProductionYear, Genre, Rating from region1titles, ukdvdtitles, imdbratings where region1titles.DVDTitle = ukdvdtitles.DVDTitle And ProductionYear > 1900

    /*[18:01:41][ 150 ms]*/ show variables like '%character%'

    /*[18:01:41][ 70 ms]*/ Set character_set_connection=latin1

    /*[18:01:41][ 0 ms]*/ Set character_set_results=latin1

    /*[18:01:41][ 0 ms]*/ Set character_set_client=latin1

    /*[18:01:41][ 0 ms]*/ set sql_mode=''

    /*[18:01:41][ 90 ms]*/ show full fields from `uk-dvd`.`ukdvdtitles`

    /*[18:01:42][ 160 ms]*/ show keys from `uk-dvd`.`ukdvdtitles`

    /*[18:01:42][ 51 ms]*/ select * from `uk-dvd`.`ukdvdtitles`

    in reply to: Creating Table Error #22513
    flixer
    Member

    BTW, which column would i need to set as primary key ?

    in reply to: Creating Table Error #22512
    flixer
    Member
    peterlaursen wrote on Sep 24 2006, 02:13 AM:
    the reason for the error is that you must specify the lenth of a varchar.

    A numeric and a decimal is the same! Sure that you would not like an INTEGER for the 'votes' ? And you would normally want some decimals available for 'rating' ?

    About numeric types in MySQL:

    http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html

    I would also say that you should consider to have a Primary Key!

    Thanks again, it works now!.

    in reply to: Creating Table Error #22510
    flixer
    Member
    peterlaursen wrote on Sep 23 2006, 02:33 PM:
    did you specify the decimal format like decimal(19,4)?.

    If you do not specify anything default is decimal(10,0)!

    decimal is a 'high precision math type' in MySQL. It is good to use a decimal(x,4) for monetary data.

    http://www.webyog.com/faq/29_90_en.html

    Hi peter,

    I'm using sqlyog v5.18, below is the history data and screenshot attached. I've also tried specifying decimal in lens column as 10,0 but i get the same error.

    /*[21:37:31][ 150 ms]*/ show variables like '%character%'

    /*[21:37:31][ 20 ms]*/ Set character_set_connection=latin1

    /*[21:37:31][ 0 ms]*/ Set character_set_results=latin1

    /*[21:37:31][ 0 ms]*/ Set character_set_client=latin1

    /*[21:37:31][ 0 ms]*/ set sql_mode=''

    /*[21:37:31][ 60 ms]*/ show databases

    /*[21:37:33][ 0 ms]*/ use `uk-dvd`

    /*[21:37:33][ 10 ms]*/ select `TABLE_NAME` from `INFORMATION_SCHEMA`.`TABLES` where `TABLE_SCHEMA` = 'uk-dvd' and `TABLE_TYPE` = 'BASE TABLE'

    /*[21:38:38][ 10 ms]*/ create table `uk-dvd`.`IMDBRating` ( `Votes` numeric NULL , `Rating` decimal NULL , `Title` varchar NULL )

    hope you can help

    regards

    in reply to: Joining Tables #22380
    flixer
    Member
    peterlaursen wrote on Sep 12 2006, 08:07 PM:
    “m getting more than one matched result for certain titles”

    use GROUP BY

    That worked great thankyou!. The query took approximately 25 seconds to execute, and produced 1010 results, is this a typical time

    to process SQL querys across 2 tables?

    I'm guessing the “Views” subtree is where i can save certain search results for future reference?

    Could you briefly explain the “Stored Procs” and “Functions” subtrees also available in the database tree, im guessing one of these is for saving SQL queries?

    Sorry to ask so many questions. But im very keen to learn SQL for personal use and also to improve my job prospects.

    in reply to: Joining Tables #22378
    flixer
    Member
    peterlaursen wrote on Sep 10 2006, 10:58 PM:
    No there isn't really any easier way. We plan a GUI SQL-builder but most likely won't be this year.

    In most uncomplicated cases you can omit the JOIN keyword itself like.

    Code:
    SELECT table1.DVDTitle, table2.Ratings FROM table1, table2 WHERE ……

    Example WHERE with a typical FK-setup (no matter if you use it as CONSTRAINT with InnoDB or not)

    Code:
    WHERE table1.parent_field = table2.child_field

    .. I assume that you have SOME identical info in both tables that identifies each DVD (whether a number or something else!). If you don't you are in trouble …

    Thanks for the feedback. Yes that query statement works and i getting matching results. The only problem i now have is im getting more than one matched result for certain titles. The ratings table may have 2 or more entries for “Batman” for example one entry for Batman, the movie which came out in 1989 and another entry for Batman the Tv series which aired in 1992, both with unique ratings. So even though my UK DVD titles list may only have one entry for Batman, i get several results.

    Obviously this is more a problem with the raw data than my database or sqlyog.

    I possibly may be able to get around it by adding another table of titles and release dates so atleast then in the results i will know how to differentiate between the various duplicate results.

    Any other feedback/suggestions appreciated.

Viewing 14 posts - 1 through 14 (of 14 total)